Counting number of rows

[LearningCoder]

Good morning,

How can I modify my select query to count the number of rows?

Here is my query:

PHP Code:

$sql = 'SELECT username, DATE_FORMAT(join_date, "%d/%m/%y") AS join_date_str
         FROM users
         ORDER BY join_date 
         DESC
         LIMIT 5'; 


I've tried adding COUNT(id); but it only returns 1 row and it overwrites the username. It does count the number of rows correctly though.

I've looked on mysql reference and googled but can't seem to find the answer I am looking for!

Regards,

LC.

[Celtboy]

does this not do what you want?

$sql = 'SELECT username, DATE_FORMAT(join_date, "%d/%m/%y") AS join_date_str, COUNT(*)
         FROM users
         ORDER BY join_date 
         DESC
         LIMIT 5';

I may not understand what you're after.

[LearningCoder]

That's exactly how I tried it.

I also tried putting it before the username field but it seems to overwrite my username field and it only prints one row of data. When I take it out I have my desired result. I need to count how many users there are so I can print out the result on another part of my page.

Kind regards,

Lc.

[Celtboy]

Oh. Yeah. once you toss in a COUNT, it aggregates everything into a single row. The solution would be to add a GROUP BY clause...or actually that will probably not give you the correct row count. You'll either need to use a subquery, or join the table on itself.

This isn't exactly elegant (i'd prefer multiple SQL queries) but...

SELECT username, DATE_FORMAT(join_date, "%d/%m/%y") as join_date_str,
(SELECT COUNT(*) FROM users) AS num_users
FROM users
ORDER BY join_date
DESC
LIMIT 5;

should do it.

[LearningCoder]

That works great.

Thank you very much.

Kind regards,

LC.