CODEWITHSUNDEEP
mysql
A Garcia
A Garcia

Reputation: 27

Two SQL queries in one sql

I have two queries which I want to combine together in order to get information from two different tables into one array. 

 $stmt = $pdo->query("SELECT * FROM Product WHERE id=". $productid);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        $itemData = array(
            'id' => $row['id'],
            'name' => $row['name'],
            'unitprice' => $row['unitprice'],
            'quantity' => 1
        );

I would like to add this to the same stmt

SELECT size FROM ProductDetails where id=".$productid);

and then have the $itemData array as follows:

$itemData = array(
                'id' => $row['id'],
                'name' => $row['name'],
                'size' => $row['size'],
                'unitprice' => $row['unitprice'],
                'quantity' => 1
            );

Is there a possible way to do this? Thank you.

Upvotes: 0

Views: 59

Answers (3)

Dybrn
Dybrn

Reputation: 61

Use a join in your SQL query and only select the fields you need.

$stmt = $pdo->query("SELECT p.id, p.name, pd.size, p.unitprice FROM Product as p INNER JOIN ProductDetails as pd ON p.id = pd.productid WHERE p.id=". $productid);
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    $itemData = array(
        'id' => $row['id'],
        'name' => $row['name'],
        'size' => $row['size'],
        'unitprice' => $row['unitprice'],
        'quantity' => 1);

Upvotes: 0

Kasia Gogolek
Kasia Gogolek

Reputation: 3414

You want to use a left join here

The MySQL LEFT JOIN clause allows you to query data from two or more database tables. The LEFT JOIN clause is an optional part of the SELECT statement, which appears after the FROM clause.

So in your example:

SELECT * FROM Product 
LEFT JOIN ProductDetails ON ProductDetails.product_id = Product.product_id
WHERE id=". $productid

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520978

Try using a join:

SELECT p.*, pd.size
FROM Product p
INNER JOIN ProductDetails pd
    ON p.id = pd.productid;

Upvotes: 0

Related Questions