running 2 SQL select statements

Question

I am trying to run 2 SQL Select queries to retrieve data from 2 different tables and then echo the fields from both tables. The code I am trying doesn't seem to work, any help would be appreciated.

$ModelID = $_GET['model_id'];

$result = mysqli_query($con, "SELECT RegNumber, Colour FROM Car WHERE ModelID = '$ModelID'

UNION ALL

SELECT CarModel, CarMake, CostPerDay FROM Model WHERE ModelID = '$ModelID'");   

while($row = $result->fetch_assoc())    
{     
    echo $row["CarModel"];
    echo $row["CarMake"];
    echo $row["CostPerDay"];    
    echo $row["RegNumber"];        
    echo " - " .$row["Colour"];         
}

Why are you using an union and not a join on modelId ? – Fky Nov 30 '16 at 16:01 — Fky, Nov 30 '16 at 16:01
I tried googling the solution and got recommended union – Jamie Obrien Nov 30 '16 at 16:03 — Jamie Obrien, Nov 30 '16 at 16:03

score 1 · Accepted Answer · answered Nov 30 '16 at 16:02

1

You can change request :

SELECT c.RegNumber, c.Colour , m.CarModel, m.CarMake, m.CostPerDay FROM Car c INNER JOIN Model m ON m.ModelID=c.ModelID WHERE c.ModelID = '$ModelID'

answered Nov 30 '16 at 16:02

Fky

2,133
1
15
23

score 1 · Answer 2 · answered Nov 30 '16 at 16:07

1

try with LEFT JOIN

$result = mysqli_query($con, 
   "SELECT c.RegNumber, c.Colour, m.CarModel, m.CarMake, m.CostPerDay 
   FROM Car AS c
   LEFT JOIN Model as m ON c.ModelID = m.ModelID 
   WHERE c.ModelID = '".$ModelID."'"
);

answered Nov 30 '16 at 16:07

Gabriele Carbonai

459
4
18

running 2 SQL select statements

2 Answers2