mysql_num_rows returns incorrect value

Dave_TC

I am new to php & mysql and have a problem getting mysql_num_rows to return the correct number of rows. Here is my code:

$tr110 = mysql_query("SELECT ad FROM classified WHERE category_number=110");
$rc110 = mysql_num_rows($tr110);
print " ($rc110)";

Here is the problem: If 0 rows exist mysql sometimes returns 0 and sometimes returns 1. If 1 or more rows exist, it always returns 1 more than the number of rows that exist. Please help!!

PHP Version 4.3.2
MySQL Version 4.0.15
MAC OS X (Darwin)

also tried on
PHP Version 4.3.4
MySQL Version 3.23.49
Windows NT 5.2

planetsim

Welcome to the forums 😉

Weird problem. Not sure i quite understand you. So ill throw a question back at you.

So lets say your category_number has 1 record of 110 it throws back the output as 2? You may want to run the query also in PHPMyAdmin to double check.

Dave_TC

Thanks for your response. I think we are on the same page. For example: Category 110 will actually have 1 record, but the above code will return 2. I use Cocoa MySQL and when I query from there it returns the correct number of records. Pretty weird huh?

FMB

What happens if you do this:

Print $rc110;

Absolutely no need to put it in quotes, nor ()

tsinka

Hi,

how does your table look like (column types, indexes and index types).

You might have a problem with the way how MySQL optimizes WHERE clauses.

Thomas

Dave_TC

Thanks for your responses FMB and tsinka;
FMB - I use " ()" for formatting the user frontend. For example:
Food (3)
Firewood (5)
Without " ()" the page displays like this:
Food3
Firewood5
Do you think the way I am displaying my query result is affecting the query?

tsinka - The table only has 3 fields:
1. ID - auto-incrementing integer used as primary key
2. category_number - I use varchar for that as the field may contain letters ie: 140d
3. ad - I use text as the type
I hope that I gave you the information you were looking for. I really appreciate the help.

TheDefender

The way you are displaying your results should have no effect on the query itself....

Change this:

$tr110 = mysql_query("SELECT ad FROM classified WHERE category_number=110");

to this:

$tr110 = mysql_query("SELECT ad FROM classified WHERE category_number='110'");

Notice the addition of the single quotes around the value of 110... See if that helps any.

Dave_TC

Hello TheDefender, thanks for the response. I tried the single quotes idea but nothing changed.

stew

instead of using mysql_num_rows() you could use COUNT in the SQL statement

look at www.mysql.com/count just a little bit down the page...

tsinka

Hi,

which indexes did you create on that table ?

Thomas

FMB

Print "(" . $rc110 . ")";

stew has a point too:

 $tr110 = mysql_query("SELECT count(ad) FROM classified WHERE category_number=110");
$rc110 = mysql_fetch_array($tr110);
print "(" . $rc110[0] . ")";