Can't Figure Out How To Display Data Properly

[scd1982]

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!

[kicken]

You need to group the data together in a PHP array by day/person before you output anything. Your query is going to return only one person/date combination per row, but you are trying to treat it like there are multiple people per row. You need to re-arrange the data to match for format first.

 

During you while loop that gets the info from the query, generate an array that looks like:

array(
   '2013-6-24' => array(
        '1' => array(
            'client' => 'XYZ Corp'
            , 'event' => 'Teaching'
        )
        , '2' => array(
            'client' => 'ABC Corp'
            , 'event' => 'Exec Dev'
        )
        , '3' => array(
            'client' => 'AAA Corp'
            , 'event' => 'Maintenance'
        )
   ///...
)

The first level of the array is indexed by date, which maps to the rows for your output table. The second level is indexed by the consultant id, which maps to the columns in your output table. Once you have that array generated, you just output it something like:

foreach ($dates as $day=>$consultants){
   echo '<tr>';
   echo '<td>'.$day.'</td>';
   echo '<td>'.$consultants[1]['client'].' | '.$consultants[1]['event'].'</td>';
   echo '<td>'.$consultants[2]['client'].' | '.$consultants[2]['event'].'</td>';
   echo '<td>'.$consultants[3]['client'].' | '.$consultants[3]['event'].'</td>';
}

Of course, you'd probably want to make the output of the consultants across the table dynamic as well, but I'll leave that as an exercise for the reader.

[scd1982]

You need to group the data together in a PHP array by day/person before you output anything. Your query is going to return only one person/date combination per row, but you are trying to treat it like there are multiple people per row. You need to re-arrange the data to match for format first.

Would I benefit from a different table structure? I thought about scrapping the "calendar_event" table, and altering the "dates" table to add columns for: consultant_id, client_id, event_id, billing_id? But then that would just be about the same thing as the current calendar_event table. I don't have any live data in the database right now, so re-working the table structure won't result in any loss of data - definitely open to that if it will help.

 

As for your suggestion with the arrays, is there a way I could set up an array to act like you described, but pull the dates/client names/events from the database rather than manually typing in each one? I'm sure if that is possible, then I probably don't need to have a static header for the consultant names either - those should be able to be pulled from the database as well, right?

 

Sorry if this seems like a stupid question. As I mentioned, I'm a little new to this and arrays baffle me.

[scd1982]

Would something like this work? Obviously the echo result would need to be formatted in table cells, but to display the result, this is what I have so far.

 

When I use this, it just returns an empty page (viewing the page source also has no content - not even head/body tags).

while ($consultantresult=mysql_fetch_array($consultantresults))
		{
		$events = array(
			$consutantresult[date]=>array(
				$consultantresult[client_name]=>array(
					$consultantresult[event_type]=>array(
						$consultantresult[billing_color]
						)
					)
				)
			);
		foreach ($events as $date=>$client=>$type=>$billing)
		{
		echo $date . " " . $billing . " " . $client . " " . $type;
		}
		}