I found the answer for my problem from this mysql select dynamic row values as column names, another column as value. Here's the SQL:
SELECT CONCAT(
'SELECT dm_generics.dm_id', GROUP_CONCAT(CONCAT('
, t_', REPLACE(field_name, '', ''), '.field_value
AS ', REPLACE(field_name, '', ''), ''
) SEPARATOR ''),
' FROM dm_generics ', GROUP_CONCAT(CONCAT('
LEFT JOIN dm_generics AS t_', REPLACE(field_name, '', ''), '
ON dm_generics.dm_id = t_', REPLACE(field_name, '', ''), '.dm_id
AND t_', REPLACE(field_name, '', ''), '.field_name = ', QUOTE(field_name)
) SEPARATOR ''),
' GROUP BY dm_generics.dm_id'
) INTO @qry FROM (SELECT DISTINCT field_name FROM dm_generics) t;
PREPARE stmt FROM @qry;
EXECUTE stmt;
But, I was confused how to execute that query inside the PDO.
I had tried to use something like this:
$stmt = $dbh->prepare($sql);
if($stmt->execute())
{
$object = $stmt->fetchAll(PDO::FETCH_OBJ);
return $object;
}
Instead of using PDO::prepare, I also tried to use PDO query
$stmt = $dbh->query($sql);
But, I got an empty array or null. I guess there was something 'wrong' with the query, maybe because there's a MySQL variable inside?
Here's the fiddle provided by the answer http://sqlfiddle.com/#!2/6e689/1
So, could you please tell me what the problem is and how to make it work?
UPDATE:
As Your Common Sense suggestion, now i can make those query worked. Here's the code:
$sql = "SELECT CONCAT(
'SELECT dm_generics.dm_id', GROUP_CONCAT(CONCAT('
, t_', REPLACE(field_name, '', ''), '.field_value
AS ', REPLACE(field_name, '', ''), ''
) SEPARATOR ''),
' FROM dm_generics ', GROUP_CONCAT(CONCAT('
LEFT JOIN dm_generics AS t_', REPLACE(field_name, '', ''), '
ON dm_generics.dm_id = t_', REPLACE(field_name, '', ''), '.dm_id
AND t_', REPLACE(field_name, '', ''), '.field_name = ', QUOTE(field_name)
) SEPARATOR ''),
' GROUP BY dm_generics.dm_id'
) INTO @qry FROM (SELECT DISTINCT field_name FROM dm_generics) t;";
$sql2 = "PREPARE stmt FROM @qry;";
$sql3 = "EXECUTE stmt;";
$stmt = $dbh->prepare($sql);
$stmt2 = $dbh->prepare($sql2);
$stmt3 = $dbh->prepare($sql3);
if($stmt->execute() && $stmt2->execute() && $stmt3->execute())
{
return $stmt3->fetchAll(PDO::FETCH_OBJ);
}