SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Member
    Join Date
    Aug 2007
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    return topic by perdate

    Hi guys I'm new to this community and I already have a problem that I need help with

    I was trying to display the title by date for example this is what in my database


    id | title | date (unix timestamp)
    -----------------------------
    1 | Title 1 | 1186599216
    2 | Title 2 | 1186599163
    3 | Title 3 | 1186598897
    4 | Title 4 | 1186598731

    Now this is my php code

    PHP Code:
    $begin_datestamp time() - (24 60 60);
    $end_datestamp time();

    $this->DB->query("SELECT title FROM table WHERE date BETWEEN $begin_datestamp AND $end_datestamp");

    while(
    $row $this->DB->fetch_row())
    {
        echo 
    $row['title']."<br>";

    Wanted outcome to be something like below:

    August 3, 2007: Title 4

    July 2, 2007: Title 1, Title2

    July 1, 2007: Title 3

    I just couldn't figure out in my mind and the design for the php code.

    Thanks
    Last edited by all4one; Aug 9, 2007 at 13:35. Reason: Different approach day of the week no longer work

  2. #2
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,252
    Mentioned
    161 Post(s)
    Tagged
    1 Thread(s)
    Use the DAYOFWEEK with the FROM_UNIXTIME function and then use a GROUP BY on the Day of Week value.

    Example:
    SELECT title, DAYOFWEEK(FROM_UNIXTIME(date, GET_FORMAT(DATETIME, 'USA'))) AS `dayOfWeek` FROM table WHERE date BETWEEN $begin_datestamp AND $end_datestamp GROUP BY `dayOfWeek`

    Matt

  3. #3
    SitePoint Member
    Join Date
    Aug 2007
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for replying

    Maybe I should do a different approach because the day of the week is no longer work for me.

    My new approach is the out come it could display like this

    August 3, 2007: Title 4

    July 2, 2007: Title 1, Title2

    July 1, 2007: Title 3

  4. #4
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,252
    Mentioned
    161 Post(s)
    Tagged
    1 Thread(s)
    That I would do in an ORDER BY statement, order by the date descending, and then loop through the results.

    As you read each result, add the title to an array for the appropriate date, such as:

    Code:
    $myarray[$date] += $title
    or build a multi-dimensional array based on date.

    Code:
    array_push($myarray[$date], $title);
    Then at the end, you can look through the array, displaying the date once, followed by all of the titles.

    Matt

  5. #5
    SitePoint Member
    Join Date
    Aug 2007
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks I will get to it


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
  •