Statement work on phpmyadmin but not through php

Question

Possible Duplicate:
PDO support for multiple queries (PDO_MYSQL, PDO_MYSQLND)

i have a statement here.. when i ran it from phpmyadmin.. i got my query works.. but it doesnt when i execute it from php..

here's my query (statement)

SET @sql = NULL;
 SELECT GROUP_CONCAT(DISTINCT CONCAT('MAX(IF(a.Name = ''', a.Name, ''', ca.value, NULL)) AS ', a.Name )) INTO @sql FROM cust_attribute ca INNER JOIN attribute a ON ca.idAttribute = a.idAttribute INNER JOIN customer c ON c.idCustomer = ca.idCustomer WHERE a.publish = 'Y';
 SET @sql = CONCAT('SELECT c.idCustomer, c.namaCustomer, ' ' c.email, c.noTelp, ', @sql, ' FROM cust_attribute ca', ' INNER JOIN attribute a ON ca.idAttribute = a.idAttribute', ' INNER JOIN customer c ON c.idCustomer = ca.idCustomer', ' GROUP BY c.idCustomer');
 SELECT @sql;
 prepare stmt FROM @sql;
 execute stmt;

and here's my code on php...

<?php 
$uname = 'root'; //nama username database 
$pwd = ''; //Password database 
$dbname = 'eav_latihan'; //nama Database 

/* Initialisasi database */ 
$db = new PDO('mysql:host=localhost;dbname='.$dbname, $uname, $pwd); 
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING); 


$query = "SET @sql = NULL; ". 
" SELECT GROUP_CONCAT(DISTINCT CONCAT('MAX(IF(a.Name = ''', ". 
" a.Name, ''', ca.value, NULL)) AS ', a.Name )) INTO @sql ". 
" FROM cust_attribute ca ". 
" INNER JOIN attribute a ON ca.idAttribute = a.idAttribute ". 
" INNER JOIN customer c ON c.idCustomer = ca.idCustomer ". 
" WHERE a.publish = 'Y'; ". 
" SET @sql = CONCAT('SELECT c.idCustomer, c.namaCustomer, ' ". 
" ' c.email, c.noTelp, ', @sql, ' FROM cust_attribute ca', ". 
" ' INNER JOIN attribute a ON ca.idAttribute = a.idAttribute', ". 
" ' INNER JOIN customer c ON c.idCustomer = ca.idCustomer', ". 
" ' GROUP BY c.idCustomer'); ". 
" SELECT @sql; ". 
" prepare stmt FROM @sql; ". 
" execute stmt;"; 

$result_array = array();
$queryku = $db->query($query);
$result_array = $queryku->fetchAll(PDO::FETCH_OBJ);
var_dump($result_array);
?>

Anyone can help me or give me a suggest here??

use a try-catch block, catch all exceptions that are raised and look at the messages they produce. Also: why are you using session variables and db-side prepared statements? PS: look into how to turn of emulated prepared statements in the PDO extension, because that might be what is causing your problem, that and the fact that half of the time you're using too many single quotes: `a.Name = '''` — Elias Van Ootegem, Nov 05 '12 at 07:35

score 2 · Accepted Answer · edited May 23 '17 at 11:50

2

This issue has come up before with regards to PDO. Not sure if it's a bug or a security feature (might be related to this: Mysql can't perform more than 1 query at a time).

Have a look at this solution: http://www.php.net/manual/en/book.pdo.php#96264

edited May 23 '17 at 11:50

Community

1
1

answered Nov 05 '12 at 07:31

Tom

3,031
1
25
33

score 0 · Answer 2 · answered Nov 05 '12 at 07:40

I think PDO can't execute multiple queries. Try using a Stored Procedure on this.

DELIMITER $$
CREATE PROCEDURE procName
BEGIN
SET @sql = NULL;
 SELECT GROUP_CONCAT(DISTINCT CONCAT('MAX(IF(a.Name = ''', a.Name, ''', ca.value, NULL)) AS ', a.Name )) INTO @sql FROM cust_attribute ca INNER JOIN attribute a ON ca.idAttribute = a.idAttribute INNER JOIN customer c ON c.idCustomer = ca.idCustomer WHERE a.publish = 'Y';
 SET @sql = CONCAT('SELECT c.idCustomer, c.namaCustomer, ' ' c.email, c.noTelp, ', @sql, ' FROM cust_attribute ca', ' INNER JOIN attribute a ON ca.idAttribute = a.idAttribute', ' INNER JOIN customer c ON c.idCustomer = ca.idCustomer', ' GROUP BY c.idCustomer');
 SELECT @sql;
 prepare stmt FROM @sql;
 execute stmt;
END $$
DELIMITER ;

and call procName on your PHP file.

how to do that?? i know nothing about stored procedure.. where to create that? — Wawan Brutalx, Nov 05 '12 at 07:46

Statement work on phpmyadmin but not through php

2 Answers2