[Resolved] mysql_num_rows and query missing data

helz

what i have noticed with mysql_num_rows is that if you call it after a mysql_query and then try to access that same mysql result then the query has been lost.
eg:

$query = "SELECT * FROM table";
$result = mysql_query($query);
$numrows = mysql_num_rows($result); //this returns 1, so there is something in the database
while ($data = mysql_fetch_array($result)) {
/*
this appears to be empty which would make it seem like there aren't any entries in the databse
*/
}

ive forgotton how i fixed it/got around it last time

anyone have any ideas?

Roger_Ramjet

From the user notes in the manual for mysql_num_rows

aaronp123 att yahoo dott comm
22-Feb-2003 01:40
I may indeed be the only one ever to encounter this - however if you have a myisam table with one row, and you search with valid table and column name for a result where you might expect 0 rows, you will not get 0, you will get 1, which is the myisam optimised response when a table has 0 or one rows. Under "5.2.4 How MySQL Optimises WHERE Clauses" it reads:

*Early detection of invalid constant expressions. MySQL quickly detects that some SELECT statements are impossible and returns no rows.

and

*All constant tables are read first, before any other tables in the query. A constant table is:
1) An empty table or a table with 1 row.
2) A table that is used with a WHERE clause on a UNIQUE index, or a PRIMARY KEY, where all index parts are used with constant expressions and the index parts are defined as NOT NULL.

Hopefully this will keep someone from staying up all night with 1146 errors, unless I am completely mistaken in thinking I have this figured out.

This would appear to be your answer as to why an empty result set returns a count of 1.

Nasty behaviour in my opinion and not something I was aware of before. Certainly something that should be emphasised in the manula. Count 1 against MySQL for this false behaviour.

Roger_Ramjet

On the other hand, I've never found that num rows moves the cursor: must be a version specific bug. If it is the case that your resultset is not empty and the cursor is moving then reset it with mysql data seek

helz

having access to phpmyadmin i know there is data in the database.

i think it is a version specific bug as it has happened before, like you said, as if mysql_num_rows has moved the cursor

ps.
the bug only seems to move the cursor one record ahead as when i wanted to loop through a few results, it starts from the second result after i used the num_rows function

helz

actually i think ive remembered what i did to fix it.
i fixed the sql query lol 🙁