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

Question

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.

Answer 1

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.

Answer 2

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

Answer 3

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.

Answer 4

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 !

Answer 5

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."%'"

Answer 6

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