[SMARTY] Re: Nested Queries

* Gary Smith <[email protected]>:
> I have a database that has two tables  CUSTOMERS and RESERVATIONS. A
> customer can have multiple reservations. I would like to display the
> customers name and the types of reservations they made. Without templates I
> would embed a second query within the results of the first query to find all
> reservations with the current customerID. I can¹t figure out how to do this
> with templates.
>
> I have a file setup as such:
> index.html
> $query=
> <<<SQL
> SELECT     *
> FROM       CUSTOMERS
> INNER JOIN  RESERVATIONS
> ON         CUSTOMERS.customerID =   RESERVATIONS.customerID
> ORDER BY   CUSTOMERS.customerLastName ASC, CUSTOMERS.customerFirstName ASC
> SQL;
> $data = $DB->GetAssoc($query);
> $tpl->assign('data', $data);

Obviously, the issue here is going to be that with multiple reservations
for a single customer, you're going to end up with multiple records for
the same customer. (Basic one-to-many relationship.)

The trick is to grab your list of customers, and then loop through that
list grabbing the reservations for each (I'm assuming GetAssoc() works
like the method in PEAR::DB):

    $query =<<<EOQ
    SELECT *
    FROM CUSTOMERS
    ORDER BY customerLastName ASC, customerFirstName ASC
    EOQ;
    $customers = $DB->GetAssoc($query);

    $query =<<<EOQ
    SELECT *
    FROM RESERVATIONS
    WHERE customerID = ?
    EOQ;
    foreach ($customers as $key => $customer) {
        $id = $customer['id'];
        $reservations = $DB->GetAssoc($query, false, array($id));
        $customers[$key]['reservations'] = $reservations;
    }
    $tpl->assign('data', $customers);

Then, what you do in the template is loop over the customers, and then,
within that loop, loop over their reservations (basic nested loop):

{foreach from=$data item='entry'}
    <b>{$entry.customerFirstName} {$entry.customerLastName}</b><br />
    <ul>
    {foreach from=$entry.reservations item='reservation'}
        <li>{$reservation.reservationType}</li>
    {foreachelse}
        <li>No reservations for this customer</li>
    {/foreach}
    </ul>
{foreachelse}
    <b>No customers found</b>
{/foreach}

Hope that helps!

-- 
Matthew Weier O'Phinney
Zend Certified Engineer
http://weierophinney.net/matthew/

-- 
Smarty General Mailing List (http://smarty.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php