mickyginger1981 Posted December 3, 2010 Share Posted December 3, 2010 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 Quote Link to comment Share on other sites More sharing options...
Eiolon Posted December 3, 2010 Share Posted December 3, 2010 Have you tried grouping by the authorcourse in your SQL query? Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.