搜索具有不同列的多个表 [英] Search on multiple tables with different columns

问题描述

我想为我的网站构建一个搜索引擎.我的数据库表列表如下:

I want to build a search engine for my website. My database table list is as follows:

1. d_name:我需要搜索两个列.
2. em:我需要在一个列上进行搜索.
3. seri:我需要在一个列上进行搜索.
4. topics:我需要在一个列上进行搜索.
5. 许多其他表，这些表的数量是动态的，并且列表包含在d_name表的列中.我需要在每个表中的两个列上进行搜索.

1. d_name: I need to search on two columns.
2. em: I need to search on one column.
3. seri: I need to search on one column.
4. topics: I need to search on one column.
5. A lot of other tables which the number of them is dynamic and the list is included in a column in d_name table. I need to search on two columns on each of these tables.

由于结果数量可能很多，因此我也需要进行分页.
据我所知，我应该使用UNION以便在对多个表的搜索中使用分页，但是首先，列数应该相同，但这里不一样，而且表数是动态的.

As the number of results may be large, I need to make a pagination as well.
As I know, I should use UNION in order to use the pagination on search on multiple tables but first, the number of columns should be the same but here isn't and also the number of my tables is dynamic.

另一方面，如果我尝试使用SHOW TABLES，它还会搜索其他一些包含我的私人信息的表.

On the other hand, if I try to use SHOW TABLES it will also search some other tables which my private information is included in them.

这是我到目前为止所做的:

This is what I've so far done:

        $query = "(SELECT d_name, doc, db_name AS d FROM d_name WHERE d_name LIKE '%" . 
           $keyword . "%' OR title LIKE '%" . $keyword ."%') 
           UNION
           (SELECT em_name, db_name AS em FROM em WHERE em_name LIKE '%" . 
           $keyword . "%') 
           UNION
           (SELECT name AS topic FROM topics WHERE name LIKE '%" . 
           $keyword . "%')
           UNION
           (SELECT seri_name, db_name AS seri FROM seri WHERE seri_name LIKE '%" . 
           $keyword . "%')";

但是UNION不起作用！有人知道我该怎么办吗?

But the UNION doesn't work! Does anybody know what should I do?

推荐答案

如果执行UNION查询，则所有单独的查询的结果中的列数均应相同.您可以使用NULL，''或'not provided'之类的固定值来填充其他表.

If you perform an UNION-query all seperate queries should have the same number of columns in the result. You can use fixed values like NULL, '' or 'not provided' to fill up the other tables.

Examlpe查询(演示)

Examlpe-query (demo)

SELECT column_1, column_2 FROM i_have_two_columns
UNION
SELECT column_1, NULL FROM i_have_only_one_column

这篇关于搜索具有不同列的多个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！