-1

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;';
kelvin daniel
  • 27
  • 6
  • Since you're only using single quotes in the query, you could just keep it "as is" and use double quotes for the string: `$sql = "..."` – M. Eriksson Nov 20 '22 at 10:17
  • @M.Eriksson I changed double quotes to single quotes to see if it works, originally it was double quotes and didn't work. – kelvin daniel Nov 20 '22 at 10:57
  • All these statements should be executed separately. Do not use `mysqli_multi_query()` as this is a terrible function that is very unsafe – Dharman Nov 20 '22 at 11:22
  • @Dharman I will see about that. Thank you. – kelvin daniel Nov 20 '22 at 11:48
  • @kelvindaniel Why do you build a dynamic query in MySQL when you have PHP available? Can't you generate the dynamic SQL query in PHP instead? – Progman Nov 20 '22 at 16:21

1 Answers1

2

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;
Jae
  • 278
  • 1
  • 6