在多个sql表中选择一个名称

Question

I want to search a product detail from multiple sql table. I used these queries which do not work. I found many post about this topic but I cannot apply any of them.

Every table has the same structure like (I have 14 table in this category)

id | name | cast | detail | date

I tried:

Method 1:

$result = mysqli_query($db,"SELECT movie.*, audio.* 
  FROM movie,audio WHERE movie.name='$name' OR audio.name='$name'");

Method 2:

$result = mysqli_query($db,"SELECT * 
  FROM movie,audio WHERE movie.name='$name' OR audio.name='$name'");

Method 3:

$result = mysqli_query($db,"SELECT * FROM movie,audio WHERE name='$name'");

Answer 1

Note, credit really goes to Giorgos's comment at the top, I just formalized it.

Assuming all tables line up perfectly, and you're just trying to look up something with name in all tables, your third attempt is really close.

The SQL should be

SELECT *
FROM movie
    UNION audio
WHERE name='$name'

Just chain UNIONs till you've combined all tables.

Only catch is that the columns will be labeled in the way Movie has them, and if there's any structural differences between the tables, you'll get a lot of weirdness and not the results you want.

In that situation, the trick is to chain UNIONs on SELECTs instead of inside the FROM like so:

SELECT *
FROM movie
WHERE name='$name'
UNION
SELECT *
FROM audio
WHERE name='$name'

If the SQL flavor dislikes that setup, just wrap it in a SELECT * FROM ( ... ).

Side note, directly inserting a variable into your SQL is potentially an SQL Injection risk. If $name is not 100% server-controlled, you may want to investigate switching to parametrized queries instead, potentially with stored procedures.

Or just sanitize the variable. That also works. Injection was blockable that way before parametrization came along.