Jump to content

formatting one-to-many sql result


mickyginger1981

Recommended Posts

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

 

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.