select different columns from multiple tables with common variable

Question

I have three tables: - food - toys - animals

Each table has the columns - id, color, item

Table toys has additional columns - sn, date and more

Need to select all items from all tables with color = red and get them separately on client side

Something like:

$color = 'red';
$sql = "select * from food, toys, animals where color = :acolor";
$st = $db->prepare($sql);
$st->execute([":acolor" => $color]);
$food = $toys = $animals = '';
while($row = $st->fetch()){
        $food .= "<div class='food' data-id = " . $row['food.id'] . ">" . $row['food.item'] . "</div>\n";
        $toys .= "<div class='toys' data-id = " . $row['toys.id'] . " data-serial = " . $row['toys.sn'] . " data-date = '" . $row['toys.date'] . "'>" . $row['toys.item'] . "</div>\n";
    }
        $animals .= "<div class='animals' data-id = " . $row['animals.id'] . ">" . $row['animals.item'] . "</div>\n";
    }
$arr = [];
array_push($arr, $food, $toys, $animals);
echo json_encode($arr);

client side

...
data = JSON.parse(data);
$('#wrapfood').html(data[0]);
$('#wraptoys').html(data[1]); 
$('#wrapanimals').html(data[2]);

As the final result:

wrapfood should have 5 divs with class food
wraptoys should have 9 divs with class toys
wrapanimals should have 21 divs with class animals

I tried various versions of the above code without success - getting errors on server side.

Any help?

@catcon - each select statement within UNION must have the same number of columns — qadenza
– qadenza, Commented Aug 7, 2019 at 5:59
show your exact error message and add a proper data sample and the expcted result .. — ScaisEdge
– ScaisEdge, Commented Aug 7, 2019 at 6:05

BlackXero · Accepted Answer · 2019-08-07 06:07:41Z

2

You can use this

$data = array('food' => array(),'toys' => array(),'animals' => array());
$color = 'red';
$foodSql = "select * from food where color = :acolor";
$toySql = "select * from toys where color = :acolor";
$animalSql = "select * from animals where color = :acolor";
$ft = $db->prepare($foodSql);
$tt = $db->prepare($toySql);
$at = $db->prepare($animalSql);
$ft->execute([":acolor" => $color]);
$tt->execute([":acolor" => $color]);
$at->execute([":acolor" => $color]);
$food, $toys, $animals = '';
while($row = $ft->fetch()){
        $food .= "<div class='food' data-id = " . $row['food.id'] . ">" . $row['food.item'] . "</div>\n";
}
array_push($data['food'], $food);

while($row = $tt->fetch()){
        $toys .= "<div class='toys' data-id = " . $row['toys.id'] . " data-serial = " . $row['toys.sn'] . " data-date = '" . $row['toys.date'] . "'>" . $row['toys.item'] . "</div>\n";
}
array_push($data['toys'],$toys);

while($row = $at->fetch()){
        $animals .= "<div class='animals' data-id = " . $row['animals.id'] . ">" . $row['animals.item'] . "</div>\n";
}
array_push($data['animals'],$animals);
echo json_encode($data);

Then on client side you can access the data as

data = JSON.parse(data);
$('#wrapfood').html(data.food);
$('#wraptoys').html(data.toys); 
$('#wrapanimals').html(data.animals);

answered Aug 7, 2019 at 6:07

BlackXero

8904 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

qadenza Over a year ago

thanks but I hope there is a way with a single select statement.

Sayed Mohd Ali · Accepted Answer · 2019-08-07 06:51:21Z

1

if you want to use a single select statement here is the solution... if you just want a color item and id

SELECT f.* from food f
     UNION 
    SELECT a.* from animals a
     UNION 
    SELECT t.id, t.color,t.item from toys t 
    WHERE t.color ="red" AND f.color="red" AND a.color="red"

if you want sn and date also

SELECT f.*,null,null from food f
 UNION 
SELECT a.*,null,null from animals a
 UNION 
SELECT t.* from toys t 
WHERE t.color ="red" AND f.color="red" AND a.color="red"

if your columns of the tales are in the same order as you specified id, color, and item then no problem with the above queries otherwise if the order is different arrange the column names in select statement accordingly...

now you can still use the above query if you want to separate it only client site in the single loop... just concatenate in the select statement. here is what you can do...

SELECT CONCAT("#food",f.id), CONCAT("#food",f.color),CONCAT("#food",f.item),null,null from food f
 UNION 
SELECT CONCAT("#animals",a.id),CONCAT("#animals",a.color),CONCAT("#animals",a.item),null,null from animals a
 UNION 
SELECT CONCAT("#toys",t.id),CONCAT("#toys",t.color),CONCAT("#toys",t.item),CONCAT("#toys",t.sn),CONCAT("#toys",t.date) from toys t 
WHERE t.color ="red" AND f.color="red" AND a.color="red"

now we have concatenated the names of the tables with the column now when we display it we can easily filter them with their names...

this is how you filter them later on...

if(substr( $row['id'], 0, 5 ) === "#food"){
echo substr($row['id'],4);
}

answered Aug 7, 2019 at 6:51

Sayed Mohd Ali

2,2523 gold badges14 silver badges31 bronze badges

2 Comments

qadenza Over a year ago

can't check your solution now, but anyway - thanks a lot.

Nick Over a year ago

I think you want t.color ="red" OR f.color="red" OR a.color="red"

Collectives™ on Stack Overflow

select different columns from multiple tables with common variable

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related