Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

select count(*) with mysql is one too large

Posted on 2004-08-07
4
Medium Priority
?
18,638 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 150 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
Suggested Courses

824 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