mysql returns a resource for a row that doesn't exist

Forum Moderators: open

Message Too Old, No Replies

mysql returns a resource for a row that doesn't exist

neophyte

12:47 am on May 21, 2012 (gmt 0)

Hello All -

I'm trying to display an error notification if a SELECT query contains the wrong information in a WHERE clause.

For example:

$row = 1;

$sql =
"SELECT *
FROM tbl_attack_log
WHERE fld_row_id = '" . $row . "'";

Now, fld_row_id = 1 doesn't exist in this db, but a resource is still returned. I thought that mysql would throw an error (like it does if the table name was wrong, etc) under this circumstance, but it doesn't.

I've thought about using mysql_num_rows, but there are times where zero rows exist in this table which is fine.

Does mysql generate any kind of error like "row number specified doesn't exist"? If not, is there a way to evaluate the resource in php to trap for this kind of error?

Dijkgraaf

12:52 am on May 21, 2012 (gmt 0)

Do a search for check for zero rows returned mysql and the scripting language you are using and you will find plenty of examples.

rocknbil

4:03 pm on May 21, 2012 (gmt 0)

It's not mySQL that's generating the resource, it's PHP. If there's any resource, it's "Empty set (0.00 sec)"

Rows don't exist is easy. You don't need to call another function mysql_num_rows, or evaluate $result - $result will only not be present if the select statement errors. There will, or will not, be rows. So

// SEE BELOW
$sql = "select * from tbl_attack_log where fld_row_id=$row";

$result = mysql_query($sql) or die("cannot query attack log: " . mysql_error());

// You're only expecting a single result. Use IF, not WHILE
// Also careful not to tromp on your $row variable
if ($foundrows = mysql_fetch_array($result)) {
// Output something from $foundrows array
}
else { die("No record found for id $row"; }

For a while statement,

$output=null;
$sql = "select * from tbl_attack_log";

$result = mysql_query($sql) or die("cannot query attack log: " . mysql_error());

while ($foundrow = mysql_fetch_array($result)) {
// concatenate to $output
$output .= $foundrow[1] . ' ' . $foundrow[2];
}

if ($output) { echo $output; }
else { die("No records found"; }

Note that you do NOT need to quote numeric selects in myslql, and scalar variables WILL interpolate fine with double quotes. If $row is empty or null,

SELECT * FROM tbl_attack_log WHERE fld_row_id = '';

will give you a result, it just won't have anything in it. This is a feature that will help you debug:

SELECT * FROM tbl_attack_log WHERE fld_row_id = ;

will error, alerting you to a problem.

neophyte

3:47 am on May 23, 2012 (gmt 0)

Rocknbil -

Thanks very much... very helpful (as usual)