从所有表中选择行的简便方法

Question

i want to select some rows in my mysql databases, I have 3 tables with the same structure, I have tried using the script that I created and this works, but it seems like it takes too many lines in my script.

<?php
$check1 = mysqli_query($con, "SELECT * FROM text WHERE keyword='$string'");
$check2 = mysqli_query($con, "SELECT * FROM sticker WHERE keyword='$string'");
$check3 = mysqli_query($con, "SELECT * FROM image WHERE keyword='$string'");
if(mysqli_num_rows($check1) == 0 && mysqli_num_rows($check2) == 0 && mysqli_num_rows($check3) == 0) {
    echo "success";
}
?>

is there another way to shorten my script above ? thank you :)

Answer 1

If all the tables have the same structure, you can use a UNION query to return rows from all three tables at once:

$check = mysqli_query($con, "SELECT * FROM text WHERE keyword='$string'
                             UNION
                             SELECT * FROM sticker WHERE keyword='$string'
                             UNION
                             SELECT * FROM image WHERE keyword='$string'");

This will give you the equivalent set of rows that your three existing queries give you. Note that there will be no way to determine which table a given row came from, so you may want to add an extra field to indicate that e.g.

$check = mysqli_query($con, "SELECT *, 'text' AS src FROM text WHERE keyword='$string'
                             UNION
                             SELECT *, 'sticker' AS src FROM sticker WHERE keyword='$string'
                             UNION
                             SELECT *, 'image' AS src FROM image WHERE keyword='$string'");