Placing a PHP variable in a MySQL query

Question

I'm having issues placing a PHP variable in MySQL string,

<?php
$con=mysqli_connect("***","***","***","***");

function getItem($itemNo)
{
    if (mysqli_connect_errno())
    {
        echo "Failed to connect to MySQL: " . mysqli_connect_error();
    }

    $result = mysqli_query($con,"SELECT * FROM products WHERE product_id = '$itemNo'");

    echo $itemNo;
    echo "<br>";
    while($row = mysqli_fetch_array($result))
    {
        echo $row['product_id'] . " " . $row['product_name'];
        echo "<br>";
    }
}
getItem(1001);
mysqli_close($con);

?>

The page shows my echo of the $itemNo, but thats all. If I just do select * from products, it gives my entire table like it should, so I know the database is working, so I've narrowed it down to the placement of the variable.

EDIT:

product_id column is an int and also the primary key.

what is the type of product_id ? if the type is int or bigint then just use $result = mysqli_query($con,"SELECT * FROM products WHERE product_id = $itemNo"); — user3470953, Apr 21 '14 at 06:31
are you doing anything like - you have data like 001001 in db and passing in the function only 1001 ? — user3470953, Apr 21 '14 at 06:40
Is that your actual database login in your supplied code? Surely that's something you should be keeping to yourself? — Jimmy Thompson, Apr 21 '14 at 06:41
As Jimmy mentioned, you've left your login data. An edit was done to remove it, but people can always see the history. It would be advisable to change both login and password on your end. — Blizz, Apr 21 '14 at 06:43
how stupid of me, i worked out the issue, my $con were out of scope for when i was calling the function. and changing the '$itemNo' to $itemNo allowed it to work in the query as it was just an int also. — Nicholas Tsaoucis, Apr 21 '14 at 06:44
Just a sidenote, which you probably already know. Dont do this `"SELECT * FROM products WHERE product_id = '$itemNo'"` unless you are making software just for yourself. One can SQL-inject to that code pretty much anything they want. — raPHPid, Apr 21 '14 at 06:55

rmcfrazier · Answer 1 · 2014-04-21T06:48:47.270

2

You can try a prepared statement to make using variables in your queries easier.

$stmt = $con->prepare("SELECT * FROM products WHERE product_id=?");
$stmt->bind_param($itemNo);
$stmt->execute();
$stmt->close();

edited Apr 21 '14 at 06:48

answered Apr 21 '14 at 06:36

rmcfrazier

444
4
8

score 1 · Accepted Answer · answered Apr 21 '14 at 06:49

1

 $result = mysqli_query($con,"SELECT * FROM products WHERE product_id = " .$itemNo );

answered Apr 21 '14 at 06:49

Bender

705
11
25

Placing a PHP variable in a MySQL query

2 Answers2