Hi there,
So I have data which I am fetching from two tables link in a one-to-many relationship using the following code:
sql = "select authcourses.id as id, title, date_format(closingdate, '%d.%m.%y') as date, name from authcourses inner join authorities on authorityid = authorities.id order by authorityid";
$result = mysqli_query($link, $sql);
if(!$result)
{
$error = 'Unable to get list of authorities';
include '../../error.html.php';
exit();
}
while ($row = mysqli_fetch_array($result))
{
$authcourses[] = array('name' => $row['name'], 'title' => $row ['title'], 'date' => $row['date'], 'id' => $row['id']);
}
I then output the data in a table like this:
<table>
<thead><th>Title</th><th>Closing Date</th><th>Action</th></thead>
<tbody>
<?php foreach ($authcourses as $authcourse): ?>
<tr>
<form action="?" method="post">
<tr>
<td><?php htmlout($authcourse['name']); ?></td> <!-- custom function htmlout($text) see helpers.inc.php -->
<td><?php htmlout($authcourse['title']); ?></td>
<td><?php htmlout($authcourse['date']); ?></td>
<td>
<input type="hidden" name="id" value="<?php echo $authcourse['id']; ?>" />
<input type="submit" name="action" value="Edit" />
<input type="submit" name="action" value="Delete" />
</td>
</tr>
</form>
<?php endforeach; ?>
</tbody>
</table>
The htmlout() is a custom function which is basically "echo htmlspecialchars($str)", and you can ignore the form stuff.
I get an out put like this:
York, Course 1, 2011-02-15
York, Course 2, 2011-03-01
Manchester, Course 3, 2011-06-17
Manchester, Course 4, 2011-08-12
Derby, Course 5, 2011-01-10
Barnet, Course 6, 2011-08-19
Barnet, Course 7, 2011-06-23
etc etc...
What I want is something like this:
York:
Course 1, date
Course 2, date
Manchester:
Course 3, date
Course 4, date
Derby:
Course 5, date
Barnet:
Course 6, date
Course 7, date
etc..
I guess I'd like to load an array for each of the authorities (cities) with the course info, then I can output it in my form like this:
<?php foreach ($authcourses as $authcourse): ?>
<h1><?php htmlout($authcourse['name'] ?>: </h1>
<?php foreach ($courses as $course): ?>
<p><?php htmlout($course['title'];?><p>
<p><?php htmlout($course['date'];?></p>
<?php endforeach; ?>
<?php endforeach; ?>
I hope that makes sense... Any help would be great.
Cheers,
Mike