COUNT()

[The Little Guy]

Why does this:

 

SELECT COUNT(users.id) as userCount FROM users GROUP BY users.id

 

it is returning 41 rows, each with a value of 1?

 

It should return 1 row with a value of 41. Why is it doing that?

[The Little Guy]

What I figured out is that id is a individual number, that is why I am getting that.

 

SO...

 

New approach...

 

Is this the only way to count the total number of rows in a table?

 

SELECT id FROM tablename

 

then in the php use mysql_num_rows

 

OR, is there a better way, one that doesn't select hundreds of rows in one query?

[obsidian]

If you simply want the total number of rows in a table, you don't have to group by anything. Just select the count of the id column:

SELECT COUNT(id) AS count FROM tablename;

 

Then, you can retrieve the result:

<?php
$sql = mysql_query("SELECT COUNT(id) AS count FROM tablename");
$count = mysql_result($sql, 0, 'count');
?>

[The Little Guy]

I found this, does it work as well?

 

SHOW TABLE STATUS FROM murdercup

 

I can narrow it down to a specific table if I want using:

 

SHOW TABLE STATUS FROM murdercup LIKE 'users'

 

I would like to use the first one:

SHOW TABLE STATUS FROM murdercup

 

because it shows more info in one query.

 

but then I need to somehow return the results. So if I go this method, is it possible to select a certain row without using numbers? I was hoping like this, but it doesn't work:

$sql = mysql_query("SHOW TABLE STATUS FROM murdercup");
$row = mysql_fetch_array($sql);

echo $row['Name']=>['users']['Rows'];

the above I was hoping that it would grab column1 "Name", search down it for the row labeled "users" then get the info from row "Rows", and it would return 41

[obsidian]

You can use anything that will give you the right data, but keep in mind, depending on your server setup, you may eventually be working with a server that only has raw writing privileges (INSERT, UPDATE, SELECT, DELETE), so you'll need to be sure you understand how to get the data you're after any way possible, too

[fenway]

Assuming you're using MyISAM, use COUNT() -- unless you want to query the metadata using the I_S tables.