running dynamic PREPARE STATEMENT

Question

currently I'm experimenting with http://buysql.com/mysql/14-how-to-automate-pivot-tables.html I need basic idea to run the dynamic sql below.

table:

+----+---------+---------------+--------+
| id | item_id | property_name | value  |
+----+---------+---------------+--------+
|  1 |       1 | color         | blue   |
|  2 |       1 | size          | large  |
|  3 |       1 | weight        | 65     |
|  4 |       2 | color         | orange |
|  5 |       2 | weight        | 57     |
|  6 |       2 | size          | large  |
|  7 |       3 | size          | small  |
|  8 |       3 | color         | red    |
|  9 |       3 | weight        | 12     |
| 10 |       4 | color         | violet |
| 11 |       4 | size          | medium |
| 12 |       4 | weight        | 34     |
| 13 |       5 | color         | green  |
| 14 |       5 | weight        | 10     |
+----+---------+---------------+--------+

the query is

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(IF(property_name = ''',
      property_name,
      ''', value, NULL)) AS ',
      property_name
    )
  ) INTO @sql
FROM properties;
SET @sql = CONCAT('SELECT item_id, ', @sql, ' FROM properties GROUP BY item_id');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Question: how to run this statement from controller(any framwork or pdo) and get fetched resutls..

Edit Actually, I'm working on a products database. Expecting feature_id from frontEnd. Number of argument is unknown. So, I'm thinking to create dynamic query based on arguments.

Why don't you create a procedure @ MySQL server and use PDO to call that procedure? `$pdo->query("CALL procedure_name()");`. That's much easier than executing all of the above from PDO. — Mjh, Mar 24 '16 at 15:34
I'm not so comfortable with procedure. And besides number of argument is unknown. I also need to supply where condition which need to create dynamically. Any other suggestion? — Fazal Rasel, Mar 24 '16 at 15:45

running dynamic PREPARE STATEMENT

0 Answers0