Warm tip: This article is reproduced from stackoverflow.com, please click
mysql pdo php select

PDO select multiple table with where clause

发布于 2020-04-08 09:24:16

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 ?

Questioner
Alper Aslan
Viewed
37
分享
nbk 2020-02-01 09:07

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.

Alper Aslan 2020-02-07 08:58:09

Thank you for correct answer and sorry for late confirmation ^^ !

Related issues

1
Create a model with a foreign key to a table not created by SQLAlchemy
2
Update sql database by clicking checkbox without submit button using ajax
3
MYSQL filled some rows, but was't suppost to
4
Directus calculated field / column
5
How to count messages for a user with php and mysql
6
How to get numbers of rows and columns using QtSql?
7
How do I get django "Data too long for column '' at row" errors to print the actual value?
8
LPAD without trimming in mysql
9
MySQL query from JSON datatype column that has backslash character
10
How to include a PHP variable inside a MySQL statement