Zend Framework - Displaying data from two tables

Question

This is my current schema - shown partially.

CREATE TABLE cart (
username VARCHAR(60),
productid INT(5),
quantity INT(6),

PRIMARY KEY (username, productid),
FOREIGN KEY (username) REFERENCES login(username),
FOREIGN KEY(productid) REFERENCES product(productid)
);

CREATE TABLE product (
productid INT(5) NOT NULL AUTO_INCREMENT,
name VARCHAR(30) NOT NULL,
description VARCHAR(1000),
price VARCHAR(6) NOT NULL,
stocks INT(6) NOT NULL,

PRIMARY KEY (productid)
);

I have this function in my CartController:

public function indexAction()
{   
    $auth= Zend_Auth::getInstance(); 
    $user= $auth->getIdentity();
    $username   = $user->username;

    $cart = new Application_Model_DbTable_Cart();
    $fetch = $cart->fetchAll($cart->select()->where('username = ?', $username));
    $this->view->cart = $fetch;     
}

Which is being called by my index.phtml:

<?php foreach($this->cart as $cart) :?>
<tr>
<td><?php echo $this->escape($cart['productid']);?></td>
<td></td>
<td><?php echo $this->escape($cart['quantity']);?></td>
<td></td>
<td>    
    <a href="<?php echo $this->url(array('controller'=>'product', 
        'action'=>'edit', 'id'=>$product->productid));?>">Edit</a>
</td>   
<td>    
    <a href="<?php echo $this->url(array('controller'=>'product', 
        'action'=>'delete', 'id'=>$product->productid));?>">Delete</a>
</td>
</tr>
<?php endforeach; ?>

What is the most elegant way to display the product name and price in the table?

devnate · Accepted Answer · 2012-08-07 19:55:30Z

1

What you are looking for is a join. This is how I would usually do it.

$select = $cart->select()
    ->from(array('c' => 'cart'))
    ->join(array('p' => 'product', 'p.productid = c.productid')
    ->where('username = ?', $username);
$fetch = $cart->fetchAll($select);

Then you can just get the data from your $cart variable like you are with quantity.

answered Aug 7, 2012 at 19:55

devnate

7645 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ayrx Over a year ago

Thanks, that almost worked but i had to add setIntegrityCheck(false).

Collectives™ on Stack Overflow

Zend Framework - Displaying data from two tables

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related