dongpu3347 2014-03-11 12:31
浏览 20
已采纳

创建一个完整的mysql搜索查询,搜索多个表

I am trying to search my full website but I'm unsure how to make the query.

    $query = "SELECT * FROM 
game_content,
tech_content,
hint_content
 WHERE 
title LIKE '%".$input."%' 
description LIKE '%".$input."%'";

The tables i want to search are: game_content,tech_content,hint_content & the columns are: title,description each table has these columns

To be honest I'm confused using JOIN i have been getting lots of unexpected results.

  • 写回答

6条回答 默认 最新

  • dousao1175 2014-03-12 13:32
    关注

    After trying out some of your answers i decided to do what Dieter suggested in the comments.

        //set the queries.
    $query[1] = "SELECT * FROM game_content WHERE title LIKE '%".$input."%' OR description LIKE '%".$input."%'";
    $query[2] = "SELECT * FROM hint_content WHERE title LIKE '%".$input."%' OR description LIKE '%".$input."%'";
    $query[3] = "SELECT * FROM tech_content WHERE title LIKE '%".$input."%' OR description LIKE '%".$input."%'";
    
        //loop the queries setting the results
    for ( $i = 1; $i <= 3; $i++ ) {
    
        $result[$i] = mysqli_query( $connection, $query[$i] );
        check_query( $result[$i] );
    
        while( $row = mysqli_fetch_assoc( $result[$i] ) ) {
    
            //display output
        }
    }
    

    It is only a small site so this will suffice.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
  • doumiang0597 2014-03-11 12:36
    关注

    I would recommend to have each table seperatly searched, or you could have tech_content.name as name , hint_content.name as name and so on in the select part, see I have selected rows with name alike

    and union for select over every table

    评论
  • douxin9135 2014-03-11 12:37
    关注

    You could do something like this:

    SELECT 'game_content' as source, * FROM game_content WHERE <your condition>
    UNION
    SELECT 'tech_content' as source, * FROM tech_content WHERE <your condition>
    UNION
    <further tables>;
    

    to join the results from all tables into a single result set.

    评论
  • duanhu7615 2014-03-11 12:40
    关注

    You need to use joins

    SELECT game_content.*, tech_content.*, hint_content.*
    FROM game_content
        JOIN tech_content
            ON tech_content.aID = game_content.bID
        JOIN hint_content
            ON hint_content.cID = tech_content.bID
    WHERE game_content.title LIKE '%".$input."%' 
    and game_content.description LIKE '%".$input."%'"';
    

    Make sure you have your relations(constraints) already created ! Note: - this way your query will take like forever, this is the fastest way to do it by sql! but performance wise is very poor !

    Because you are going to join all data and then start sorting it out ! If your tables are big then you have a problem - the query will take forever !

    评论
  • drnpwmq4536 2014-03-11 12:42
    关注

    It would be shorter without aliases if criteria field names and returned field names are the same in the tables. It should be something like following (tableName field tells you which table the result is coming from)

    "SELECT gameField1 AS fld1, gameField2 AS fld2, 'game' AS tableName FROM game_content WHERE gameTitle LIKE '%".$input."%' AND gameDescription LIKE '%".$input."%'
    UNION
    SELECT techField1 AS fld1, techField2 AS fld2, 'tech' AS tableName FROM tech_content WHERE techTitle LIKE '%".$input."%' AND gameDescription LIKE '%".$input."%'
    UNION
    SELECT hintField1 AS fld1, hintField2 AS fld2, 'hint' AS tableName FROM hint_content WHERE hintTitle LIKE '%".$input."%' AND hintDescription LIKE '%".$input."%'"
    
    评论
  • dss67853 2014-03-11 12:50
    关注

    If each table has an id primary key column (or similar), you can create a view;

    CREATE VIEW all_content AS 
    SELECT CONCAT('g',id) AS id, title, description FROM game_content 
    UNION 
    SELECT CONCAT('t', id) AS id, title, description FROM tech_content 
    UNION
    SELECT CONCAT('h', id) AS id, title, description FROM hint_content
    

    Then query the view

    评论
查看更多回答(5条)

报告相同问题?

悬赏问题

  • ¥15 关于#java#的问题,请各位专家解答!(相关搜索:java程序)
  • ¥15 linux tsi721的驱动编译后 insmod 提示 报错
  • ¥20 multisim测数据
  • ¥15 求无向连通网的所有不同构的最小生成树
  • ¥15 模拟器的framebuffer问题
  • ¥15 opencv检测轮廓问题
  • ¥15 单点式登录SSO怎么爬虫获取动态SSO_AUTH_ACCESS_Token
  • ¥30 哈夫曼编码译码器打印树形项目
  • ¥20 求完整顺利登陆QQ邮箱的python代码
  • ¥15 怎么下载MySQL,怎么卸干净原来的MySQL