How to implement mySQL dynamic query in PHP? -

i can produce output:

+----------+------------+------------+------------+ | startt   | 2013-04-01 | 2013-04-02 | 2013-04-03 | +----------+------------+------------+------------+ | 08:00:00 | donald     | daisy      | mickey     | | 12:00:00 | pluto      | goofy      | minnie     | | 14:00:00 | null       | mickey     | null       | +----------+------------+------------+------------+ 

from original data:

mysql> select * test; +------------+----------+----------+--------+ | startd     | startt   | duration | name   | +------------+----------+----------+--------+ | 2013-04-01 | 08:00:00 |        4 | donald | | 2013-04-02 | 08:00:00 |        4 | daisy  | | 2013-04-03 | 08:00:00 |        4 | mickey | | 2013-04-03 | 12:00:00 |        4 | minnie | | 2013-04-01 | 12:00:00 |        4 | pluto  | | 2013-04-02 | 12:00:00 |        4 | goofy  | | 2013-04-02 | 14:00:00 |        4 | mickey | +------------+----------+----------+--------+ mysql> 

using mysql dynamic query:

1 set @sql = null; 2 select 3     group_concat(distinct 4         concat( 5             'group_concat(case when startd = ''', 6             `startd`, 7             ''' `name` end ) `', 8             `startd`,'`' 9         ) 10     ) @sql 11 test; 12 13 set @sql = concat('select startt, ',@sql,' 14                     test 15                     group startt'); 16 17 prepare stmt @sql; 18 execute stmt; 19 deallocate prepare stmt; 

thanks point @hims056.

how can pass results of dynamic query variable can loop on in php?

in past have used:

$result=mysqli_query($con,"select ..."); ... lines deleted ... while ($row=mysqli_fetch_array($result)) ... lines deleted ... 

this method not seem appropriate in these circumstances.

any assistance appreciated.

a possible solution wrap in stored procedure

delimiter $$ create procedure sp_test()     begin     set @sql = null;     select         group_concat(distinct             concat(                 'group_concat(case when startd = ''',                 `startd`,                 ''' `name` end ) `',                 `startd`,'`'              )          ) @sql      test;       set @sql = concat('select startt, ', @sql, '                           test                          group startt');      prepare stmt @sql;     execute stmt;     deallocate prepare stmt; end$$ delimiter ; 

and use it

call sp_test(); 

here sqlfiddle demo

update: on php side can do

$db = new mysqli('localhost', 'user', 'password', 'dbname'); $sql = "call sp_test()"; $query = $db->query($sql); $result = array(); while ($row = $query->fetch_assoc()) {     $result[] = $row; } $query->close(); $db->close(); // whatever need present var_dump($result); 

all error handling has been intentionally omitted brevity