Welcome to WebmasterWorld Guest from 107.20.28.48

Forum Moderators: open

Message Too Old, No Replies

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

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

Preferred Member

10+ Year Member

joined:Aug 20, 2004
posts: 615
votes: 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?
12:52 am on May 21, 2012 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:May 31, 2005
posts:1108
votes: 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.
4:03 pm on May 21, 2012 (gmt 0)

Senior Member

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Nov 28, 2004
posts:7999
votes: 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.
3:47 am on May 23, 2012 (gmt 0)

Preferred Member

10+ Year Member

joined:Aug 20, 2004
posts: 615
votes: 0


Rocknbil -

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

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members