dynamic prepared statement in controller

Question

dynamic prepared statement in controller

Fazal Rasel Mar '16

Created Mar '16	Last Reply Mar '16	Replies 1	Views 466	Votes 0

Fazal Rasel 3.8k

Mar '16

currently I'm experimenting with https://buysql.com/mysql/14-how-to-automate-pivot-tables.html

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 and get fetched resutls..

David Hübner · Answer 1 · 2016-03-25T00:13:33-07:00

With registered MySQL db service in DI, you can simply execute in controller context

$statement = $this->db->prepare('SELECT * FROM robots WHERE name = :name');
result = $this->db->executePrepared($statement, array('name' => 'Voltron'));

more info in API doc

https://docs.phalcon.io/en/latest/api/Phalcon_Db_Adapter_Pdo.html