A complicated PHP mySQL query...

Discussion in 'Web Design and Development' started by Brendon Bauer, Apr 14, 2011.

  1. macrumors 6502

    #1
    Ok so here is my dilemma. I have an event system and I am trying to come up with some php to accomplish a specific style of output. To begin, I have a database that contains 3 tables:

    1. The first table (events) contains the events and their data. It consists of: eventID, eventTitle, eventDateTime, and eventDescRaw. There are a few more fields but I don't need them for this.

    2. The second table (events_categories) contains: categoryID and categoryTitle. This table essentially gives an ID we can work with to the different event categories.

    3. The third table (events_to_categories) contains: eventID and categoryID. This table just links the events to the categories they belong to. One event can belong to multiple categories (leading to multiple rows in this table with the same eventID).

    What I am trying to achieve is this:

    I would like to query the database and pull all events (from today's date on) and output as such:

    Code:
    <ul>
       <li>
          First Month (ie April)
          <ul>
              <li>
                   Day of the month
                   <ul>
                       <li>Event title and description (first event on this date)</li>
                       <li>Another event on the same day, etc</li>
                   </ul>
              </li>
              <li>
                   Another day of the month
                   <ul>
                        <li>Event title and description (only one event is on this date</li>
                   </ul>
              </li>
         </ul>
      </li>
      <li>
          Start the process all over again with the second month (ie May)
      </li>
    </ul>
    
    So essentially I'm outputting as

    Month - Days - Events
    Month - Days - Events

    in unordered lists. I'm not repeating the string "April" for each event. Let alone the day for each event if multiple events occur on the same day.

    I've uploaded a screenshot of what I'm talking about, but this example only has the info for one month. Imagine the month of May and it's events listed under it as well. (http://cl.ly/60hc)

    The reason I included the information about the categories is because it would be nice if I could specify which categories I want included. My main purpose is to include all categories, but it would be nice to be able to output in a similar style for one specific category in another area of the website.

    I hope this makes sense... and now I turn to the experts here to help me figure this out as I can't quite seem to wrap my head around this one. :eek: Thanks in advance!

    Brendon
     
  2. macrumors 6502

    #2
    I guess I should add that I'm not afraid to get my hands dirty with php. I can usually figure my way around scripts and modify them for my needs. But at this point I'm not even sure how to approach this--the logic behind it.
     
  3. macrumors 6502

    blaster_boy

    #3
    As a general remark without going into details, you could use the GROUP BY in your sql query to group your events by date / category... this would help you get the results back already ordered how you want (or at least partly).
     
  4. macrumors 6502

    #4
    Thanks for the tip! I think you are steering me in the right direction. Here's what I've been able to come up with so far using Group By. My query looks like this:

    Code:
    SELECT YEAR(`eventDateTime`) as `year`, MONTH(`eventDateTime`) as `month`, DAY(`eventDateTime`) as `day`, `eventID`, `eventDateTime`, `eventTitle`, `eventDescRaw` FROM `events` GROUP BY `year`, `month`, `day` ASC
    This gives me an output (taken from phpMyAdmin) that looks like this: http://cl.ly/62Mt

    Now my next problem is how to loop through those rows and produce an output like I wanted above (without repeating identical years, months, days if they exist) I'm pretty rusty on my php and I've been reading through functions but I'm not quite sure what I should be doing next.

    Any more helpful hints from the experts here on MacRumors? :D
     
  5. macrumors 6502

    blaster_boy

    #5
    Your php is probably way more fu than my php - I've switched over to Python just about completely.

    In python I would loop through that list of events, and keep note of which month I'm running in :

    (this is not python, just me thinking aloud)
    - set current_month to None
    - set current_day to None
    - set html_text to ""
    - read the very first event date (closest one)
    Loop Starts Over All Events
    - Extract the month out of it
    - Is it the same month as current_month
    yes -> continue
    no -> add month on a separate line to html_text and set current_month to month
    - Extract the event day of the month out of it
    - Is it the same day as current_day ?
    yes -> continue
    no -> add date on a seperate line to html_text and set current_day to day
    - Display the event itself : add the event description on a sperate line to html_text
    - Get the next event
    Loop Ends

    Display html_text
     
  6. Brendon Bauer, Apr 15, 2011
    Last edited: Apr 15, 2011

    macrumors 6502

    #6
    Thanks so much! After much trial and error and a few headaches along the way, I came up with something that seems to work. It may not be the most elegant or easiest way to do it, but it's what I could come up with using my rusty php and referencing php.net :p. If anyone else has any pointers or suggestions to make this better, please share them! I am no expert and this is probably not perfect.

    Code:
    <?php
    	/*mysql query*/
    	$link = mysql_connect('localhost', '**username**', '**password**');
    	mysql_select_db("**database**");
    	$sql = "SELECT YEAR(`eventDateTime`) as `year`, MONTH(`eventDateTime`) as `month`, DAY(`eventDateTime`) as `day`, `eventID`, `eventDateTime`, `eventTitle`, `eventDescRaw` FROM `events` [B]ORDER BY[/B] `year`, `month`, `day` ASC";
    	$result = mysql_query($sql);
    	$num = mysql_numrows($result);
    	mysql_close($link);
    	/* variables */
    	$i=0;
    	$curmonth=0;
    	$curday=0;
    	$eventhtml="<ul class='eventcal'>";
    	$startm="";
    	$startd="";
    	/* loop */
    	while ($i < $num) {
    		/* get current row */
    		$resyear=mysql_result($result,$i,"year");
    		$resmonth=mysql_result($result,$i,"month");
    		$resday=mysql_result($result,$i,"day");
    		$resid=mysql_result($result,$i,"eventID");
    		$restitle=mysql_result($result,$i,"eventTitle");
    		$resdesc=mysql_result($result,$i,"eventDescRaw");
    		$resdate=strtotime(mysql_result($result,$i,"eventDateTime"));
    		if ($resmonth!=$curmonth) {
    			/* make new month */
    			$curmonth = $resmonth;
    			$eventhtml .= $startm."<li style='background:none;'><h1 class='eventcal'>".date("F Y",$resdate)."</h1><ul>";
    		}
    		if ($resday!=$curday) {
    			/* make new day */
    			$curday = $resday;
    			$eventhtml .= $startd."<li style='background:none;'><h3 class='eventcal'>".date("j",$resdate)."</h3><ul>";
    		}
    		/* add event to list */
    		$eventhtml .= "<li style='background:none;'><h4 class='eventcal'>".$restitle."</h4><p class='eventcal'>".$resdesc."</p></li>";
    		/* conditions for next row */
    		$inext=$i+1;
    		if ($inext < $num) {
    			if (mysql_result($result,$inext,"month")==$curmonth) {
    				$startd="</ul></li>";
    			} elseif (mysql_result($result,$inext,"month")!=$curmonth) {
    				$startd="";
    			}
    		}
    		/* loop next row */
    		$i++;
    		$startm="</ul></li></ul></li>";
    	}
    	$eventhtml .= "</ul></li></ul></li></ul>";
    	echo $eventhtml;
    ?>
    Thanks blaster_boy for helping me in the right direction! Here is a screenshot of the finished output if anyone is curious: http://cl.ly/61Gh

    UPDATE: I realized I was using the Group By statement wrong... I didn't actually test multiple events on the same day until now and found that if I used Group By it would only list the last event and the first event on that day would just disappear. I now realize I didn't understand Group By, so I'm using Order By instead. That seems to have fixed the problem.
     
  7. blaster_boy, Apr 15, 2011
    Last edited: Apr 15, 2011

    macrumors 6502

    blaster_boy

    #7
    Hey, no problem for helping you out - and you're not the only one who understood GROUP BY wrong... :)

    As for your php : it works, right ? :D
     

Share This Page