homepage Welcome to WebmasterWorld Guest from 54.197.110.151
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

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

10+ Year Member



 
Msg#: 4455735 posted 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

WebmasterWorld Senior Member 5+ Year Member



 
Msg#: 4455735 posted 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

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



 
Msg#: 4455735 posted 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

10+ Year Member



 
Msg#: 4455735 posted 3:47 am on May 23, 2012 (gmt 0)

Rocknbil -

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

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved