How to create a dynamic query using php?

Question

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?

array_filter returns a new array, you need to assign it to a variable. — Barmar
– Barmar, Commented Dec 30, 2015 at 22:50
$result just contains the first row of the table. Did you mean to use fetchAll? — Barmar
– Barmar, Commented Dec 30, 2015 at 22:51
@Barmar honestly I don't know, maybe I need to fetchAll .. I just understand (so far) it is better to I use DISTINCT (mysql layer) instead of array_unique() (php layer) — stack
– stack, Commented Dec 30, 2015 at 22:51

Barmar · Accepted Answer · 2015-12-31 17:08:43Z

3

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 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));

edited Dec 31, 2015 at 17:08

answered Dec 30, 2015 at 22:57

Barmar

789k57 gold badges555 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

16 Comments

stack Over a year ago

Oh Dear Lord ...! I don't know what should I say .. Perfect. +1

stack Over a year ago

Well I'm embarrassed to ping you again, but ..., Look your code seems completely correct. But I don't know why it doesn't execute ..! My php version is old (5.2.6). I think array_map(function($code_table) part has a problem with my php version. Is there any alternative?

Barmar Over a year ago

You have to use create_function in that old version. Exercise for the reader, I don't have time to show it.

Barmar Over a year ago

It also doesn't have array_column, that's from PHP 5.5. Read the documentation for a shim.

Barmar Over a year ago

Sorry about that -- added WHERE code_table IS NOT NULL to the query now.

|

Iłya Bursov · Accepted Answer · 2015-12-30 22:37:58Z

1

$query .= 'select * from'.$convert_code_name[$numb].'union all';

will generate wrong sql, change it to (I assume that $convert_code_name[$numb] contains full table name like Post1, Post2):

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

answered Dec 30, 2015 at 22:37

Iłya Bursov

24.4k4 gold badges36 silver badges68 bronze badges

2 Comments

stack Over a year ago

Can you please tell me how can I don't select NULL values in the query?

Iłya Bursov Over a year ago

@stack where field is not null

cFreed · Accepted Answer · 2015-12-30 22:40:24Z

0

You simply need to first execute this query:

SELECT DISTINCT code_table FROM events

Then you can build your second query like from its result, which only contains 1 row for each existing code_table.

answered Dec 30, 2015 at 22:40

cFreed

4,4641 gold badge26 silver badges35 bronze badges

Collectives™ on Stack Overflow

How to create a dynamic query using php?

3 Answers 3

16 Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

16 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related