Question 245996 in PHP Versions Question posted in
You can find a list of all versions here.

Php versions – A Crosstab Query in PHP and MYSQL

I was searching around for a way to implement a crosstab query in MySQL and found an answer from this post show dates in rows as dynamic columns in MySQL. I tried the query and got it to work but how do I write the query in PHP using MySQL procedural style. Below is the query:

SELECT
  GROUP_CONCAT(DISTINCT
               CONCAT('MAX(IF(`week_start` = "', `week_start`,'", `weekly_value`,0)) AS "',DATE(`week_start`),'"')
              ) INTO @sql
FROM (SELECT * FROM crosstab WHERE `week_start` BETWEEN NOW() - INTERVAL 4 WEEK  AND NOW()) t1;


SET @sql = CONCAT('SELECT s.`ID`, s.`name`,  ', @sql, ' 
                  FROM (SELECT * FROM crosstab WHERE `week_start` BETWEEN NOW() - INTERVAL 4 WEEK  AND NOW()) s
                 GROUP BY s.`name`
                 ORDER BY s.`ID`');
                 
SELECT @sql;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

$sql = "SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
               CONCAT('MAX(IF(`week_start` = "', `week_start`,'", `weekly_value`,0)) AS "',DATE(`week_start`),'"')
              ) INTO @sql
FROM (SELECT * FROM crosstab WHERE `week_start` BETWEEN NOW() - INTERVAL 4 WEEK  AND NOW()) t1;


SET @sql = CONCAT('SELECT s.`ID`, s.`name`,  ', @sql, '
                  FROM (SELECT * FROM crosstab WHERE `week_start` BETWEEN NOW() - INTERVAL 4 WEEK  AND NOW()) s
                 GROUP BY s.`name`
                 ORDER BY s.`ID`');

SELECT @sql;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;";

// echo $sql;
// exit;


$report_query = mysqli_query($conn, $sql);

If I print out the resulting sql and paste it in PHPMyAdmin, the query executes fine but on php in give an SQL syntax error "Fatal error: Uncaught mysqli_sql_exception: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘SELECT GROUP_CONCAT(DISTINCT CONCAT(‘MAX(IF(week_start =…’ at line 2 in..". Is there a way to write the query in php?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I found a way using mysqli multi_query function :

    if ($mysqli -> multi_query($sql)) {
  do {
    // Store first result set
    if ($result = $mysqli -> store_result()) {
      while ($row = $result -> fetch_assoc()) {
        $data[] = $row;
      }
     $result -> free_result();
    }
   
     //Prepare next result set
  } while ($mysqli -> next_result());
}

// Resulting $data array:

Array
(
    [0] => Array
        (
            [@sql] => SELECT s.`ID`, s.`name`,  MAX(IF(`week_start` = "2023-08-14", `weekly_value`,0)) AS "2023-08-14",MAX(IF(`week_start` = "2023-08-21", `weekly_value`,0)) AS "2023-08-21",MAX(IF(`week_start` = "2023-08-28", `weekly_value`,0)) AS "2023-08-28"
                  FROM (SELECT * FROM crosstab WHERE `week_start` BETWEEN NOW() - INTERVAL 4 WEEK  AND NOW()) s
                 GROUP BY s.`name`
                 ORDER BY s.`ID`
        )

    [1] => Array
        (
            [id] => 4
            [name] => Jacqui
            [2023-08-14] => 29
            [2023-08-21] => 44
            [2023-08-28] => 49
        )

    [2] => Array
        (
            [id] => 5
            [name] => Monica
            [2023-08-14] => 31
            [2023-08-21] => 33
            [2023-08-28] => 31
        )

    [3] => Array
        (
            [id] => 6
            [name] => Sarah
            [2023-08-14] => 58
            [2023-08-21] => 48
            [2023-08-28] => 44
        )

)

    I just need to work on the array to produce totals. Thank you to all who tried to help.


  2. 0

    What about this?

    <?php
// Connect to your MySQL database
$mysqli = mysqli_connect("hostname", "username", "password", "database");

if (mysqli_connect_errno()) {
    printf("Connect failed: %sn", mysqli_connect_error());
    exit();
}

// Define your SQL query
$sql = "SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
               CONCAT('MAX(IF(`week_start` = "', `week_start`,'", `weekly_value`,0)) AS "',DATE(`week_start`),'"')
              ) INTO @sql
FROM (SELECT * FROM crosstab WHERE `week_start` BETWEEN NOW() - INTERVAL 4 WEEK  AND NOW()) t1;


SET @sql = CONCAT('SELECT s.`ID`, s.`name`,  ', @sql, '
                  FROM (SELECT * FROM crosstab WHERE `week_start` BETWEEN NOW() - INTERVAL 4 WEEK  AND NOW()) s
                 GROUP BY s.`name`
                 ORDER BY s.`ID`');

SELECT @sql;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;";

// Execute your SQL query
if (mysqli_multi_query($mysqli, $sql)) {
    do {
        if ($result = mysqli_store_result($mysqli)) {
            while ($row = mysqli_fetch_row($result)) {
                printf("%sn", $row[0]);
            }
            mysqli_free_result($result);
        }
    } while (mysqli_next_result($mysqli));
}

// Close your MySQL database connection
mysqli_close($mysqli);
?>
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search