CODEWITHSUNDEEP
phpmysqlselectpdo
Alper Aslan
Alper Aslan

Reputation: 47

PDO select multiple table with where clause

I have 4 identical table with same row names with diffrend values. I have send $_GET['urun_barkod'] code from another page so im trying to show matched row on 2nd page with detailed row information.

$barko=$_GET['barkod'];
$urunsor=$db->prepare("SELECT * FROM kanyon UNION ALL SELECT * FROM zorlu UNION ALL SELECT * FROM 
tesvikiye UNION ALL SELECT * FROM citys where urun_barkod=:barkod");
$urunsor->execute(array(
    'barkod' => $barko
));
$uruncek=$urunsor->fetch(PDO::FETCH_ASSOC);

But i cant get the result correctly. Just wanted to show matched barkod row on my 2nd page with details.

What am i doing wrong ?

Upvotes: 0

Views: 149

Answers (2)

nbk
nbk

Reputation: 49375

Your Problem is that your where Clause, works only on the last Select

So You need to en capsule the union and then do your where clause

Like

SELECT 
  * 
FROM
(SELECT * FROM kanyon UNION ALL SELECT * FROM zorlu UNION ALL SELECT * FROM 
tesvikiye UNION ALL SELECT * FROM citys) a
 where urun_barkod=1

The problem is you gather all the data from all Tables and union them, and finally then you remove all the unnecessary rows , which makes no sense at all, because it takes time and resources.

So do better this

SELECT * FROM kanyon  where urun_barkod=1
UNION ALL SELECT * FROM zorlu  where urun_barkod=1 
UNION ALL SELECT * FROM tesvikiye  where urun_barkod=1
UNION ALL SELECT * FROM citys where urun_barkod=1;

Which only unions the actual wanted rows.

In my opinion i would also add a column in the selects to identify the tables, but that me.

Upvotes: 1

David Shindler
David Shindler

Reputation: 109

If the id's are identical, please try: 

"
SELECT     *
FROM    ((kanyon INNER JOIN zorlu ON kanyon.id = zorlu.id)
                INNER JOIN tesvikiye ON kanyon.id = tesvikiye.id)
        INNER JOIN citys ON kanyon.id = citys.id        

where urun_barkod=:barkod
"

Upvotes: 0

Related Questions