I have a database that is to be used as an online scheduling of resources. The database is set up with the following tables (note: table names are the first bullet points, and column names are indented under each table name):
- consultant
- consultant_id
- f_name
- l_name
- user_name
- password
- client
- client_id
- client_name
- event_type
- event_id
- event_type
- event_full_name
- billing_status
- billing_id
- billing_type
- dates (Note that all available dates are listed in this table, however there are some dates that are not able to be used for my purposes so not every day is listed here. Think of it as an inventory of available days.)
- date_id
- date
- calendar_event
- calendar_event_id
- consultant_id
- client_id
- event_id
- billing_id
- date_id
Every time a consultant is assigned to an event, it gets added to the calendar_event table, and a new calendar_event_id is created.
On my PHP page, I have the following query:
$query = "SELECT * FROM billing_status, calendar_event, client, consultant, dates, event_type WHERE (consultant.consultant_id = calendar_event.consultant_id AND client.client_id = calendar_event.client_id AND event_type.event_id = calendar_event.event_id AND billing_status.billing_id = calendar_event.billing_id AND dates.date_id = calendar_event.date_id) ORDER BY calendar_event.date_id";
$consultantresults = mysql_query($query) or die ('Query failed: ' . mysql_error());
I am then displaying the output as follows:
echo "<html><head></head><body><table>";
while ($consultantresult=mysql_fetch_array($consultantresults))
{
$consultantname = $consultantresult[f_name];
$consultantname .= " ";
$consultantname .= $consultantresult[l_name]; // to display the first and last names together
echo "<tr style=\"background-color:#eee;\"><th>Date</th><th>" . $consultantname . "</th></tr>
<tr><td>" . date('D M d, Y', strtotime($consultantresult[date])) . "</td><td>" . $consultantresult[client_name] . " " . $consultantresult[event_type] . "</td></tr>";
}
echo "</table></body></html>";
} else {
header("Location: /index.php");
exit;
}
This manages to display all the records contained in the calendar_event table, but each record is displayed in its own rows like:
<table>
<tr style="background-color:#eee;"><th>Date</th><th>Consultant #1</th></tr>
<tr><td>Mon Aug 05, 2013</td><td>Client A</td></tr>
<tr style="background-color:#eee;"><th>Date</th><th>Consultant #2</th></tr>
<tr><td>Mon Aug 05, 2013</td><td>Client B</td></tr>
<tr style="background-color:#eee;"><th>Date</th><th>Consultant #3</th></tr>
<tr><td>Mon Aug 05, 2013</td><td>Client C</td></tr>
<tr style="background-color:#eee;"><th>Date</th><th>Consultant #1</th></tr>
<tr><td>Tue Aug 06, 2013</td><td>Client D</td></tr>
</table>
You'll notice that the first three entries are on the same date (date gets repeated each time), and the first and last entries are for the same consultant (name also gets repeated).
Instead, I'd like to have the table displayed with each unique consultant's name in the top heading row, and each unique date in the first column. None of the names or dates should be repeated in the first row/column. For example:
<table>
<tr style="background-color:#eee;"><th>Date</th><th>Consultant #1</th><th>Consultant #2</th><th>Consultant #3</th></tr>
<tr><td>Mon Aug 05, 2013</td><td>Client A</td><td>Client B</td><td>Client C</td></tr>
<tr><td>Tue Aug 06, 2013</td><td>Client D</td><td> </td><td> </td></tr>
</table>
It will be possible for a consultant to not be assigned on a particular day, so that day would be blank for them (I used as a placeholder).
I will also need to provide the end user with a way of only displaying certain dates, rather than every single entry in the entire calendar_event table.
Any ideas how I can modify the code above to get it to display the output correctly?
