Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    Regular Coder LearningCoder's Avatar
    Join Date
    Jan 2011
    Location
    The Pleiades
    Posts
    912
    Thanks
    76
    Thanked 28 Times in 28 Posts

    Counting number of rows

    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.

  • #2
    Regular Coder
    Join Date
    May 2002
    Location
    Virginia, USA
    Posts
    620
    Thanks
    0
    Thanked 6 Times in 6 Posts
    does this not do what you want?
    Code:
    $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.

  • #3
    Regular Coder LearningCoder's Avatar
    Join Date
    Jan 2011
    Location
    The Pleiades
    Posts
    912
    Thanks
    76
    Thanked 28 Times in 28 Posts
    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.

  • #4
    Regular Coder
    Join Date
    May 2002
    Location
    Virginia, USA
    Posts
    620
    Thanks
    0
    Thanked 6 Times in 6 Posts
    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...

    Code:
    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.

  • Users who have thanked Celtboy for this post:

    LearningCoder (02-24-2013)

  • #5
    Regular Coder LearningCoder's Avatar
    Join Date
    Jan 2011
    Location
    The Pleiades
    Posts
    912
    Thanks
    76
    Thanked 28 Times in 28 Posts
    That works great.

    Thank you very much.

    Kind regards,

    LC.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •