PHP variables don't evaluate in the query

Question

I am pretty new to PHP and MySQL and I just can't figure this one out. I have searched all around the forum but haven't found an answer I can make sense of. I originally was using mysql_fetch_assoc() but I could only search numbers and I received errors when searching for letters as well. I hope I am on the right track here. Thank you in advance for all your help!

$con = mysqli_connect($hostname,$username,$password) or die ("<script language='javascript'>alert('Unable to connect to database')</script>");
mysqli_select_db($con, $dbname);

if (isset($_GET['part'])){
    $partid = $_GET['part'];
    $sql = 'SELECT * 
        FROM $usertable 
        WHERE PartNumber = $partid';

    $result = mysqli_query($con, $sql);
    $row = mysqli_fetch_assoc($result);

    $partnumber = $partid;
    $nsn = $row["NSN"];
    $description = $row["Description"];
    $quantity = $row["Quantity"];
    $condition = $row["Conditio"];
}

Don't forget to filter `$_GET['part']` to prevent SQL injection. — honyovk, Jul 05 '12 at 15:51

score 33 · Accepted Answer · edited Dec 21 '22 at 11:17

33

This happens when your result is not a result (but a "false" instead). You should change your code to this

$sql = 'SELECT * 
    FROM usertable 
    WHERE PartNumber = ?';

$stmt = $con->prepare($sql);
$stmt->bind_param("s", $partid);
$stmt->execute();
$result = $stmt->get_result();
$row = mysqli_fetch_assoc($result);

because you shouldn't add data variables in the SQL directly, but use placeholders instead. Given a placeholder cannot be used for a table name, it is generally a bad idea to make it a variable, so just write it as is.

edited Dec 21 '22 at 11:17

Your Common Sense

156,878
40
214
345

answered Jul 05 '12 at 15:50

Sliq

15,937
27
110
143

It's because strings need to be escaped like above. I just improved my answer. – Sliq Jul 05 '12 at 16:21
"This happens when your result is not a result (but a "false" instead)." That really helped me debug in seconds. – The Room Dec 28 '16 at 23:55

score 17 · Answer 2 · edited Jan 02 '18 at 23:56

17

mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, boolean given

This means that the first parameter you passed is a boolean (true or false).

The first parameter is $result, and it is false because there is a syntax error in the query.

" ... WHERE PartNumber = $partid';"

You should never directly include a request variable in a SQL query, else the users are able to inject SQL in your queries. (See SQL injection.)

You should escape the variable:

" ... WHERE PartNumber = '" . mysqli_escape_string($conn,$partid) . "';"

Or better, use Prepared Statements.

edited Jan 02 '18 at 23:56

Shareque

361
4
23

answered Jul 05 '12 at 15:51

Arnaud Le Blanc

98,321
23
206
194

http://www.macaerospace.info/inventory/test?part=1 works fine when it is just searching for a number | http://www.macaerospace.info/inventory/test?part=PN123 does not work and gives me the boolean error| Also, I received a T-VARIABLE error when inserting the escape string. Security is not a huge issue for me right now as I am just trying to get this to work first – user1504463 Jul 05 '12 at 16:39

score 2 · Answer 3 · answered Jul 05 '12 at 15:50

2

You are single quoting your SQL statement which is making the variables text instead of variables.

$sql = "SELECT * 
    FROM $usertable 
    WHERE PartNumber = $partid";

answered Jul 05 '12 at 15:50

johnmadrak

840
5
7

score 1 · Answer 4 · edited Nov 05 '16 at 18:32

Mysqli makes use of object oriented programming. Try using this approach instead:

function dbCon() {
        if($mysqli = new mysqli('$hostname','$username','$password','$databasename')) return $mysqli; else return false;
}

if(!dbCon())
exit("<script language='javascript'>alert('Unable to connect to database')</script>");
else $con=dbCon();

if (isset($_GET['part'])){
    $partid = $_GET['part'];
    $sql = "SELECT * 
        FROM $usertable 
        WHERE PartNumber = $partid";

    $result=$con->query($sql_query);
    $row = $result->fetch_assoc();

    $partnumber = $partid;
    $nsn = $row["NSN"];
    $description = $row["Description"];
    $quantity = $row["Quantity"];
    $condition = $row["Conditio"];
}

Let me know if you have any questions, I could not test this code so you might need to tripple check it!

Your SQL query is invalid. You can `NOT` use variables in a single quote string — Twister1002, Jun 27 '14 at 18:00

score -1 · Answer 5 · answered Jul 05 '12 at 15:51

-1

What happens in your code if $usertable is not a valid table or doesn't include a column PartNumber or part is not a number.

You must escape $partid and also read the document for mysql_fetch_assoc() because it can return a boolean

answered Jul 05 '12 at 15:51

TerryE

10,724
5
26
48

PHP variables don't evaluate in the query

5 Answers5

Linked