asked on

select count(*) with mysql is one too large

Hello-
When I run the following:

      $recordCount = "select count(*) from memberInfo where lname like 'B%'";
      $totalRowsResult = mysql_query($recordCount,$conn);
      $totalRows = mysql_fetch_row($totalRowsResult);
      echo $totalRows[0];

$totalRows[0] = 25. However, if I run select * from memberInfo where lname like 'B%' it returns 24 records. Why is select count(*) counting one too many when run within PHP? If i run both queries from the MySQL client, they both indicate that there are 24 rows. Do I just always have to subtract one from whatever is returned when I do select count(*) from PHP?

Thanks-
Joel

ASKER CERTIFIED SOLUTION

ldbkutty

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jdaynes

ASKER

wow--it looks like I'm an idiot. I accidentally included a % in one like clause that wasn't there in another, causing the discrepancy. Sorry for my stupidity....

VIkasumit

Hi..

I know that the Answer is already accepted But I want to clear you that the two queries you are talking about are completely different ..

count(*) return that you have 25 records in the database matching the where clause....

and select * from ... return the first records first column not the COUNT as you refer....
If you want to see how many records are there in the second query you should use mysql_num_rows( $totalRowsResult ) ...

I hope you read this and Correct your logic there...

VIkasumit

ldbkutty

>> I hope you read this and Correct your logic there...

I dont think there is something wrong in the logic.

The author justed wanted to *verify* the number of records with SELECT * FROM memberInfo WHERE lname LIKE 'B%' and SELECT count(*) FROM memberInfo WHERE lname LIKE 'B%'.

Ofcourse, both are different but the former will return 24 rows and the later returns 24.