PDO从多个相同的表中选择 [英] PDO select from multiple identical tables

问题描述

我想从多个相同的表(三个甚至更多)中获取数据，它们具有相同的列名(但行中的数据不同).

I want to fetch datas from multiple identical tables (three and even more), they got the same columns names (but different datas in the rows).

因此，我想用循环来获取所有表，就好像它是一个查询(一个循环)，而不是三个查询一样.

So, I want to fetch with loops all the tables as if it was one query (one loop), not three.

获取数组时，我不想将列数增加三倍.我希望所有表都使用相同的列名.表中的数据将在阵列中一个接一个地堆叠，而不是一个接一个地堆叠，使阵列中的列数增加三倍.但是，一旦它们彼此堆叠，"ORDER BY"过滤器将混合"表:"ORDER BY"将立即作用于所有表(就像它只是一个表一样)，而不是全部作用各个表.

I do not want to triple the number of columns when fetching the array. I want all the tables to use the same columns names. The datas of the tables will be stacked one over the other in the array, not one next to the other, tripling the number of columns in the array. But, once they get stacked one over the other, the "ORDER BY" filter will "blend" the tables : the "ORDER BY" will act on all the tables at once (like if it was only one table), not on all the tables individually.

我对1个表的查询如下:

My query for 1 table looks like this :

$pdo = "SELECT * FROM " .$table1 ." ORDER BY price LIMIT " 
       .$current_page_offset .", " .$items_per_pages;

所以...我想获取三个表，就像我只获取一个表一样.

So...I want to fetch the three tables like if I was fetching only one.

*还希望保持我的限制，并且"ORDER BY"甚至可以使用更多过滤器.我不希望三倍限制显示页面上我想要的项目数量的三倍...

*also want to keep my limit and "ORDER BY" even more filters working. I do not want a triple-limit showing three times the number of items i want on my page...

有没有办法做到这一点?

Is there a way to do that ?

* 我在第三段更改了对"blended"的描述.对不起(也许)我的英语不好.

*EDIT : I changed my description of "blended" at 3rd paragraph. sorry for (maybe) my bad English.

推荐答案

您要使用 UNION :

UNION用于将来自多个SELECT语句的结果合并为一个结果集.

UNION is used to combine the result from multiple SELECT statements into a single result set.

因此，如果您的表名在名为$tables的数组中:

Therefore, if your table names are in an array called $tables:

mb_regex_encoding($charset); // character set of database connection
foreach ($tables as $table) {
  $table = '`'.mb_ereg_replace('`','``',$table).'`';
  $sql[] = "(
    SELECT   *
    FROM     $table
    ORDER BY price
    LIMIT    $current_page_offset, $items_per_pages
  )";
}
$sql = implode(' UNION ALL ', $sql);

但是，听起来好像您的模式应该被规范化，这样您就没有这样的相同"表:而是拥有一个带有附加列的表，以表明每个表之间的区别.

However it sounds as though your schema should be normalised, such that you don't have such "identical" tables: instead have a single table with an additional column to indicate whatever distinguishes each.

这篇关于PDO从多个相同的表中选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！