How to implement mySQL dynamic query in PHP?

Question

I can produce this 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 this original data:

mysql> select * from 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 this MySQL dynamic query:

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

Thanks for your help to this point @hims056.

How can I pass the results of this dynamic query to a variable that I can loop over in PHP?

In the past I have used:

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

This method does not seem appropriate in these circumstances.

Any assistance would be appreciated.

Thanks for your suggestion Yogesh, can you elaborate, I am not familiar with mysqli'd multi_query. Or can you point me to a link where I can find out how to do this? — Tony
– Tony, Commented Jul 22, 2013 at 4:25

peterm · Accepted Answer · 2013-07-22 04:58:06Z

1

A possible solution is to wrap it in a stored procedure

DELIMITER $$
CREATE PROCEDURE sp_test()
    BEGIN
    SET @sql = NULL;
    SELECT
        GROUP_CONCAT(DISTINCT
            CONCAT(
                'GROUP_CONCAT(CASE WHEN startd = ''',
                `startd`,
                ''' THEN `name` END ) AS `',
                `startd`,'`'
             )
         ) INTO @sql
     FROM test;

     SET @sql = CONCAT('SELECT startt, ', @sql, '
                          FROM test
                         GROUP BY startt');

    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;

And use it

CALL sp_test();

Here is SQLFiddle demo

UPDATE: on php side you 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();
// then do whatever you need to do to present it
var_dump($result);

All error handling has been intentionally omitted for brevity

edited Jul 22, 2013 at 4:58

answered Jul 22, 2013 at 4:33

peterm

93k16 gold badges157 silver badges162 bronze badges

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

4 Comments

Tony Over a year ago

Thanks for your suggestion Peter @peterm. I understand what you have suggested, what I dont understand is how I can loop over the result to produce a html table. Any suggestions on presenting the results in a html table? My objective is to present the results in a browser.

Tony Over a year ago

It has helped because I now understand how to proceed. I will implement the suggestion today. This was the piece of the puzzle that I was missing, I expect your suggestion will do exactly what I want it to do. Thank you for your assistance.

Tony Over a year ago

G'day again @peterm. I am please to say that I have successfully implemented your suggestion and it works. I have learned things from the exercise. Thank you for taking the time to assist me.

peterm Over a year ago

@user2593733 Great, congrats! I'm glad I could help :)

Collectives™ on Stack Overflow

How to implement mySQL dynamic query in PHP?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related