April 12, 2012

PHP, MYSQL – Query fails but no error meessage appears

Question by S.e. Estes

Back again, thanks for all the help last time. I’m running this query:

 $query = "SELECT * FROM event where evLoc = ".$loc." AND evChar = ".$char;
 var_dump($query);
 $result = mysql_query($query, $con) or die('Error 1:'.mysql_error());
 if ($result) {
    $row = mysql_fetch_array($result) or die('Error 2:'.mysql_error());
    var_dump(mysql_num_rows($result));
    exit();

I get a message Error 2: but no mysql_error printed out. The var_dump($query) printed out a query that ran without errors in phpMyAdmin. The var_dump(mysql_num_rows($result)) did not print. Ideas?

Answer by Michael Berkowski

This is a case of being too cautious and applying error checking where it doesn’t belong.

Don’t call die() in partnership with a fetch call. The fetch intentionally returns FALSE when there are no rows available, so you don’t have an error, just no rows.

// No rows were returned, wich is FALSE
$row = mysql_fetch_array($result) or die('Error 2:'.mysql_error());

Instead, don’t call die() here:

$row = mysql_fetch_array($result);
if ($row) {
  // you got something
}

Or this way:

if ($row = mysql_fetch_array($result)) {
  // you got something.
}

If multiple rows are expected to be returned, fetch in a while loop.

while ($row = mysql_fetch_array($result)) {
  // loops until you're out of rows
  // or not at all if you have no rows
}

Answer by Starx

Apply Single Quotes in the fields of your query

 $query = "SELECT * FROM event where evLoc = '".$loc."' AND evChar = '".$char."'";

You can write these in short form too. Like

$query = "SELECT * FROM event where evLoc = '$loc' AND evChar = '$char'";

Next, you might want to change your fetch portion.

while($row = mysql_fetch_assoc($result)) { 
 ....
}

When you use this, you will avoid the error you would receive when no rows are returned.

Author: Nabin Nepal (Starx)

Hello, I am Nabin Nepal and you can call me Starx. This is my blog where write about my life and my involvements. I am a Software Developer, A Cyclist and a Realist. I hope you will find my blog interesting. Follow me on Google+

...

Please fill the form - I will response as fast as I can!