[Solved] Strange behavior with SELECT WHERE A=”$var” SQL [duplicate]

by


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

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>';
}

The idiom is the same using PDO. The format of the PDO data source name (DSN) is documented online.

$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.

solved Strange behavior with SELECT WHERE A=”$var” SQL [duplicate]