2018-05-14 03:34
浏览 91


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 name="city" class="select_city">
    <option value="">City</option>
    <option value="">London</option>
    <option value="">New York</option>
    <option value="">Paris</option>

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

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.

  • 写回答
  • 好问题 提建议
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • duannuci4008 2018-05-14 08:10

    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.

    解决 无用
    打赏 举报

相关推荐 更多相似问题