SELECT count(*) FROM table, show invalid results

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
yosuke_
Forum Commoner
Posts: 64
Joined: Tue Apr 13, 2004 12:29 pm

SELECT count(*) FROM table, show invalid results

Post by yosuke_ »

Hi!
Well Here is my code, to check how much records are in database

Code: Select all

<?php
mysql_connect("localhost","user","password");
mysql_select_db("data");
$query_count = "SELECT count(*) FROM users";      
$result_count = mysql_query($query_count);      
$totalrows = mysql_num_rows($result_count);
echo $totalrows;
?>
The result I get is 1. When I open MySQL Monitor and type: SELECT count(*) FROM users; I get this:
+----------+
| count(*) |
+----------+
| 11
+----------+
1 row in set (0.01 sec)
The result is 11. When I type there SELECT username FROM users; I get all 11 usernames, but why does my code displays that there are only one record? Thank you!
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

your code is wrong, why use mysql_num_rows, when the query is counting the rows. Makes no sense.

Anyway

Code: Select all

<?php 
mysql_connect("localhost","user","password"); 
mysql_select_db("data"); 
$query_count = "SELECT count(*) FROM users";      
$result_count = mysql_query($query_count);      
$totalrows = mysql_fetch_array($result_count); 
echo $totalrows[0]; 
?>
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post by pickle »

count(*) does the work of counting the rows for you. MySQL counts *, then puts it in one record, and outputs. So, when you are only getting one record like this, that one record contains a count of all records. You can get this information in 2 ways. The first would be to change your query to:

Code: Select all

SELECT
   *
FROM
  users
The second would be to change your query to:

Code: Select all

SELECT
  count(*) as count
FROM
  users
Then:

Code: Select all

$result_row = mysql_fetch_assoc($result_row);
$totalrows = $result_row['count'];
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
duk
Forum Contributor
Posts: 199
Joined: Wed May 19, 2004 8:45 am
Location: London

Post by duk »

if you want to know how many rows are affected by the request

use this simple code...

Code: Select all

<?php
 
$query_count = "SELECT * FROM users";      
$result_count = mysql_query($query_count);      
$totalrows = mysql_affected_rows($result_count);
echo "$totalrows";

//$totalrows now have the affected rows by the request from $result_count 

?>

?>
[/php_man]
yosuke_
Forum Commoner
Posts: 64
Joined: Tue Apr 13, 2004 12:29 pm

Post by yosuke_ »

Thank you!
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

last I read, affected_rows() doesn't work on SELECT's, am I wrong?
User avatar
launchcode
Forum Contributor
Posts: 401
Joined: Tue May 11, 2004 7:32 pm
Location: UK
Contact:

Post by launchcode »

feyd - you're not wrong, using affected_rows on a SELECT query is utterly pointless (as is doing a SELECT * - what a complete waste of CPU! Just count it like you're supposed to).
Post Reply