mysqli_fetch_assoc() expects parameter 1 to be mysqli_result error

[LaurelBrittany]

0 down vote favorite

I am getting an error on my ecommerce site Warning: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, boolean given in /home/lpehrson/public_html/webdev/edit.php on line 65

You can log in here: http://lpehrson.mydevryportfolio.com/webdev/admin_login.php The user name is [email protected] and the password is test.

The edit page is here: http://lpehrson.mydevryportfolio.com/webdev/edit.php

Here is a link to the website files here: http://lpehrson.mydevryportfolio.com/webdev/LPehrsonHomeworkHelp.zip

If you could help that would be great.

[__]

> *`Warning:`*` mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, boolean given`

This means that the `mysqli_fecth_assoc` function expects the first argument to be a `mysqli_result` (i.e., the type of object returned from a successful query), but that argument was a boolean instead (i.e., `true` or `false`).

This almost always happens because an SQL query **failed**, and **nobody checked** before trying to retrieve the results.

**wrong**

$result = mysqli_query( $sql );
while( $row = mysqli_fetch_assoc( $result ) ){
/* . . . do something */
}

**right**

$result = mysqli_query( $sql );
if( $result ){
// success! check results
while( $row = mysqli_fetch_assoc( $result ) ){
/* . . . do something */
}
}
else{
// failure! check for errors and do something else
}

*****
[The Friendly Manual](http://php.net/mysqli_fetch_assoc) is a great resource too… : )

[LaurelBrittany]

				while($row=mysql_fetch_assoc($result))

{

echo '<div>ID</div><input style="hidden" name="id" value="'.$id.'"><br/><br/>';

echo '<div>Table</div><input style="hidden" name="table" value="'.$table.'"><br/><br/>';

echo '<div>Title</div><textarea name="title" width="500">'.$row.'</textarea><br/><br/>';

echo '<div>Message</div><textarea name="message" width="500" height="300">'.$row.'</textarea>';

}Check out this Pen!

I now have this. How would I go about writing a code that would work. I got this code from my school and it doesn’t seem to work.

[__]

> How would I go about writing a code that would work.

In Very General Terms,

… write code

… test code

… **describe problems, or no one will be able to help**

… rewrite code

… test again

> I got this code from my school and it doesn’t seem to work.

What do you mean by that? “it doesn’t work” is very, very useless information. Are you still getting the same error regarding `mysqli_fetch_assoc()`? Something else? Does it reach a certain point and then break? Is there any output at all?

I could describe a few things wrong with that code, but I doubt any of them are the problem you’re asking about. Did you note my comment above about *checking if the query was successful* before trying to use the results? Do you have (or have you tried) such a control structure in your code already?

[LaurelBrittany]

Most of what you are saying is over my head. I am in a beginner PHP course. That’s why I said it came from my school. I do not know how to test the code besides running it in the browser and seeing what errors I get. If there are any tools you know about to test the code that would be helpful. This is the error I get: Invalid query: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘where id = ”’ at line 1

I do not know what a control structure is. I don’t know what mean by checking if the query is successful before trying to use the results. I do not know how to write my own PHP. I need help writing something that will make the form to edit the product table.

[LaurelBrittany]

I ran the code as you described as being correct in your first comment and got this:

“This code has failed “

<form method="post" action="<?php $_SERVER;?>">

<?PHP

// Get the ID and Table name into variables

$id = $_GET;

$table = $_GET;

$title = $_GET;



include('includes/dbc_admin.php');

$sql = "SELECT * FROM $table where id = '$id'";

$result = mysql_query($sql);

if($result) {

while($row=mysql_fetch_assoc($result))

{

echo '<div>ID</div><input style="hidden" name="id" value="'.$id.'"><br/><br/>';

echo '<div>Table</div><input style="hidden" name="table" value="'.$table.'"><br/><br/>';

echo '<div>Title</div><textarea name="title" width="500">'.$row.'</textarea><br/><br/>';

echo '<div>Message</div><textarea name="message" width="500" height="300">'.$row.'</textarea>';

}

}

else {

echo 'This code has failed';

}

?>

<input type="submit" name="Submit_Update" value="Update">

</form>Check out this Pen!

[__]

> Most of what you are saying is over my head. I am in a beginner PHP course.

Not a problem. Have you discussed this code in class yet? I would hope your instructor helps you understand what code *does* before expecting you to use it.

> I do not know how to test the code besides running it in the browser and seeing what errors I get.

Actually, that’s a great method.

> If there are any tools you know about to test the code that would be helpful.

A good text editor can be a great help, by highlighting your code, pointing out syntax errors, and so forth – I use [Komodo](http://activestate.com/komodo-edit) (Komodo Edit is free and runs on Linux/Windows/Mac), but there are many good choices out there.

> This is the error I get: `Invalid query: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘where id = ”’ at line 1`

**Very** helpful.

That means something is going wrong when you try to write your query – near the part that says “where id=”. Print it out and see what it actually says:

// after this line:
$sql = “SELECT * FROM $table where id = ‘$id'”;
// do this:
exit( $sql );

That should print the SQL query you’re generating to the screen, so you can see what MySQL sees.

*(My guess is that your variable `$id` holds something that creates a syntax error.)*

> I do not know what a control structure is. I don’t know what mean by checking if the query is successful before trying to use the results.

A *control structure* helps decide *how your program runs*. In this case, I’m talking about using an `if{}` structure to make sure you only try to fetch results **if** the query was successful.

> *[mysqli_query()] Returns FALSE on failure. For successful SELECT, SHOW, DESCRIBE or EXPLAIN queries mysqli_query() will return a mysqli_result object. For other successful queries mysqli_query() will return TRUE.*
–http://php.net/mysqli_query

.

> I need help writing something that will make the form to edit the product table.

Alright – check your `$sql` first, and then we can start figuring that out.

[__]

> // Get the ID and Table name into variables
$id = $_GET;
$table = $_GET;
$title = $_GET;

I don’t mean to distract you, but it’s probably best to address this now:

*****
## [**NEVER DO THIS**](http://xkcd.com/327/) : )
*****

If you take user input (e.g., from `$_GET`) and put it directly into an SQL query, you run the risk of generating SQL errors that will ruin your database (*best* case) or allowing an attacker to gain control of your database and possibly compromise your entire server (*worst* case).

Instead, you need to *validate* and *sanitize* all user-submitted data.

*Validate* means “make sure it *is* what you *think it is*.”

For example, you probably expect `$_GET` to be a number – but it *could* be anything the user types in. They could type in `gibberish’ or 1=1 –`, and your query would return *all* rows.

Before putting it in the query, make sure it is a number:

if( ctype_digit( $_GET ) ){
$id = $_GET;
}
else{
/* error! ‘id’ is not a number */
}

Likewise, you probably expect `$_GET` to be the name of a database table (and probably one of a particular few). Use a whitelist:

$allowedTables = array( ‘this_table’,’that_table’ );
if( in_array( $_GET,$allowedTables ) ){
$table = $_GET;
}
else{
/* error! you’re not allowed to look at ‘table’ */
}

*Sanitize* means “make sure *data* is treated like *data*, and not like *a command*.”

Certain characters can change the meaning of your SQL query. In the example above, I used `gibberish’ or 1=1 –` – the single-quote after *gibberish* would make MySQL treat “or” as an SQL command, and not as simply the word “or.” That’s what created the danger – allowing an attacker to rewrite your query as they saw fit.

Use the appropriate escape functions to prevent this. In this case, [mysqli_real_escape_string()](http://php.net/mysqli_real_escape_string) would make sure that quote was *escaped* and treated as part of the string, not as part of the SQL query.

[Author]

Posts