Page 1 of 1

putting data from multiple tables into an array

Posted: Wed Sep 02, 2009 1:58 pm
by 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?

Code: Select all

class horse_details {
    
    function get_horses(){
        $query = "SELECT * FROM horses";
        $result = mysql_query($query);
 
        $horses = array();
 
        while($tmp = mysql_fetch_assoc($result)) {
            $horses[] = $tmp;
        }
 
        return $horses;
    }
 
}
my smarty template:

Code: Select all

{foreach item=horse from=$horses}
<tr>
    <td>{$horse.name}</td>
    <td>{$horse.breed}</td>
    <td><a href="owners.php?action=view&owner_id={$horse.owner_id}">{$i.want.the.owner.name.here}</a></td>
</tr>
{foreachelse}
<tr>
    <td colspan="3">No items were found</td>
</tr>
{/foreach}
any help would be great. - please let me know if you need any of the other code.

Re: putting data from multiple tables into an array

Posted: Wed Sep 02, 2009 4:09 pm
by Benjamin
:arrow: Moved to PHP - Code

Re: putting data from multiple tables into an array

Posted: Wed Sep 02, 2009 4:44 pm
by Mark Baker
Assuming that every horse has an owner

Code: Select all

 
SELECT H.*,
       O.name AS ownerName
  FROM horses H,
       owners O
 WHERE O.id = H.owner_id
 

Re: putting data from multiple tables into an array

Posted: Wed Sep 02, 2009 5:42 pm
by whypie
excellent. the join worked. thanks. :lol: