I got error prepare SQL in PHP with PDO. I was Tried query in PHPMyadmin than it's work, but when i tried in php it doesn't work with error
SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use
In my case i want to pivot my data
this my query:
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT('MAX(IF(`meta_key` = "', `meta_key`,'", `meta_value`,"")) AS "',`meta_key`,'"')
) INTO @sql
FROM wp_frmt_form_entry_meta
RIGHT JOIN wp_frmt_form_id
ON wp_frmt_form_entry_meta.meta_key = wp_frmt_form_id.meta_keyy
ORDER BY wp_frmt_form_entry_meta.meta_id;
SET @sql = CONCAT('SELECT `entry_id`, ', @sql, '
FROM wp_frmt_form_entry_meta s
GROUP BY s.`entry_id`
ORDER BY s.`entry_id`');
#SELECT @sql;
PREPARE a FROM @sql;
EXECUTE a;
and this my php:
global $con;
$hasil = array();
$sql1 = "SET @sql = NULL;";
."SELECT GROUP_CONCAT(DISTINCT CONCAT(\'MAX(IF(`meta_key` = \"\', `meta_key`,\'\", `meta_value`,\"\")) AS \"\',`meta_key`,\'\"\')) INTO @sql
FROM wp_frmt_form_entry_meta
RIGHT JOIN wp_frmt_form_id
ON wp_frmt_form_entry_meta.meta_key = wp_frmt_form_id.meta_keyy
ORDER BY wp_frmt_form_entry_meta.meta_id;
SET @sql = CONCAT(\'SELECT `entry_id`, \', @sql, \'
FROM wp_frmt_form_entry_meta s
GROUP BY s.entry_id
ORDER BY s.entry_id\');
#SELECT @sql;
PREPARE a FROM @sql;
EXECUTE a;";
try {
$stmt = $con ->prepare($sql1);
$stmt->execute();
$stmt->setFetchMode(PDO::FETCH_ASSOC);
$rs = $stmt->fetchAll();
if($rs != null){
$i=0;
foreach ($rs as $val) {
$hasil[$i]['name-1'] = $val['name-1'];
...........
$i++;
}
}
} catch (Exception $e) {
echo 'Error getAllData = '.$e->getMessage();
}
return $hasil;
Please someone can help this out?
