I want to do join query from 2 different table and i want output from both table

Question

I apply LEFT JOIN and RIGHT JOIN query to get output from both table..

There is two table :

1st table for Product_list.. This table contain category_id which is foreign key and all other product detail..

2nd table for product_images.. This table contain product_id which is foreign key and product images separately..

I want product detail and product images for particular category_id..

I use this Query but not work properly :

SELECT * 
FROM product_list
LEFT JOIN product_images ON product_list.pro_id = product_images.pro_id
WHERE product_list.cat_id =  '.$id.'
UNION 
SELECT * 
FROM product_list
RIGHT JOIN product_images ON product_list.pro_id = product_images.pro_id

I get this output:

API Code

<?php
error_reporting(0);

$response = array();

require_once __DIR__ . '/db_Connect.php';

// check for post data
if (isset($_GET["cat_id"])) {

    $id = $_GET['cat_id'];

    // get a product from products table
    //$q="SELECT * FROM product_list WHERE cat_id ='".$id."' ORDER BY pro_id DESC ";
    $q="SELECT * 
FROM product_list
LEFT JOIN product_images ON product_list.pro_id = product_images.pro_id
WHERE product_list.cat_id =  '.$id.'
UNION 
SELECT * 
FROM product_list
RIGHT JOIN product_images ON product_list.pro_id = product_images.pro_id WHERE product_list.cat_id =  '.$id.'";
    //print($q);
    $res = mysql_query($q);
    print($res);
    //exit();
    if (mysql_num_rows($res) > 0) {

        $responce["category_sub_list"]=array();

        // check for empty result
        while($result = mysql_fetch_array($res)) {

        $product=array();
        $product['pro_id']=$result['pro_id'];
        $product['cat_id']=$result['cat_id'];  
        $product['product_name']=$result['product_name'];
        $product['image']="http://friendzfashionz.com/pandora/admin/Sub_uploads/".$result['image'];
        $product['product_desc']=$result['product_desc'];

        array_push($responce["category_sub_list"],$product);
        } 
         $responce["success"]=1;
         echo json_encode($responce);

    } else {
        // no product found
        $response["success"] = 0;
        $response["message"] = "No user found";

        // echo no users JSON
        echo json_encode($response);
    }
} else {
    // required field is missing
    $response["success"] = 0;
    $response["message"] = "Required field(s) is missing";

    // echoing JSON response
    echo json_encode($response);
}
?>

What is your current output and what is your expected output? Can you please post here? — SRK, Mar 16 '18 at 10:54
it works in php myadmin properly but it didn't work in my API...@SmitRaval — Bhoomi Patel, Mar 16 '18 at 11:09
echo this "SELECT * FROM product_list LEFT JOIN product_images ON product_list.pro_id = product_images.pro_id WHERE product_list.cat_id = '.$id.' UNION SELECT * FROM product_list RIGHT JOIN product_images ON product_list.pro_id = product_images.pro_id WHERE product_list.cat_id = '.$id.'" and run the query in phpmyadmin, check if it works or not? — SRK, Mar 16 '18 at 11:30
its work in phpmyadmin....it gives proper output in phpmyadmin but it didn't work in my API..@SmitRaval — Bhoomi Patel, Mar 16 '18 at 11:36
SELECT * FROM product_list LEFT JOIN product_images ON product_list.pro_id = product_images.pro_id WHERE product_list.cat_id = '6' UNION SELECT * FROM product_list RIGHT JOIN product_images ON product_list.pro_id = product_images.pro_id WHERE product_list.cat_id = '6' @SmitRaval — Bhoomi Patel, Mar 16 '18 at 11:56
Remove '' from id.In your query put product_list.cat_id=".$id." in first query and in second query product_list.cat_id=".$id; — SRK, Mar 16 '18 at 11:59
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/166955/discussion-between-smit-raval-and-bhoomi-patel). — SRK, Mar 16 '18 at 12:04

score 0 · Accepted Answer · answered Mar 16 '18 at 10:57

Please try this query. You need to apply where condition in both queries.

SELECT * 
FROM product_list
LEFT JOIN product_images ON product_list.pro_id = product_images.pro_id
WHERE product_list.cat_id =  '.$id.'
UNION 
SELECT * 
FROM product_list
RIGHT JOIN product_images ON product_list.pro_id = product_images.pro_id WHERE product_list.cat_id =  '.$id.'

I want to do join query from 2 different table and i want output from both table

1 Answers1