SELECT WHERE A=“$var"的奇怪行为；SQL [英] Strange behavior with SELECT WHERE A="$var" SQL

问题描述

我寻找了答案，但找不到，因为这里的问题似乎略有不同.

I looked for an answer but could not find it as the issue seems to be slightly different here.

$vid = $_SESSION['ID_Vendor'];
echo "ID: $vid";
$q = 'SELECT business_name, vd.ID_Vendor, res.ID_RestaurantEstablishment 
  FROM restaurant res
   INNER JOIN vendor_data vd
  ON vd.ID_Vendor=res.ID_Vendor AND res.ID_Vendor="$vid" ORDER BY business_name ASC';

变量 $vid 确实有一个值(在这种情况下等于 2，但它可能不同)，但是，当我专门设置

The variable $vid has indeed a value (equal to 2 in this case, but it could be different), HOWEVER, when I specifically set

 WHERE res.ID_Vendor=2

我的查询返回正确和预期的值列表，但是当我使用

my query returns the correct and expected list of values, but when, instead, I use

 WHERE res.ID_Vendor="$vid"

使用$vid"，我的值的回声就是空的.

with "$vid", the echo of my values is simply empty.

以下是用于回显输出的完整代码片段.感谢您的帮助.

Below is the full snippet of the code to also echo the output. Thanks for your help.

 $vid = $_SESSION['ID_Vendor'];
 echo "ID: $vid";
 $q = 'SELECT business_name, vd.ID_Vendor, res.ID_RestaurantEstablishment 
  FROM restaurant res
   INNER JOIN vendor_data vd
  ON vd.ID_Vendor=sfe.ID_Vendor AND res.ID_Vendor="$vid" ORDER BY   business_name ASC';
 $r = mysqli_query($connection, $q);
 while ($row = mysqli_fetch_array ($r, MYSQLI_NUM)) {

  echo '>' . htmlspecialchars($row[0]) . '  ' . htmlspecialchars($row[1]) . '   ' . htmlspecialchars($row[2]) .'</option>';
}

推荐答案

既然我提出了这个想法，我想我应该说明准备语句的使用.

Since I raised the idea, I suppose I should illustrate the use of prepared statements.

使用 mysqli 可以进行如下操作(假设 $connection 已成功初始化):

Using mysqli one would proceed as follows (assuming $connection has been successfully initialized):

// The indentation here is purely a matter of personal preference
$query = 'SELECT business_name, vd.ID_Vendor, res.ID_RestaurantEstablishment 
            FROM restaurant res
            INNER JOIN vendor_data vd
              ON vd.ID_Vendor = res.ID_Vendor
            WHERE res.ID_Vendor = ?
            ORDER BY business_name ASC';

$stmt = $connection->prepare($query);
$stmt->bind_param('s', $vid);  // 's' assumes $vid is string; use 'i' for int
$stmt->execute();
$res = $stmt->get_result();
while ($row = $res->fetch_array(MYSQLI_NUM))
{
    echo '>' . htmlspecialchars($row[0]) . '  ' . htmlspecialchars($row[1]) . '   ' . htmlspecialchars($row[2]) .'</option>';
}

使用 PDO 的习惯用法是一样的.记录了 PDO 数据源名称 (DSN) 的格式在线.

$conn = new PDO($dsn, $username, $password); // define these vars elsewhere
$query = 'SELECT business_name, vd.ID_Vendor, res.ID_RestaurantEstablishment 
            FROM restaurant res
            INNER JOIN vendor_data vd
              ON vd.ID_Vendor = res.ID_Vendor
            WHERE res.ID_Vendor = :vid
            ORDER BY business_name ASC';
$stmt = $conn->prepare($query);
$stmt->execute(array(':vid' => $vid));
while ($row = $stmt->fetch(PDO::FETCH_NUM))
{
    echo '>' . htmlspecialchars($row[0]) . '  ' . htmlspecialchars($row[1]) . '   ' . htmlspecialchars($row[2]) .'</option>';
}

在这两种情况下，我都将错误处理留给读者作为练习.

In both cases I leave error handling as an exercise for the reader.

这篇关于SELECT WHERE A=“$var"的奇怪行为；SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！