foreach loop inside another foreach loop using Custom PHP MVC

Question

I have a table 1 products and Table 2 suppliers

The tables structure is this way:

Products

CREATE TABLE IF NOT EXISTS `products` (
`id_product` int(11) NOT NULL AUTO_INCREMENT,   
`id_supplier` int(11) NOT NULL,   
`name_product` varchar(20) NOT NULL,     
PRIMARY KEY (`id_product`) )
ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

Suppliers

CREATE TABLE IF NOT EXISTS `suppliers` (
`id_supplier` int(11) NOT NULL AUTO_INCREMENT,   
`name_supplier` varchar(11) NOT NULL,    
PRIMARY KEY (`id_supplier`) ) 
ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

Model: (suppliers_model.php)

class Suppliers_Model extends Model{

    public function fetchSuppliers(){
        stmt = $this->db->prepare("SELECT * FROM suppliers");
        $stmt->setFetchMode(PDO::FETCH_OBJ);
        $stmt->execute();
        return $stmt->fetchAll();       
    }
}

Controller (suppliers.php)

class Suppliers extends Controller{
    function __construct(){
        parent::__construct();
    }

    public function index(){
        $this->view->Suppliers= $this->model->fetchSuppliers();
    }
}

View :

<table>
    <tr>
        <th>ID</th>
        <th>Name of supplier</th>
        <th>List of products</th>
    </tr>
    <?php foreach($this->Suppliers AS $key=>$value):?>
    <tr>
        <td><?php echo $value->id_supplier; ?></td>
        <td><?php echo $value->name_supplier; ?></td>
        <td>
                <?php foreach():?>
                    **HERE I Wante To display a liste of products**
                    1. Product A
                    2. Product B
                    etc....
                <?php endforeach;?>
        </td>
    </tr>
    <?php endforeach;?>

How to created another model and a controller to fetch list of products and pass the value

$value->id_supplier

as a condition to display the list of products of each supplier?

I've tried this code but it doesn't work

    public function listProducts($id_supplier){
        stmt = $this->db->prepare("SELECT * FROM products WHERE id_supplier=$id_supplier");
        $stmt->setFetchMode(PDO::FETCH_OBJ);
        $stmt->execute();
        return $stmt->fetchAll();

    }

and added this code to the controller

$this->view->listProducts= $this->model->listProducts('id_supplier');

and tried to use the foreach loop to loop through the products table to retrieve the list of products but it doesn't work

NB: Im working with a custom PHP MVC

Answer 1

Since you already setup the model method to get the products, just use it in your controller.

Don't try to make invocations inside the views, do it inside the controller.

Controller:

public function index()
{

    $suppliers = $this->model->fetchSuppliers();
    foreach($suppliers as &$s) {
        $s->products = $this->model->listProducts($s->id_supplier);
    }

    $this->view->Suppliers = $suppliers; 
}

Then in your view, just continue on what you have:

<table>
    <tr>
        <th>ID</th>
        <th>Name of supplier</th>
        <th>List of products</th>
    </tr>
    <?php foreach($this->Suppliers AS $key=>$value):?>
    <tr>
        <td><?php echo $value->id_supplier; ?></td>
        <td><?php echo $value->name_supplier; ?></td>
        <td>
            <?php foreach($value->products as $k => $p): ?>
               <p><?php echo ($k + 1) '. ' . $p->product_name; ?></p>
            <?php endforeach;?>
        </td>
    </tr>
    <?php endforeach;?>
</table>