如何在PHP PDO中使用SQL准备语句 [英] How to use SQL prepared statement in php pdo
问题描述
我为sql创建了准备好的语句,使我在 mysqlWorkbench 中得到正确的结果,但是当我尝试对 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.
那么如何在 php pdo 中使用我准备好的语句?
So how to use my prepared statement with php pdo ?
这是我的代码:
<?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);
?>
推荐答案
我找到了可以给我正确结果的解决方案.
I found a solution that give me the right result.
我在数据库中创建一个存储过程,然后在php pdo中调用它.
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代码
<?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);
?>
这是有关php preapared语句和存储过程的链接 http://php.net/manual/zh/pdo.prepared-statements.php
This is a link about php preapared statement and stored procedures http://php.net/manual/en/pdo.prepared-statements.php
这篇关于如何在PHP PDO中使用SQL准备语句的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!