stack stack - 4 years ago 111
SQL Question

How to create a dynamic query using php?

I have a table like this:

// 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


Also I have this array:

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


Now I want to create this:

$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


How can I do that?




Here is my try:

// 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';
}


But I don't know why my code doesn't work, How can I do that?

Answer Source

First, use SELECT DISTINCT in the query to get unique values, so you don't need to call array_unique.

Then, once you have all the values, you can use implode to connect all the SELECT queries with UNION ALL.

$stm = $db->prepare("SELECT DISTINCT code_table FROM mytable");
$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));
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download