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

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'");

Answer 2

1. Make sure that you have index on keyword column of all the 3 table so that complete table scan can be avoided. use below to create index.

    CREATE INDEX keyword ON text (keyword);
    CREATE INDEX keyword ON sticker (keyword);
    CREATE INDEX keyword ON image (keyword);

1. Select only 1 row if you want to know if keyword exist in any of the 3 tables.

    <?php
    $check = mysqli_query($con, "SELECT 1 FROM text WHERE keyword='$string' limit 1
                                UNION SELECT 1 FROM sticker WHERE keyword='$string' limit 1
                                UNION SELECT 1 FROM image WHERE keyword='$string' limit 1");
    if(mysqli_num_rows($check) == 0 ) {
        echo "success";
    }
    ?>

If you want to select some rows from each table, use limit. I have used 5 just for an example.

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