Page 1 of 1

SELECT count(*) FROM table, show invalid results

Posted: Wed May 19, 2004 10:56 am
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!

Posted: Wed May 19, 2004 11:02 am
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]; 
?>

Posted: Wed May 19, 2004 11:03 am
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'];

Posted: Wed May 19, 2004 11:16 am
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]

Posted: Wed May 19, 2004 11:18 am
by yosuke_
Thank you!

Posted: Wed May 19, 2004 11:32 am
by feyd
last I read, affected_rows() doesn't work on SELECT's, am I wrong?

Posted: Wed May 19, 2004 1:46 pm
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).