Tutorial :PHP mysql_query not returning false



Question:

Just learning PHP and I'm having some trouble understanding mysql_query. My understanding is that mysql_query is supposed to return FALSE if the record is not found. However, it seems that it always returns true because "FOUND!" is always the result:

$q = "SELECT * FROM users WHERE username = 'doesnotexist'";    $r = mysql_query($q);    if (!$q) {      echo "<p>NOT FOUND!</p>";  } else {      echo "<p>FOUND!</p>";  }    mysql_close();  

Thanks in advance for any light you can shed.


Solution:1

mysql_query returns false if there is an error, not if there are no results found. From the documentation:

For SELECT, SHOW, DESCRIBE, EXPLAIN and other statements returning resultset, mysql_query() returns a resource on success, or FALSE on error.

For other type of SQL statements, INSERT, UPDATE, DELETE, DROP, etc, mysql_query() returns TRUE on success or FALSE on error.

mysql_query() will also fail and return FALSE if the user does not have permission to access the table(s) referenced by the query.

If you want to check to see if there were results returned by your query, use mysql_num_rows(). See the documentation:

Use mysql_num_rows() to find out how many rows were returned for a SELECT statement or mysql_affected_rows() to find out how many rows were affected by a DELETE, INSERT, REPLACE, or UPDATE statement.


Solution:2

You are checking the '$q' variable (your sql statement) instead of the '$r' variable (the mysql result)


Solution:3

if (empty($r)) {          echo "<p>NOT FOUND!</p>";  } else {          echo "<p>FOUND!</p>";  }  

The following things are considered to be empty:

"" (an empty string)  0 (0 as an integer)  "0" (0 as a string)  NULL  FALSE  array() (an empty array)  var $var; (a variable declared, but without a value in a class)  


Solution:4

Ok i have worked out the answer to this.

This is the version for updating a database that tells you if a record was valid.

$updatequery = "update `mydb` set `userid` = '$arr[0]', `name` = '$arr[1]' where `age` = '$arr[2]'";    $updatequeryresult= mysql_query($updatequery);    $howmanyupdatedrecords = mysql_affected_rows();    if ($howmanyupdatedrecords == 0) {  echo("The update didn't update any records as no one matched an age of " .$arr[2]");  }  

This will iterate through the DB updating all people with the specified age, if the age does not exist in the DB a message will be displayed showing you the age that does not exist. Also something to note, even if the mysql query matches a record, it won't update the record if the data already matches what it is being updated to. This causes the script to return "no one matched an age" even though there are people who did match. Can only attribute that to a bug in mysql. I told MySQL to update the information I don't see why it should take it upon itself to not bother doing as i told it. ;)


Solution:5

Your 'if (!$q)' should be 'if (!$r)' I think.


Solution:6

if that dosen't work, try this:

<?php  $q = "SELECT * FROM users WHERE username = 'doesnotexist'";  $r = mysql_query($q);  if (!mysql_num_rows($r) >= 1) {          echo "<p>NOT FOUND!</p>";  } else {          echo "<p>FOUND!</p>";  }  mysql_close();  ?>  

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »