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

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;';

CodePudding user response：

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;