PHP - Live Search using multiple MySQL tables

Question

I've scoured the internet, yet I couldn't find something like my problem. I'm trying to build a live search function that can pull data from multiple tables. It works with one table, but I want to be able to pull from bands, users, (albums, etc.)

Here's my PHP code:

require ("includes/config/config.php"); 

$search_term = sanitize(htmlentities($_POST['search_term']));

if (!empty($search_term)){

    $search = "(SELECT `band_id`, `band_name` FROM `bands` WHERE `band_name` LIKE '%$search_term%'  LIMIT 0, 5) 
                UNION ALL
                (SELECT `user_id`, `username` FROM `users` WHERE `username` LIKE '%$search_term%'  LIMIT 0, 5)";
    $query = mysqli_query($conn, $search);
    $result = mysqli_num_rows($query);

    while ($row = mysqli_fetch_assoc($query)){
        #$user_id = $row['user_id'];
        #$username = $row['username'];
        $band_id = $row['band_id'];
        $band_name = $row['band_name'];
        $check = mysqli_num_rows($query);



        if ($check != 0){
            echo "<a style='text-decoration: none; color: black;' href='index.php?band=$band_id'><li class='searchResults'>" . ucfirst($band_name) . "</li></a>";
        } else {
            echo "<li class='searchResults'>No Results Found</li>";
        }
    }
}

jQuery:

$("#searchbar").keyup(function(){
    var searchTerm = $(this).val();

    $.post('search.php', { search_term: searchTerm}, function(data){

        $(".searchResults").html(data);
        $("#searchUl").css("display", "block");
    });
});

Again, everything else runs fine, but I don't know how to query multiple tables in a way so I can echo out the href differently (i.e. index.php?band=$band_id for band and index.php?user=$user_id for user.

Chris · Accepted Answer · 2015-01-26 19:36:14Z

1

The problem is that you are losing information whether a record is a band or a user by doing a UNION ALL. This puts records of different types into the same set and merges the columns band_id, user_id and band_name, username.

You could fix this in different ways:

Create two separate queries. Then get all the results from the bands query and put them in an array and put all the results from the user query into a different array. Afterwards, echo the results of both arrays.
Make MySQL tell you the type of each row. You can achieve this by changing your query like this:
```
(
    SELECT `band_id` , `band_name` , "band" AS `type`
    FROM `bands`
    WHERE `band_name` LIKE '%$search_term%'
    LIMIT 0 , 5
)
UNION ALL (    
    SELECT `user_id` , `username` , "user" AS `type`
    FROM `users`
    WHERE `username` LIKE '%$search_term%'
    LIMIT 0 , 5
)
```
Your query result will now have an additional column called type which will contain either "band" or "user". Depending on the value of that column you can echo a different link.

For example, you could extend your code like this:
```
if($row["type"] === "band") {
    echo "somelink?band_id=" . $row["band_id"];
} else {
    echo "somelink?user_id=" . $row["band_id"];
}
```
Note that in both cases you will need to read $row["band_id"] because the join will take the column name from the first part of the query. All user IDs will be in the band_id column.

edited Jan 26, 2015 at 19:36

answered Jan 25, 2015 at 13:19

Chris

7,2147 gold badges57 silver badges83 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Mitch Mullvain Over a year ago

How would I use the second option in an "if statement" or "echo" it out?

Mitch Mullvain Over a year ago

I ended up going with the first option, but I ended up having to put it through two different while loops causing it to display all band results before users. Not a good fix obviously, but it is nice to have it work. It will be perfected one day. Thanks for the help.

Chris Over a year ago

@MitchMullvain If using the second solution, you can access the type with $row['type'].

Chris Over a year ago

@MitchMullvain I updated my answer to include some example code how you could use the second solution; I hope this makes it clearer.

Mitch Mullvain Over a year ago

Thanks a bunch for your help. It works beautifully now and your suggestion really reduced the code. I really appreciate your help.

Collectives™ on Stack Overflow

PHP - Live Search using multiple MySQL tables

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related