0

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);
}
Community
  • 1
  • 1

0 Answers0