I create prepared statement for sql that give me the right result in mysqlWorkbench, but when I tried to use the same query with php pdo, it returns me an empty Array.
So how to use my prepared statement with php pdo ?
This is my code:
<?php $dbh = new PDO('mysql:host=localhost;dbname=db', 'user', 'pass'); $pstmt = "set @sql = null; select group_concat(distinct concat( 'MAX(IF(ch.ch_name = ''', replace(ch.ch_name, '''', '''''), ''', v.v_value, NULL)) AS ''', replace(ch.ch_name, '''', ' ') , '''' ) ) into @sql FROM e_champ ch join e_champ_value v on v.v_fk_champ_id = ch.ch_id join e_collecte c on c.c_id = v.v_fk_collecte_id AND c.c_id = 2; set @sql = concat('select oi.oi_id, ', @sql, ' from e_order_item oi left join e_champ_value v on v.v_fk_order_item_id = oi.oi_id join e_champ ch on ch.ch_id = v.v_fk_champ_id join e_collecte c on c.c_id = v.v_fk_collecte_id AND c.c_id = 2 GROUP BY oi_id'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;"; $sth = $dbh->prepare($pstmt); $sth->execute(); /* Fetch all of the remaining rows in the result set */ print("Fetch all of the remaining rows in the result set:n"); $result = $sth->fetchAll(); print_r($result); ?>
Advertisement
Answer
I found a solution that give me the right result.
I create a stored procedure in the database then I call it within php pdo.
DELIMITER $$ CREATE PROCEDURE GetResult() BEGIN set @sql = null; select group_concat(distinct concat( 'MAX(IF(ch.ch_name = ''', replace(ch.ch_name, '''', '''''), ''', v.v_value, NULL)) AS ''', replace(ch.ch_name, '''', ' ') , '''' ) ) into @sql FROM e_champ ch join e_champ_value v on v.v_fk_champ_id = ch.ch_id join e_collecte c on c.c_id = v.v_fk_collecte_id AND c.c_id = 2; set @sql = concat('select oi.oi_id, ', @sql, ' from e_order_item oi left join e_champ_value v on v.v_fk_order_item_id = oi.oi_id join e_champ ch on ch.ch_id = v.v_fk_champ_id join e_collecte c on c.c_id = v.v_fk_collecte_id AND c.c_id = 2 GROUP BY oi_id'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; END$$
// PHP code
<?php $dbh = new PDO('mysql:host=localhost;dbname=db', 'user', 'pass'); $pstmt = "call GetResult()"; $sth = $dbh->prepare($pstmt); $sth->execute(); /* Fetch all of the remaining rows in the result set */ print("Fetch all of the remaining rows in the result set:n"); $result = $sth->fetchAll(); print_r($result); ?>
This is a link about php preapared statement and stored procedures https://www.php.net/manual/en/pdo.prepared-statements.php