A complicated PHP mySQL query...

[Brendon Bauer]

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:

<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. Thanks in advance!

Brendon

 

[Brendon Bauer]

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.

 

[blaster_boy]

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).

 

[Brendon Bauer]

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:

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?

 

[blaster_boy]

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

 

[Brendon Bauer]

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

<?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.

 

[blaster_boy]

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 ?