Ack! Sorry for the wait. Try this (see below for explanation of the code, and what you'll need to do to tweak it for your case):
The code is hopefully pretty self-documenting. But let me explain each thing:
Hope this helps...! (and I hope it's fairly clear).
- Ben
PHP Code:
<?php
require_once("/path/to/global/api/api.php");
$rooms = array("BIO 301", "GEA 403", "GEA 247", "GEA 337B", "GEA 100");
$form_id = 1;
?>
<table>
<?php
// loop through each of the rooms and query the database for problems reported
// for each room
foreach ($rooms as $room)
{
$query = mysql_query("
SELECT *
FROM {$g_table_prefix}form_{$form_id}
WHERE room = '$room'
");
while ($row = mysql_fetch_assoc($query))
{
echo "<tr>
<td>{$room}</td>
<td>{$row["checker"]}</td>
<td>{$row["submission_date"]}</td>
<td>{$row["ip_address"]}</td>
<td>{$row["technical_issue"]}</td>
<td>{$row["status"]}</td>
</tr>";
}
// if there weren't any problems reported for this room, output a blank row
if (mysql_num_rows($query) == 0)
{
echo "<tr>
<td>{$room}</td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
</tr>";
}
}
?>
</table>
The code is hopefully pretty self-documenting. But let me explain each thing:
- First off, edit the path to the Form Tools API. You can either enter a relative or an absolute path to the file - but NOT a URL!
- It start off by creating an array of all possible rooms in the database (I just put 5, but you can pad it out to include all the rooms you have in the dropdown). Note that the display value and the actual value of the dropdown field should be the same (e.g. <option value="GEA 100">GEA 100</option>). If not, it's no problem but you'll need to change the values in the $rooms array to store the VALUES of the dropdown, not the DISPLAY values. The values are what's stored in the database, hence that's what you'll be running your query against.
- Next, you need to set the $form_id to the ID of your own form.
- This next bit may be a little confusing, but hopefully not too bad. All the SQL queries depend on the actual database column names of your fields. So for example this bit...
PHP Code:SELECT *
FROM {$g_table_prefix}form_{$form_id}
WHERE room = '$room'
...assumes the field that stores the room is called "room". To find out what your fields are called, log into Form Tools, edit the form and click on the database tab. That page lists the database column names for each field. It never hurts to rename them from the default col1_, col_2, ... etc. to something more descriptive. The "Smart Fill" button at the top of the column does this for you.
Once you've settled on an appropriate database column name, edit that SQL query to change the WHERE clause. e.g.
PHP Code:WHERE myroomfield = '$room'
- Lastly, you'll need to do the same for the various fields outputted in the HTML table row generation part:
PHP Code:echo "<tr>
<td>{$room}</td>
<td>{$row["checker"]}</td>
<td>{$row["submission_date"]}</td>
<td>{$row["ip_address"]}</td>
<td>{$row["technical_issue"]}</td>
<td>{$row["status"]}</td>
</tr>";
The 1st, 3rd and 4th columns will probably be left alone, but you'll need to tweak the others.
Hope this helps...! (and I hope it's fairly clear).
- Ben