SitePoint Sponsor

User Tag List

Results 1 to 6 of 6

Thread: Group By?

  1. #1
    SitePoint Evangelist
    Join Date
    Apr 2007
    Posts
    398
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Group By?

    Hi Guys,

    Just wondering if I have:

    Seven records saved in my database with the date: 2007-06-28
    Eight records saved in my database with the date: 2007-06-29
    Nine records saved in my database with the date: 2007-06-30

    Is there any way I can group the records without actually knowing the date ie writing Where date =

    in mysql statement.

    Thanks

    Chris

  2. #2
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    GROUP BY
    datefieldname

    but exactly what are you trying to do? Because what this will do is only return you three total rows.

    perhaps you can show us by way of example data what you want to do.

    If all you mean is show them in order then you would use

    ORDER BY
    datefieldname

    and then they would show up in sequence by date with all 24 rows showing.

  3. #3
    SitePoint Evangelist
    Join Date
    Apr 2007
    Posts
    398
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well what I kinda wanted to do is Order by date as you say and then kind <br /> each of them after each row ends in php? Is this possible.

    Thanks

  4. #4
    An average geek earl-grey's Avatar
    Join Date
    Mar 2005
    Location
    Ukraine
    Posts
    1,403
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by chris_stfc View Post
    Well what I kinda wanted to do is Order by date
    Code sql:
    SELECT field_list
      FROM TABLE_NAME
    ORDER
        BY DATE DESC

    Quote Originally Posted by chris_stfc View Post
    as you say and then kind <br /> each of them after each row ends in php? Is this possible.
    Not sure what you mean by this.

  5. #5
    SitePoint Zealot
    Join Date
    Jun 2007
    Posts
    150
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    How about:

    SELECT date, COUNT(date) AS count FROM table GROUP BY date ORDER BY date DESC;

    this would allow you to do:

    while ($row ...) {
    $out .= "<br />{$row['count']} records saved in my database with the date: {$row['date']}";
    }

    echo substr($out, 6);

  6. #6
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Do you understand how GROUP BY works? It collapses rows based on select criteria. So if you have 10 rows for June 30th and you use a GROUP BY on the date field, you will end up with a single row.

    It sounds like you don't want to use GROUP BY at all but just to order your records using the ORDER BY as suggested above.


Bookmarks

Posting Permissions

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