Statement work on phpmyadmin but not through php [duplicate]

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
– Elias Van Ootegem, Commented Nov 5, 2012 at 7:35

Community · Accepted Answer · 2017-05-23 11:50:35Z

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, 2017 at 11:50

CommunityBot

11 silver badge

answered Nov 5, 2012 at 7:31

xsist10

3,0611 gold badge27 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

John Woo · Accepted Answer · 2012-11-05 07:40:40Z

0

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.

answered Nov 5, 2012 at 7:40

John Woo

265k70 gold badges509 silver badges500 bronze badges

1 Comment

Its Wawan Over a year ago

how to do that?? i know nothing about stored procedure.. where to create that?

Collectives™ on Stack Overflow

Statement work on phpmyadmin but not through php [duplicate]

2 Answers 2

Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Linked

Related