php mysql将变量分配给列数据

Question

我正在尝试将 MySQL 列数据分配给 php 变量，以便我可以通过电子邮件发送。它运行查询以在数据库中查找匹配的电话号码，然后返回行/列数据。一些变量是通过文本消息引入到这个 php 脚本的，以防你想知道为什么它们不在 mysql 中。当我在浏览器中运行时，while 语句出现错误，但我感觉我的 SELECT 语句不正确。谢谢!

<?php
session_start();

//$to_number_back = $_GET['to_number'];
$to_number_back = "+15551212";
$dcsrep = array();
$name = array();
$date = array();
$amount = array();
$digits = array();
$details = array();


//include_once("scripts/connect_to_mysql.php");
$servername = "****";
$username = "****";
$password = "****";
$dbname = "****";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}

$stmt = $conn->prepare('SELECT dcsrep, name, date, amount, digits, details     FROM ***uth WHERE to_number = ?');

$stmt->bind_param('s', $to_number_back);

$result = $stmt->execute();
$stmt->store_result();

while($row = $result->fetch_assoc()) {

$dcsrep[] = $row['dcsrep'];
$name[] = $row['name'];
$date[] = $row['date'];
$amount[] = $row['amount'];
$digits[] = $row['digits'];
$details[] = $row['details'];
}

if($stmt->num_rows > 0){
echo "Records received";
} else {
echo "Error: " . $sql . "<br>" . $conn->error;
}


$conn->close();


$from_number = $_GET['from_number'];
$message = $_GET['message'];

    $to = "***@global.net";
    $from = "admin@d***.com";

    $subject = " Payment Authorization";

    //// start email body ////

    $message1 = "


From: $from_number

To: $to_number


Message:

$dcsrep
$name
$date
$amount
$digits
$details


$message



";

    //// Set das headers eh ////

    $headers = 'MIME-Version: 1.0' . "rn";

    $headers .= "Content-type: textrn";

    $headers .= "From: $fromrn";

    /// Okay, you send now!

    mail($to, $subject, $message1, $headers, '-f admin**ect.com');

echo "it worked";

?>

Answer 1

您在这里使用的是 mysql、mysqli、过程和面向对象函数的大杂烩，您需要标准化。

首先，让我们更改$conn 以实例化mysqli 连接 的实例并返回一个对象。

$conn = new mysqli($servername, $username, $password, $dbname);

然后做你的检查:

if($conn->connect_error){
    die("Connection failed: " . $conn->connect_error);
}

现在，您将接受用户输入数据，所以不要直接将其注入(inject)字符串，而是利用准备好的语句。此外，您的号码实际上是一个字符串而不是整数。但是，我们准备好的语句和 bind_param 调用将处理该问题

$stmt = $conn->prepare('select dcsrep, name, date, amount, digits, details from ****uth where to_number = ?');

现在 ? 是一个占位符，让我们将数据安全地绑定(bind)到它。

$stmt->bind_param('s', $to_number_back);
                   ^--- treat it as a string

太棒了。现在它已安全 sanitizer 。让我们现在执行语句并遍历返回的结果。

$result = $stmt->execute();

while($row = $result->fetch_assoc()){
    $dcsrep[] = $row['dcsrep'];
    $name[] = $row['name'];
    $date[] = $row['date'];
    $amount[] = $row['amount'];
    $digits[] = $row['digits'];
    $details[] = $row['details'];
}

现在您可以按预期进行了。

编辑

您也不应该担心 $conn->query($sql) === TRUE。相反，您应该存储请求，然后评估返回的行数。

调用->execute();后需要直接存储结果

$result = $stmt->execute();
$stmt->store_result();

现在您可以检查行数而不是查询是否为真。

if($stmt->num_rows > 0){
    //do your stuff, query found results.
} else {
    //die out
}