why do i get a number when i should'nt?

[rootKID]

Hey W3S!

 

I'm trying to "COUNT" my "users" table in the DataBase.

And it DOES work... i think.. the fun part is that i don't have any users added in my DataBase yet, and still i get the number 1...

 

This is how i have done the SQL/Call so far:

$user_count_res = sql_query("SELECT COUNT(*) FROM users") or sqlerr(__FILE__, __LINE__);$user_count_row = mysql_num_rows($user_count_res);$registered = ($user_count_row == 0) ? 0 : $user_count_row;

and with this code i should get "0"... but i get above that "1"... even when there is no users existing yet.

 

I call it with the variable "$registered"...

 

Any ideas? Coz i'm a little lost myself x)...

 

-rootKID

 

--Thanks for reading and help if possible!

[Sora]

mysql_num_rows() returns the number of rows returned by a query.

 

Since you are using COUNT(), the query will always return one row, the result of COUNT, even if this is 0.

 

Example from shell

mysql> SELECT COUNT(*) FROM users;+----------+| COUNT(*) |+----------+|    21912 |+----------+mysql> SELECT COUNT(*) FROM users;+----------+| COUNT(*) |+----------+|        0 |+----------+

Edited March 12, 2014 by Sora

[rootKID]

is there no way to fix? Or am i misunderstanding? Oo...

 

EDIT:

 

What i ment was that it looks the same code from my side and one gives 0 while the top code gives a higher number...

so all i mean is that i understand it like i cannot fix it... or am i mistaken?

Edited March 12, 2014 by rootKID

[Sora]

Replace

SELECT COUNT(*) FROM users

with

SELECT id FROM users

id, or any other column.

Avoid * for performance reasons.

Edited March 12, 2014 by Sora

[rootKID]

ohh.... got it! Thanks! x)

[Ingolme]

Logically, this line has a big redundancy:

$registered = ($user_count_row == 0) ? 0 : $user_count_row;

It's the same as writing:

$registered = $user_count_row;

[justsomeguy]

Many databases are optimized specifically for COUNT(*). It will be much faster and less memory-intensive to tell the database to count how many rows are in the table then it will be to tell it to return all rows. Just give the column an alias.

$user_count_res = sql_query("SELECT COUNT(*) AS num FROM users");$row = mysql_fetch_assoc($user_count_res);$count = $row['num'];

And stop using the mysql extension. Use mysqli or PDO.