SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    Resistance is Futile webgodjj's Avatar
    Join Date
    Nov 2002
    Location
    Madison, WI USA
    Posts
    448
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    next 7 events by date

    This is a bit both a php and mysql question, however, I thought I would check here first for the query... possibly help with both.

    I have an events table with events stored as either weekly (recurring) or daily (non-recurring) events.

    To query a single day I use the following query:
    Code:
    select event_id, date, event_title, event_repeat, event_desc, event_pict from event where ((date = '$passed_date' AND event_repeat = 'Once') OR ( date <= '$passed_date' AND WEEKDAY(date) = WEEKDAY('$passed_date') AND event_repeat = 'Weekly'))
    However, I now need to do a query that will bring up the next 7 events (different from the next 7 days). Then order them by day...

    Then produce something like so:
    date (date)

    Title (event_desc)
    Description (event_desc)
    Title (event_desc)
    Description (event_desc)
    ...

    date (date)
    Title (event_desc)
    Description (event_desc)
    Title (event_desc)
    Description (event_desc)
    ...

  2. #2
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Please post a SHOW CREATE TABLE statement so we can see the actual structure on which to base a query.

    I am already thinking the query could be something like

    pseudo code:

    select col1 col2 date_of_event
    from table table_name
    where date_of_event >= '$passed_date'
    order by date ASC
    limit 7



    bazz

  3. #3
    SitePoint Member
    Join Date
    Jul 2008
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If I understand the problem correctly, you want to pull the next seven events, independent of their type (i.e. the next events inclusive of recurring or one-off).

    In order to do this in the query, you can either create a temporary table within the query that holds the next X days (the number of days you'd expect to get 7 events within) and join this temp table onto your events table by the weekday
    -or-
    you can use a case statement and add a 'date' column into the query dynamically and sort by that

    Here's an example:

    Code:
    $dates = array('2008-12-01 00:00:00.000', '2008-12-02 00:00:00.000');
    $query = " SELECT ID, (
    
    CASE
    WHEN recur =1
    THEN (
    
    CASE
    WHEN recurDay =1
    THEN '" . $dates[0] . "'
    WHEN recurDay =2
    THEN '" . $dates[1] . "'
    ELSE '0000-00-00 00:00:00.000'
    END
    )
    ELSE date
    END
    ) AS 'synthDate'
    FROM events
    ORDER BY synthDate DESC
    LIMIT 7";
    so you're basically passing the query the dates of the next 7 days, which you can create dynamically in an array. The code above is just doing a case on the type of the event; if it's recurring, it assigns a date to the event based upon which day of the week it takes place. If it's not, it uses its assigned date. Then you just limit the number of values returned to 7.
    Last edited by Intaglio5; Dec 28, 2008 at 14:12. Reason: added code
    SQL optimization, Data management, Back-end development
    http://brainfreezeanalytics.com
    info@brainfreezeanalytics.com

  4. #4
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Is that not very convoluted code?

    (That's a genuine question and not a criticism of you code suggestion Intaglio5).

    bazz

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    yeah, i would attempt to do it all with mysql rather than building arrays with temp tables

    but bazz, it isn't as simple as it first appears, because of the recurring events

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Member
    Join Date
    Jul 2008
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by IBazz View Post
    Is that not very convoluted code?

    (That's a genuine question and not a criticism of you code suggestion Intaglio5).

    bazz
    it's very convoluted but because the events recur weekly, there's no simple, elegant way to get the data.
    SQL optimization, Data management, Back-end development
    http://brainfreezeanalytics.com
    info@brainfreezeanalytics.com

  7. #7
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ahhh. (maybe I think to much)
    However, I now need to do a query that will bring up the next 7 events (different from the next 7 days). Then order them by day...
    I read this to mean that the OP wanted the next seven events irrespective of what day they were on. so if there were three tomorrow, and one on each of the next four days, then showing them in date order ASC and limiting to 7 should do it.

    And if it were repetitive, like a movie house listing, there could be seven on just one day - but wouldn't they all need to be listed because they are showing at different times.

    I think we need the OP to come back from his/her break.

    My other thought was to build a hash which removes repeats and so, returns only the next n results. I don't know if php supports hashes. I use perl.

    bazz

  8. #8
    Resistance is Futile webgodjj's Avatar
    Join Date
    Nov 2002
    Location
    Madison, WI USA
    Posts
    448
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok... Close but off by a bit.... It seems to be doing things right (please check code). The events seem to be coming in right, but the date repeats...

    Code:
    $sql_select = "select date from event where date >= CURDATE()";
    $sql_select_result = mysql_query($sql_select);
    
    while ($row_select = mysql_fetch_object($sql_select_result)) {
    	$format_date = date('M jS, Y',mktime(0, 0, 0, $row->date));
    	echo "<div style=\"color: #ffcc00;font-size: 14px;font-weight:bold;\">".$format_date."</div><br />";
    	$sql = "select event_id, date, event_title, event_repeat from event where ((date = '$row_select->date' AND event_repeat = 'Once') OR (date <= '$row_select->date' AND WEEKDAY('$row_select->date') = WEEKDAY(date) AND event_repeat = 'Weekly'))";
    	$sql_result = mysql_query($sql) or die(mysql_error());
    	while ($row = mysql_fetch_object($sql_result)) {
    		echo "<div style=\"color: #fff;font-weight:bold;\">".$row->event_title."</div><br />";	
    	}
    }


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
  •