Complex sql statement in Laravel 3

Question

This is my code at the moment

    $piv0 = "SET @pivot_query = NULL;";
    $piv1 = "SELECT GROUP_CONCAT(DISTINCT CONCAT('MAX(IF(custom_field = \"\"',custom_field,\"\"', cf_values, NULL)) AS ',custom_field)) INTO @pivot_query FROM custom_fields_values; ";

    $piv2 = "SET @pivot = CONCAT('SELECT subscriber_id, ', @pivot_query, ' FROM custom_fields_values GROUP BY subscriber_id');";

    $piv3 = "SET @sql = CONCAT('SELECT * FROM user_data_3 LEFT JOIN (',@pivot,') piv ON user_data_5.aw_id = piv.subscriber_id');";
    $piv4 = "PREPARE stmt FROM @sql; ";
    $piv5 = "EXECUTE stmt;";
    $piv6 = "DEALLOCATE PREPARE stmt;";

    $db = DB::connection()->pdo;
    $db->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);

    $query = $db->prepare($piv0);
    $query->execute();

    $query = $db->prepare($piv1);
    $query->execute();

    $query = $db->prepare($piv2);
    $query->execute();

    $query = $db->prepare($piv3);
    $query->execute();

    $query = $db->prepare($piv4);
    $query->execute();

    $query = $db->prepare($piv5);
    $query->execute();

    $query = $db->prepare($piv6);
    $query->execute();

    $result = $query->fetchAll();

However, I am getting the error:

General error: 2014 Cannot execute queries while other unbuffered queries are active.  Consider using PDOStatement::fetchAll().  Alternatively, if your code is only ever going to run against mysql, you may enable query buffering by setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute.

I looked at some other SO questions and it was mentioned that $query->fetchAll() and/or $query->closeCursor() should help, but they didn't help me here.

I am getting the error on $query = $db->prepare($piv4);

What else should I try?

echo_Me · Answer 1 · 2014-02-12T13:21:58.653

2

you are executing parts or one query while you should just add them by . .try exucuting them like that.

$piv = "SET @pivot_query = NULL;";
$piv .= "SELECT GROUP_CONCAT(DISTINCT CONCAT('MAX(IF(custom_field = \"\"',custom_field,\"\"', cf_values, NULL)) AS ',custom_field)) INTO @pivot_query FROM custom_fields_values; ";

$piv .= "SET @pivot = CONCAT('SELECT subscriber_id, ', @pivot_query, ' FROM custom_fields_values GROUP BY subscriber_id');";

$piv .= "SET @sql = CONCAT('SELECT * FROM user_data_3 LEFT JOIN (',@pivot,') piv ON user_data_5.aw_id = piv.subscriber_id');";
$piv .= "PREPARE stmt FROM @sql; ";
$piv .= "EXECUTE stmt;";
$piv .= "DEALLOCATE PREPARE stmt;";  
 $db = DB::connection()->pdo;

$query = $db->prepare($piv);
$query->execute();
$result = $query->fetchAll();

EDIT:

 $piv = "SET @pivot_query = NULL;
         SELECT GROUP_CONCAT(DISTINCT CONCAT('MAX(IF(custom_field = \"\"',custom_field,\"\"', cf_values, NULL)) AS ',custom_field)) INTO @pivot_query FROM custom_fields_values; 
         SET @pivot = CONCAT('SELECT subscriber_id, ', @pivot_query, ' FROM custom_fields_values GROUP BY subscriber_id');
         SET @sql = CONCAT('SELECT * FROM user_data_3 LEFT JOIN (',@pivot,') piv ON user_data_5.aw_id = piv.subscriber_id');
         PREPARE stmt FROM @sql; 
         EXECUTE stmt;
         DEALLOCATE PREPARE stmt;";

edited Feb 12 '14 at 13:21

answered Feb 12 '14 at 12:42

echo_Me

37,078
5
58
78

Already tried that and it's not working either - I'm getting the same error. – misaizdaleka Feb 12 '14 at 12:47
New error - "SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT GROUP_CONCAT(DISTINCT CONCAT('MAX(IF(custom_field = ""',custom_field,""',' at line 1" I replaced "" with '' in both places, but I am getting the error again. Btw, I found this query online. – misaizdaleka Feb 12 '14 at 13:00
This is where I took the query from: http://stackoverflow.com/questions/12598120/mysql-pivot-table-query-with-dynamic-columns – misaizdaleka Feb 12 '14 at 13:10
Removed setAttribute, tried new query, not working again, same syntax error (something with quotes) as before. – misaizdaleka Feb 12 '14 at 14:12
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/47326/discussion-between-misaizdaleka-and-echo-me) – misaizdaleka Feb 12 '14 at 14:18

misaizdaleka · Accepted Answer · 2014-02-13T13:00:05.560

I managed to come up with a workaround:

$middleQueryRes = DB::query("SELECT GROUP_CONCAT(DISTINCT CONCAT('MAX(IF(custom_field =''', custom_field,''', cf_values, NULL)) AS ',custom_field)) FROM custom_fields_values;");
foreach ($middleQueryRes[0] as $value) {
   $middleQueryRes = $value
   break;
 }

$middleQuery2 = "SELECT subscriber_id, $middleQueryRes FROM custom_fields_values GROUP BY subscriber_id";

$finalRes = DB::query("SELECT * FROM user_data_3 LEFT JOIN ($middleQuery2) piv ON user_data_5.aw_id = piv.subscriber_id");

Complex sql statement in Laravel 3

2 Answers2