Dynamic pivot table in MariaDB not working query in php

Question

Hey guys can you help me i need to convert this query in to an PHP code.

set @sql = (
    select group_concat(distinct 
        concat(
            "sum(case when `test_id`='",test_id, "' then `points` ELSE '0' end) as `", `test_id`, "`"
        )
    ) 
    from test_answered WHERE checked = '1'
);
set @sql = concat("select url_id, ", @sql, " from test_answered group by `url_id`");
prepare stmt from @sql;
execute stmt;
deallocate prepare stmt;

i can't try that code in php. By the way im using PDO. So this will be hard on me.

I try this code

$quer = "SELECT url_id,test_id,points,GROUP_CONCAT(DISTINCT CONCAT('SUM(CASE WHEN test_id = 'test_id' THEN points ELSE '0' END))') AS test FROM  test_answered WHERE checked = '1' GROUP BY id";
    $arr4 = ['test_id'=>$array];
    $data3 = $test_a->query($quer);

But it give me this

Fatal error: Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 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 '' THEN points ELSE '0' END))') AS test FROM test_answered WHERE checked = '1...' at line 1

`can't try that code in php`? Why not, do you get an error? Please provide more details. — user3783243, Apr 10 '23 at 01:11
Sorry yeah, everytime i run it in PHP code it gets error. i try to change it into this SELECT url_id,test_id,points,GROUP_CONCAT(DISTINCT CONCAT(CASE WHEN test_id = 'test_id' THEN points ELSE '0' END)) AS test FROM test_answered WHERE checked = '1' GROUP BY id but it doesn't give what is need — Trina Chillin, Apr 10 '23 at 01:12
Please provide code you tried and error message(s) you receive. — user3783243, Apr 10 '23 at 01:15
I edited the question and put the thing that you need. I hope you find a great solution to this problem — Trina Chillin, Apr 10 '23 at 01:24
I try this code `$quer = "SELECT url_id,test_id,points,GROUP_CONCAT(DISTINCT CONCAT(SUM(CASE WHEN test_id = 'test_id' THEN points ELSE '0' END))) AS test FROM test_answered WHERE checked = '1' GROUP BY id";` — Trina Chillin, Apr 10 '23 at 01:26
Still give me this, but different one Fatal error: Uncaught PDOException: SQLSTATE[HY000]: General error: 1111 Invalid use of group function — Trina Chillin, Apr 10 '23 at 01:26
I suggest you examine the final query: `SELECT @sql;` It's easier to spot mistakes looking at the actual SQL, instead of looking at the code that you believe formats the SQL. — Bill Karwin, Apr 10 '23 at 02:34
I corrected the title and tags, because the error message clearly indicates you are using MariaDB, not MySQL. These are not the same product. MariaDB started in 2010 as a fork of MySQL, but both products have continued to change since then, and they are no longer compatible. — Bill Karwin, Apr 10 '23 at 02:36

Pippo · Answer 1 · 2023-04-10T04:49:32.970

It seems that you omitted some quotes and commas in your query.

Anyway try so:

<?php
$conn = new  PDO(/* HERE_THE_CONNECTION_STRING */, $user, $pwd);

$innerPivotQuery = <<<SQL
 SELECT GROUP_CONCAT(DISTINCT CONCAT(" SUM(CASE WHEN `test_id`='", test_id, "'  THEN `points` ELSE '0' END) as `", `test_id`, "`")) 
 FROM test_answered WHERE checked = '1'
SQL;

$pivotQuery = <<<SQL
 SELECT CONCAT("SELECT url_id,",
               ($innerPivotQuery),
               " FROM test_answered GROUP BY `url_id`");
SQL;

$pivotStmt = $conn->query($pivotQuery);
$query = $pivotStmt->fetch()[0];

$stmt = $conn->query($query);

$data = $stmt->fetchAll(PDO::FETCH_ASSOC);

This is an example, you have to add error management

Dynamic pivot table in MariaDB not working query in php

1 Answers1