formatting one-to-many sql result

[mickyginger1981]

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

 

[Eiolon]

Have you tried grouping by the authorcourse in your SQL query?