How to create a search with multiple tables with php and mysqli

[codexB]

I created a database with the name ‘food-order’, on the search bar i can only search for files on one of the table, how can i search for all the items within the database ‘food-order’ not a specific table

<?php
                //sql query to get category based on search keyword

                $sql = "SELECT * FROM tl_category AND apartment_rent  WHERE title LIKE '%$search%' OR description LIKE '%$search%' OR location LIKE '%$search%'";

                // execute the query

                $res = mysqli_query($conn, $sql);

                // count rows

                $count = mysqli_num_rows($res);

                // check whether category is available or not

                if($count>0){

                    // category is available

                    while($row=mysqli_fetch_assoc($res)){

                        // get the details

                        $id = $row['id'];

                        $title = $row['title'];

                        $price = $row['price'];

                        $description = $row['description'];

                        $location = $row['location'];

                        $image_name = $row['image_name'];

                        ?>

[phdr]

If you are asking if there’s a built-in way of searching in all the tables in a database(s), no there’s not. You need to have or query for a list of the tables in the database(s), you then need to define a list of columns in each table you want to search (it wouldn’t be efficient to search in all columns and searching in some of the internal usage columns doesn’t make sense), and you would also need to define a list of columns to select from each table so that displaying the combined result makes sense. Once you have all this information defined, you would dynamically build a UNION query.

If the data in the tables is fairly unique, so that doing the above won’t produce a meaningful result, you might as well just build and execute a separate query for each table.

Edit: also, don’t put external, unknown, dynamic values directly into an sql query statement. Use a prepared query.

[ErnieAlex]

Well, you can run a query that checks if data is NOT IN another table. Loosely like this…

SELECT * FROM table1 WHERE food_order NOT IN
( SELECT * FROM table2 )

You can nest queries. The first query would be from table 2 and then, you check if the value from the first table is in the results. But, you talk about food-order and then apartments. So we are a little confused on what you really need here.