Solved

select count(*) with mysql is one too large

Posted on 2004-08-07
4
18,556 Views
Last Modified: 2013-12-12
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
0
Comment
Question by:jdaynes
  • 2
4 Comments
 
LVL 32

Accepted Solution

by:
ldbkutty earned 50 total points
ID: 11743992
>> If i run both queries from the MySQL client, they both indicate that there are 24 rows

Then you *must* get 24 rows unless you have some more additional codes/changes in your PHP.
0
 

Author Comment

by:jdaynes
ID: 11744015
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....
0
 
LVL 4

Expert Comment

by:VIkasumit
ID: 11910303
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
0
 
LVL 32

Expert Comment

by:ldbkutty
ID: 11911069
>> 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.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question