Simply MySql Queries to Single Join

Question

I need to combine 2 queries into a single join but my SQL skills are not the best.

Here's my query:

$query = $this->db->query("
    SELECT p2c.product_id, p.model 
    FROM {$this->prefix}product_to_category p2c 
    LEFT JOIN {$this->prefix}product p 
        ON (p2c.product_id = p.product_id) 
    WHERE p.status < '3' 
    ORDER BY p2c.product_id ASC
");

$products = array();

foreach($query->rows as $product):
    $cats = $this->db->query("
        SELECT category_id 
        FROM {$this->prefix}product_to_category 
        WHERE product_id = '" . (int)$product['product_id'] . "'
    ");

    $categories = array();

    foreach($cats->rows as $category):
        $categories[] = $category['category_id'];
    endforeach;

    $products[$product['model']] = array(
        'product_id' => $product['product_id'],
        'categories' => $categories
    );
endforeach;

return $products;

The array I need returned needs to look like this:

[6596-27] => Array
        (
            [product_id] => 243
            [categories] => Array
                (
                    [0] => 7
                    [1] => 88
                )

        )

Obviously with this array being returned for each product in a larger single array.

I'm sure that this can be combined into a single query since the second query comes from one of the first queried tables but I can't understand how to grab all the categories into their own separate array without executing a second query.

While not a direct answer, the [answer to another question](http://stackoverflow.com/a/16598900/1206267) is generally extremely useful. — Ohgodwhy, Jan 27 '14 at 00:13
Your LEFT JOIN is really an INNER JOIN, and I think I don't understand the need for the second query at all. Perhaps an sqlfiddle (AND DESIRED RESULT SET) would help. — Strawberry, Jan 27 '14 at 01:07

score 1 · Accepted Answer · answered Jan 27 '14 at 03:52

This may be a solution:

$query = $this->db->query("

    SELECT p.product_id, p.model, GROUP_CONCAT(p2c.category_id SEPARATOR ',') as categories
    FROM {$this->prefix}product p
    LEFT JOIN {$this->prefix}product_to_category p2c      
        ON (p.product_id = p2c.product_id ) 
    WHERE p.status < 3
    ORDER BY p2c.product_id ASC GROUP BY p.product_id

");

$products = array();

foreach($query->rows as $product):

    if ( ! array_key_exists($product['model'],$products) ){
      $products[$product['model']] = array()
    }

    array_push( $products[$product['model']],  array(
          'product_id' => $product['product_id'],
          'categories' => explode(',', $product['categories'])
      )
    )

endforeach;

return $products;

Great idea thanks. Only a couple changes to this, the GROUP BY clause requires both p.product_id, p.model and needs to come before the ORDER BY clause. Appreciate your help. — secondman, Jan 27 '14 at 04:18

Simply MySql Queries to Single Join

1 Answers1