help putting data from multiple tables into an array

[whypie]

i am trying to get data from two tables and put them into an array that i can return to my template. - i should mention that im quite new to php.

 

here are my tables:

 

horses:

 

id, name, owner_id

 

owners:

 

id, name

 

i have a function called get_horses() that returns details on all of the horses, but i want it to also get the owner name and put that into the array, how can i do this?

 

 

  1. class horse_details {

  2.   

  3.    function get_horses(){

  4.        $query = "SELECT * FROM horses";

  5.        $result = mysql_query($query);

  6. 

  7.        $horses = array();

  8. 

  9.        while($tmp = mysql_fetch_assoc($result)) {

  10.            $horses[] = $tmp;

  11.        }

  12. 

  13.        return $horses;

  14.    }

  15. 

  16. }

 

my template:

 

  1. {foreach item=horse from=$horses}

  2. <tr>

  3.    <td>{$horse.name}</td>

  4.    <td>{$horse.breed}</td>

  5.    <td><a href="owners.php?action=view&owner_id={$horse.owner_id}">{$i.want.the.owner.name.here}</a></td>

  6. </tr>

  7. {foreachelse}

  8. <tr>

  9.    <td colspan="3">No items were found</td>

  10. </tr>

  11. {/foreach}

[tommyda]

You should post code within

 tags, to make it easier to read.

[gijew]

You could use a JOIN to get the data in one query. So now the owner id would be associated with the horse.

 

 

<?php
$query = "SELECT * FROM horses LEFT JOIN owners ON horses.owner_id = owners.id";
?>