简单的PHP过滤器，在多表列中包含多个术语

Question

I'm trying to implement a search filter that fetch the data from mysql and apply multiple criteria like select and match users from multiple tables's columns. I want to search in these columns by multiple keywords like users who's from specific country or city and Interested in music.

I want to implement this with around 10 tables with it's columns

Here is example of 3 structured Tables structure

Table User Profile (Main table)

ID | PID | Name | Email | Avatar

1 | 22 |  John | x@x.x | URL
---------------------------------
Table User details

ID | PID | Country | City | Address | HPhone | MPhone

1 |  22 |  Norway |  Bergenv addrews | 2222 |   2222 |
---------------------------------
Table User Interests

ID | PID | Interest | Type | Keyword

1 |  22 |  music | music | pop
---------------------------------
Printed Results format

PID, Name, Avatar

First PHP file file1.php

    <form class="user_search_form" action="file2.php" method="post"> 
    <select class="Interest_types" name="Interest" id="Interest">
    <option value="Retail">Music</option>      
    <option value="Transportation">Movies</option>      
    </select>   

    <select name="city" class="select_city">
    <option value="">City</option>
    <option value="">London</option>
    <option value="">New York</option>
    <option value="">Paris</option>
    </select>

    <input type="submit" value="search"  class="search_btn" />  
    </form>

Current second php file files2.php

    //Limit our results within a specified range.
    $results = $mysqli->prepare("SELECT PID, Name, Avatar FROM Profile");
    $results->execute(); //Execute prepared Query
    $results->bind_result($PID, $Name, $Email, $$Avatar); //bind variables to prepared statement

    //Display records fetched from database.
    echo '<ul class="contents">';
    while($results->fetch()){ //fetch values
        echo "<a href=\"/Profile.php?id=$PID\" onClick='Loading()'><li class=\"userlistitem\">";
        echo  "<img src='$Avatar' height='100' width='100' onerror=\"this.src = '/assets/img/noImg.png'\"/>";
        echo  "$Name  $Email $PasswordD";
        echo "</li></a>";
    }
    echo '</ul>';

What should be the best php format in files2.php?

Your help is highly needed and appreciated.

Answer 1

You could use joins in mysql to select all the data you need from those tables. An example would be something like this:

SELECT up.PID, up.Name, up.Avatar FROM User Profile AS up
LEFT JOIN User Details AS ud ON ud.PID = up.PID
LEFT JOIN User Interests AS ui ON ui.PID = up.PID
WHERE ui.KeyWord = 'pop' AND ud.City = 'Bergenv'
ORDER BY up.Name ASC
LIMIT 10

And then just add as many joins as you need to be able to filter your tables. But be careful not to add to many without proper filtering as it will slow down your page if you need to join multiple table with hundreds of rows in them. So plan your queries carefully.