dongwu1992 2018-05-14 03:34
浏览 91
已采纳

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

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.

  • 写回答

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.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥66 换电脑后应用程序报错
  • ¥50 array数据同步问题
  • ¥15 pic16F877a单片机的外部触发中断程序仿真失效
  • ¥15 Matlab插值拟合差分微分规划图论
  • ¥15 keil5 target not created
  • ¥15 C/C++数据与算法请教
  • ¥15 怎么找志同道合的伙伴
  • ¥20 如何让程序ab.eXe自已删除干净硬盘里的本文件自己的ab.eXe文件
  • ¥50 爬虫预算充足,跪巨佬
  • ¥15 滑块验证码拖动问题悬赏