Where do I do this?

[eluser]LrDarko[/eluser]
Hello.

I'm freaking out with CI. Let me explain what I want to do.


Two tables, one "product" and the other "image".

First query: I retrieve all the fields from products (including product_id).

Second query: I retrieve one field called "URL" from the table "image" with the product_id of the product I get from the first query.


Each product has to appear with its image on the webpage.

How could I do that?

If you could write some example of this with controller, model and view I would appreciate that because I'm seriously thinking of giving up on CI.

[eluser]danmontgomery[/eluser]
This has very little to do with CI, it's basic database functionality.

The query in plain SQL would be:

Code:

SELECT product.*, image.url
FROM product
LEFT JOIN image ON image.product_id = product.id

Assuming you'd want to use CI's active record, that would be:

Code:

$this->db->select('product.*, image.url')->join('image', 'image.product_id = product.id', 'left')->get('product');

[eluser]LrDarko[/eluser]
No it can't be a join.


The image table has more entries of one single product. If the product has two images it would appear twice the same product...

I have to retrieve one data and make another query with that data.

[eluser]WanWizard[/eluser]
That depends on how you deal with this problem.

I would go for noctrum's query, and solve the display issue in the code:

Code:

$product = FALSE;
foreach ( $query_result as $result )
{
    // do we have a product change?
    if ( $result->product != $product )
    {
        // display the product information
        $this->load->view( 'product', $result );
        // store the product
        $product = $result->product;
    }
    // display the product image
    $this->load->view( 'product_image', $result );
}

If you go for separate queries, you have to run N+1 queries (where N is the number of products in your first query), and you still have to create similar logic to process the result of these queries.

You gain nothing by splitting it, you're only making it more complex.

[eluser]LrDarko[/eluser]
WanWizard you are right.

I was thinking of splitting it like MVC does with all the retraiving data and formatting data... I was messed.

Thanks a lot!