Merging two separate MySQL SELECT statements into single table output

Other Questions MySQL
1

I’m working with two different MySQL queries that currently display their results in separate tables. My goal is to merge these results into one unified table display.

Currently I have one query that pulls product information (ID, name, price, quantity, etc.) and another query that gets category names. I want to add the category name as an additional column in my main product table.

Here’s what I’m working with:

<?php 
    $productQuery = mysql_query('SELECT 
        items.product_id AS "prod_id", 
        items.title AS "product_name", 
        items.cost AS "product_price", 
        items.min_order AS "minimum_qty", 
        items.barcode AS "product_code", 
        items.stock AS "available_qty",
        items.status AS "is_active" 
        FROM shop_product_details items
        INNER JOIN shop_products products
        ON items.product_id=products.product_id
        limit 10');

    while ($data = mysql_fetch_array($productQuery)) {
        echo('<tr><td>'.$data['prod_id'].'</td><td>'.$data['product_code'].'</td><td>'.$data['product_name'].'</td><td>'.$data['available_qty'].'</td><td>'.$data['is_active'].'</td><td>'.$data['minimum_qty'].'</td><td>'.$data['product_price'].'</td></tr>');
    }
?>

<?php 
    $categoryQuery = mysql_query('SELECT 
        cat_prod.product_id AS "prod_id", 
        cat_details.category_name AS "cat_name" 
        FROM shop_category_products cat_prod
        INNER JOIN shop_category_details cat_details
        ON cat_prod.category_id=cat_details.category_id
        ORDER BY prod_id
        limit 10');

    while ($data = mysql_fetch_array($categoryQuery)) {
        echo('<tr><td>'.$data['prod_id'].'</td><td>'.$data['cat_name'].'</td></tr>');
    }                                               
?>

How can I combine these two queries so the category name appears as an extra column in my product table? I want everything displayed in one table row per product.

2

Just add another JOIN to your product query to grab the category info. Since your table relationships are already there, you just need to extend what you’ve got.

Here’s how to modify your first query:

<?php 
    $combinedQuery = mysql_query('SELECT 
        items.product_id AS "prod_id", 
        items.title AS "product_name", 
        items.cost AS "product_price", 
        items.min_order AS "minimum_qty", 
        items.barcode AS "product_code", 
        items.stock AS "available_qty",
        items.status AS "is_active",
        cat_details.category_name AS "cat_name"
        FROM shop_product_details items
        INNER JOIN shop_products products
        ON items.product_id=products.product_id
        INNER JOIN shop_category_products cat_prod
        ON items.product_id=cat_prod.product_id
        INNER JOIN shop_category_details cat_details
        ON cat_prod.category_id=cat_details.category_id
        limit 10');

    while ($data = mysql_fetch_array($combinedQuery)) {
        echo('<tr><td>'.$data['prod_id'].'</td><td>'.$data['product_code'].'</td><td>'.$data['product_name'].'</td><td>'.$data['available_qty'].'</td><td>'.$data['is_active'].'</td><td>'.$data['minimum_qty'].'</td><td>'.$data['product_price'].'</td><td>'.$data['cat_name'].'</td></tr>');
    }
?>

This kills your second query completely. Don’t forget to update your table header to include the category column too.