issue with SELECT COUNT(id)

Question

I've been using this command to retrieve the number of the fields which have same email address:

$query = $db->query("SELECT COUNT(`user_id`) FROM `users` WHERE `email`='$email'") or die($db-error);

There are 3 records in users table with the same email address. The problem is when I put * instead of COUNT(user_id) it returns correctly: $query->num_rows gives 3 but when I use COUNT(user_id) then $query->num_rows returns 1 all the time. how can I correct this or where is my problem?

Maybe user_id is null in some rows. "The COUNT(column_name) function returns the number of values (NULL values will not be counted) of the specified column:" — Umair Ayub, Mar 31 '14 at 09:27
@ponciste in the question it is `*` instead of `COUNT(user_id)` and not `COUNT(*)` instead of `COUNT(user_id)`. Otherwise `$query->num_rows` could not be `3` for the given query. — t.niese, Mar 31 '14 at 09:33

score 0 · Answer 1 · answered Mar 31 '14 at 09:27

When you use $query->num_rows with that query it will return 1 row only, because there is only one count to return.

The actual number of rows will be contained in that query. If you want the result as an object, or associative array give the count a name:

$query = $db->query("SELECT COUNT(`user_id`) AS total FROM `users` WHERE `email`='$email'") or die($db-error);

And in the returned query total should be 3, while $query->num_rows will still be 1. If you just want the value a quick way would be using $total = $query->fetchColumn();.

As others have said though, be careful with NULL user ids, because COUNT() will ignore them.

score 0 · Answer 2 · edited May 23 '17 at 10:31

0

Emails have to be uinque in users table. Thus, you need no count at all.
You ought to use prepared statements.
You shouldn't post a code that will never run.

Here goes the only correct way to run such a query:

$sql = "SELECT * FROM `users` WHERE `email`=?";
$stm = $db->prepare($sql);
$stm->execute([$email]);
$user = $stm-fetch();

(the code was written due to erroneous tagging. For mysqli you will need another code, but guidelines remains the same.)

Something like this

$sql = "SELECT * FROM `users` WHERE `email`=?";
$stm = $db->prepare($sql);
$stm->bind_param('s',$email);
$stm->execute();
$res = $stm->get_result()
$user = $res->fetch_assoc();

in $user variable you will have either userdata you will need in the following code or false which means no user found. Thus $user can be used in if() statement all right without the need of any counts.

In case when you really need to count the rows, then you use this count() approach you tried. You can use a function from this answer for this:

$count = getVar("SELECT COUNT(1) FROM users WHERE salary > ?", $salary);

edited May 23 '17 at 10:31

Community

1
1

answered Mar 31 '14 at 09:39

Your Common Sense

156,878
40
214
345

If his `email` field is (for some reason) not unique, why shouldn't he count the number of repeated emails he has? – aurbano Mar 31 '14 at 09:58
@Chevi such a reason just doesn't exit. It obviously have to be unique for the users table. Either way, you have to question the Op, not me. – Your Common Sense Mar 31 '14 at 10:00
@YourCommonSense Supposing he really needs repeated emails: I asked in regards to your first point `You don't need no count at all.`. How is getting a list of all the users with that email better than using `COUNT()`? – aurbano Mar 31 '14 at 10:02
Then he has to ask another question and get an answer on that one. – Your Common Sense Mar 31 '14 at 10:04

score -1 · Accepted Answer · answered Mar 31 '14 at 09:27

-1

That's the correct behaviour: If you use the COUNT function, the result of your select query will be just one row with one column containing the number of data sets.

So, you can retrieve the number of users with the given E-mail address like this:

$query = $db->query("SELECT COUNT(`user_id`) FROM `users` WHERE `email`='$email'") or die($db-error);
$row = $query->fetch_row();
$count = $row[0];

Note that this is faster than querying all data using SELECT * and checking $query->num_rows because it does not need to actually fetch the data.

answered Mar 31 '14 at 09:27

Callidior

2,899
2
18
28

thanks. That was right. I was looking for an equal number. Although I didn't get your point then but because of your suggestion I started to study a few function to actually find out how they basically work and what is the principles. It took me 3 hours to find out what you said but now I'm clear about that. It only returns one row. I fetch the row with fetch_row() and then look at the value of the first index which is [0]. The value was 3 exactly as I was expecting. – EhsanSia Mar 31 '14 at 18:41

issue with SELECT COUNT(id)

3 Answers3