I am attempting to generate an online schedule using PHP and a MySQL database. I would like to display the schedule to the end user in a table format, with the first header row listing all consultant names, and the first column displays all available dates (an example of the desired output is below).
The tables right now are layed out as follows (with sample data):
[/th]billing_status billing_idbilling_typebilling_color 1Billablered 2Non-Billableblue 3Tentativegreen
client client_idclient_name 1ABC Corp 2XYZ Corp 3AAA Corp
consultant consultant_idf_namel_nameuser_namepassword 1JoeSmithjs@example.com1234 2JohnDoejd@example.com5678 3SamJohnsonsj@example.com9012
dates date 2013-06-24 2013-06-25
*Note: since each date is only listed once the date serves as the primary key
event_type event_idevent_type 1Maintenance 2Teaching 3Exec Dev
calendar_event calendar_event_idconsultant_idclient_idevent_idbilling_iddate 112222013-06-24 221322013-06-24 333112013-06-24 412232013-06-25 521312013-06-25 633132013-06-25
The information contained in each cell of the table can be drawn from the "calendar_event" table including where it belongs in the table using "consultant_id" and "date".
My query is:
$query = "SELECT * FROM calendar_event ce
LEFT JOIN billing_status bs ON ce.billing_id = bs.billing_id
LEFT JOIN client cl ON ce.client_id = cl.client_id
LEFT JOIN consultant co ON ce.consultant_id = co.consultant_id
LEFT JOIN dates dt ON ce.date = dt.date
LEFT JOIN event_type et ON ce.event_id = et.event_id
GROUP BY ce.consultant_id, ce.date
ORDER BY ce.date";
I then start my table heading row as follows:
<table class=\"calendar\"><tr class=\"head\">
<th>Date</th>
<th>Joe Smith</th>
<th>John Doe</th>
<th>Sam Johnson</th></tr>
And finally, I am using the following to pull the schedule out of the database:
while ($consultantresult=mysql_fetch_array($consultantresults))
{
echo "<tr>
<td>" . date('D M d, Y', strtotime($consultantresult[date])) . "</td>
<td class=\"" . $consultantresult[billing_color] . "\">" . $consultantresult[client_name] . " | " . $consultantresult[event_type] . "</td>
<td class=\"" . $consultantresult[billing_color] . "\">" . $consultantresult[client_name] . " | " . $consultantresult[event_type] . "</td>
<td class=\"" . $consultantresult[billing_color] . "\">" . $consultantresult[client_name] . " | " . $consultantresult[event_type] . "</td></tr>";
}
echo "</table>";
The end result I would like to get should look something like this:
DateJoe SmithJohn DoeSam Johnson Mon Jun 24, 2013XYZ Corp | TeachingABC Corp | Exec DevAAA Corp | Maintenance Tue Jun 25, 2013XYZ Corp | TeachingABC Corp | Exec DevAAA Corp | Maintenance
Instead, I get:
DateJoe SmithJohn DoeSam Johnson Mon Jun 24, 2013XYZ Corp | TeachingXYZ Corp | TeachingXYZ Corp | Teaching Mon Jun 24, 2013ABC Corp | Exec DevABC Corp | Exec DevABC Corp | Exec Dev Mon Jun 24, 2013AAA Corp | MaintenanceAAA Corp | MaintenanceAAA Corp | Maintenance Tue Jun 25, 2013XYZ Corp | TeachingXYZ Corp | TeachingXYZ Corp | Teaching Tue Jun 25, 2013ABC Corp | Exec DevABC Corp | Exec DevABC Corp | Exec Dev Tue Jun 25, 2013AAA Corp | MaintenanceAAA Corp | MaintenanceAAA Corp | Maintenance
The actual result pulls the first record out of the calendar_event table, and fills in that info across the first row, second record's info is filled in across the second row, etc. all while ignoring the consultant name, and repeating dates.
What am I doing wrong here? I'm sure it's a simple fix, but I'm somewhat new to this. Thanks in advance for any help!
