|
|
Posted by Matthew Weier O'Phinney on 08/10/05 16:28
* Gary Smith <gts@tsu.biz>:
> 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/
Navigation:
[Reply to this message]
|