user2285831 user2285831 - 7 months ago 19
SQL Question

How to use SQL prepared statement in php pdo

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);


?>

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 http://php.net/manual/en/pdo.prepared-statements.php

Comments