php - 如何使用 php 创建动态查询？

Question

我有一个这样的表:

// mytable
+----+---------+------------+
| id | id_post | code_table |
+----+---------+------------+
| 1  | 34523   | 1          |
| 2  | 3453    | 4          |
| 3  | 43434   | 2          |
| 4  | 54321   | 1          |
| 5  | NULL    | NULL       |
| 6  | 32411   | 2          |
| 7  | 42313   | 1          |
| 8  | 34242   | 2          |
+----+---------+------------+
//                    ^ all of my focus is on this column

我还有这个数组:

$convert_code_name = array (
                        "1" => "Post1", 
                        "2" => "Post2", 
                        "3" => "Post3",
                        "4" => "Post4"  
                           );

现在我想创建这个:

$query = "select * from post1
             union all
          select * from post2
             union all
          select * from post4";

          // there isn't "post3", because 3 isn't exist in the code_table column

我该怎么做？

---

这是我的尝试:

// connect to database
$stm = $db->prepare('select * from mytable');
$stm->execute();
$result = $stm->fetch();

/* array_unique: removes duplicate values
   array_filter: removes "NULL" values */

array_filter(array_unique($result[code_table]), function($item) {
    return $item != 'NULL';
});

foreach($item as $numb){
    $query .= 'select * from'.$convert_code_name[$numb].'union all';
}

但我不知道为什么我的代码不起作用，我该怎么做？

Answer 1

首先，在查询中使用SELECT DISTINCT获取唯一值，这样就不需要调用array_unique。

然后，一旦获得所有值，就可以使用 implode 将所有 SELECT 查询与 UNION ALL 连接起来。

$stm = $db->prepare("SELECT DISTINCT code_table FROM mytable WHERE code_table IS NOT NULL");
$stm->execute();
$results = $stm->fetchAll();
// This returns a 2-dimensional array, we just want one column
$results = array_column($results, 'code_table');

$query = implode(' UNION ALL ', array_map(function($code_table) use ($convert_code_name) {
    return "SELECT * FROM " . $convert_code_name[$code_table];
}, $results));