phpmysqlimysqli-multi-query

which quotes should be used to write a WHERE clause in this php mysqli query?


In phpMyAdmin SQL works fine with a WHERE clause in double quotes:

SET @sql = NULL;
SELECT GROUP_CONCAT(DISTINCT CONCAT('max(case when category = ''', category, ''' then status end) ',category)) INTO @sql FROM Meeting;
SET @sql = CONCAT('SELECT username,department ', @sql, ' FROM Meeting WHERE department ="SOCIAL WORK" GROUP BY username');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

With PHP it doesn't work, no data is returned:

 $sql = 'SET @sql = NULL;';
$sql.= 'SELECT GROUP_CONCAT(DISTINCT CONCAT("max(case when category = """, category, """ then status end) ",category)) INTO @sql FROM Meeting;';
$sql.= 'SET @sql = CONCAT("SELECT username,department, ", @sql, " FROM Meeting WHERE department ="SOCIAL WORK" GROUP BY username");';
$sql.= 'PREPARE stmt FROM @sql;';
$sql.= 'EXECUTE stmt;';   
$sql.= 'DEALLOCATE PREPARE stmt;'; 

But if I remove a WHERE clause to filter the table, it works, and gives all data:

 $sql = 'SET @sql = NULL;';
$sql.= 'SELECT GROUP_CONCAT(DISTINCT CONCAT("max(case when category = """, category, """ then status end) ",category)) INTO @sql FROM Meeting;';
$sql.= 'SET @sql = CONCAT("SELECT username,department, ", @sql, " FROM Meeting GROUP BY username");';
$sql.= 'PREPARE stmt FROM @sql;';
$sql.= 'EXECUTE stmt;';   
$sql.= 'DEALLOCATE PREPARE stmt;'; 

Also, if I put a WHERE clause to filter the pivot only, as shown below...It works, I get data according to the WHERE clause.

 $sql = 'SET @sql = NULL;';
$sql.= 'SELECT GROUP_CONCAT(DISTINCT CONCAT("max(case when category = """, category, """ then status end) ",category)) INTO @sql FROM Meeting WHERE category="Internal";';
$sql.= 'SET @sql = CONCAT("SELECT username,department, ", @sql, " FROM Meeting GROUP BY username");';
$sql.= 'PREPARE stmt FROM @sql;';
$sql.= 'EXECUTE stmt;';   
$sql.= 'DEALLOCATE PREPARE stmt;'; 

Solution

  • The way you are escaping the strings is not consistent with the original sql query. So i suggest you just use a heredoc string to simplify readability and conversion. Note: linebreaks, tabs, spaces will convert as well.

    $str = <<<EOF
    SET @sql = NULL;
    SELECT GROUP_CONCAT(DISTINCT CONCAT('max(case when category = ''', category, ''' then status end) ',category)) INTO @sql FROM Meeting;
    SET @sql = CONCAT('SELECT username,department ', @sql, ' FROM Meeting WHERE department ="SOCIAL WORK" GROUP BY username');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    EOF;