What causes this error in my PHP SQL query?

Question

I'm having a problem with my mysql php code. The code is meant to search the data base with variables inputted by the user and bring up the corresponding results. For example if I searched for only colour photos only colour photos would be listed with the name of the artist, size etc. But I am getting an error message and I don't understand what it means. It reads:

Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result,
boolean given in C:\Program Files\xampp\htdocs\results.php on line 64

This is the code that's causing the problem, anyone know why???

//Run the query and storee result
$result = mysqli_query($link, $query);

//Get number of rows in the result set
$number_of_rows = mysqli_num_rows ($result);

//close link to database
mysqli_close($link);

The original query is:

//Define an SQL query to retrieve desired information
$query = "
SELECT
photos.photo_id, members.member_name, photos.photo_title, photos.photo_film,
    photos.photo_height, photos.photo_width
FROM members, photos
WHERE members.member_id = photos.member_id
";

//restict SQL query with an AND clause if a photo title has been supplied
if ($form_photo_title !="") {
 $query.= "AND photos.photo_title = '$form_photo_title' ";
}

//Restrict the SQL query with an AND clause if a member has been selected
if ($form_member_name !=0) {
 $query .= "AND members.member_name = $form_member_name ";
}

//Restrict the SQL query with an AND clause if a colour mode has been selected
if ($form_type !="") {
 $query .= "AND photo.photo_film = $form_type ";
}

//Run the query and storee result
$result = mysqli_query($link, $query);

Answer 1

There is something wrong with your query and it is returning FALSE instead of a result set. What is the query?

Answer 2

Your mysqli_query command will be returning false. Use mysqli_error to diagnose the problem.

if (!mysqli_query($link, $query)) {
    printf("Errormessage: %s\n", mysqli_error($link));
}

You will need to do the above check to determine for certain, but the problem with your query could relate to this section, which does not quote what appears to be a string value:

if ($form_member_name !=0) {
    $query .= "AND members.member_name = $form_member_name ";
}

$form_member_name should be surrounded with single quotes at the very least, but you should definitely be using parameterised statements for this rather than embedding unsanitised variables into your queries as you are leaving yourself wide open to a SQL injection attack. Here is a revised version, but bear in mind I'm a bit rusty with mysqli and can't test it without your DB:

$query = "
 SELECT
 photos.photo_id, members.member_name, photos.photo_title, photos.photo_film,
  photos.photo_height, photos.photo_width
 FROM members, photos
 WHERE members.member_id = photos.member_id
";

$types = "";
$params = array();

if ($form_photo_title !="") {
 $query.= "AND photos.photo_title = ? ";
 $types .= "s";
 $params[] = $form_photo_title;
}

if ($form_member_name !=0) {
 $query .= "AND members.member_name = ? ";
 $types .= "s";
 $params[] = $form_member_name;
}

if ($form_type !="") {
 $query .= "AND photo.photo_film = ? ";
 $types .= "s";
 $params[] = $form_type;
}

if (!($statement = mysqli_prepare($link, $query)))
 throw new Exception(mysqli_error($link));

// this tells the statement to substitute those question marks with each of 
// the values in the $params array. this is done positionally, so the first 
// question mark corresponds to the first element of the array, and so on. 
// the $types array is just a string with an indication of the type of the 
// value stored at each position in the array. if all three of the above 
// clauses are applied, then $types will equal "sss", indicating that the 
// first, second and third elements in $params are string types. 
// worse still, because the parameters to the query are dynamic, we can't 
// call mysqli_stmt_bind_param directly as it does not allow an array to be 
// passed, so we have to call it dynamically using call_user_func_array!
// i really hate this about mysqli.
// if all three of your above query clauses are applied, this call translates to
//     mysqli_stmt_bind_param(
//         $stmt, $types, 
//         $form_photo_title, $form_member_name, $form_type
//     );
array_unshift($values, $stmt, $types);
call_user_func_array("mysqli_stmt_bind_param", $values);

mysqli_stmt_execute($stmt);

// this instructs mysqli to assign each field in your query to each of 
// these variables for each row that is returned by mysqli_stmt_fetch(). 
// this is also positional - if you change the order or number of fields 
// in your query, you will need to update this.
mysqli_stmt_bind_result($photo_id, $member_name, $photo_title, $photo_film, $photo_height, $photo_width);

while (mysqli_stmt_fetch($stmt)) {
 // $photo_id will be reassigned to the value from the row on each 
 // loop iteration
 echo $photo_id."<br />";
}

I forgot what a ghastly beast the mysqli extension is - if you have access to the PDO extension, I cannot recommend any more strongly that you learn your way around it and use it instead.

Answer 3

The form_member_name needs to be in quotes?

Actually, both that and the form_photo_title need to be properly handled to prevent SQL Injection attacks.

Answer 4

My guess is something wrong with the SQL you are trying to execute. Print the SQL to the output before the mysqli_query() command. Then take that string and run it from a mysql console to see if the query can be executed.

Answer 5

$query .= "AND photo.photo_film = $form_type ";

I think it must be:

$query .= "AND photos.photo_film = $form_type ";

You misspelled the table name and got an error like "Table doesn't exist".

And don't forget about the SQL-injections. This code is vulnerable.