Click here to Skip to main content
15,392,226 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
hi friends, i am trying 2 days i didnt get a pivot table in mysql codeigniter. pls help me.
i got an error:
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 'Writing THEN qty ELSE 0 END) AS English - Cursive Writing ,max(case when' at line 1

What I have tried:

 $hasil = $this->db->query('SET SESSION group_concat_max_len=100000');
        $hasil = $this->db->query('SET @sql=(SELECT GROUP_CONCAT(DISTINCT concat("max(case when s.item_name=", item_name, " THEN qty ELSE 0 END) AS ", item_name, " ")
) FROM tbl_iss_books_dtl d INNER JOIN tbl_stationary s ON s.item_code=d.stat_item_code)');
        $hasil = $this->db->query('SET @pivot_statement = CONCAT("SELECT ibh_id,",@sql," from tbl_iss_books_dtl d INNER JOIN tbl_stationary s ON s.item_code=d.stat_item_code group by ibh_id order by ibh_id asc")');
        $hasil = $this->db->query('PREPARE complete_pivot_statement FROM @pivot_statement');
        $hasil = $this->db->query('EXECUTE complete_pivot_statement');
        return $hasil->result_array();
Updated 1-Jun-22 6:18am
Richard MacCutchan 1-Jun-22 12:16pm
Start by displaying the actual SQL statement in error so you can actually see what is wrong.

1 solution

It looks like you are trying to create a column in your result set called English - Cursive Writing. That is an invalid column name. You need to surround it with the appropriate delimiters which in MariaDb I believe is ` i.e.
`English - Cursive Writing`

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900