Multiple queries in code igniter - semicolon breaks query

[eluser]petkodil[/eluser]
I am working on a project where I need to create a pivot table with dynamic columns. To this end I am using the tutorial as described here: http://stratosprovatopoulos.com/web-deve...c-columns/

Using that tutorial, I was able to make a proper query to make a pivot table. When I execute it via phpMyAdmin SQL dialog, the query runs fine and the results come up as expected. The problem I ran into is that when I try to run the query through Code Igniter via $this->db->query($query), it fails at the first semicolons that mark the end of the first SQL statement in the overall query. The problem is that the statement is built up of several queries that I need to run at the same time. I read about transactions on CI, but could not figure how (if possible) to obtain the result of the query. Will greatly appreciate any advice on a proper way to run that query on CI and avoid the semicolon problem.

Thanks!

[eluser]CroNiX[/eluser]
It would help to see the query.

[eluser]petkodil[/eluser]
The query is complex and includes a virtual table as result from another query. But the key thing is that it works fine when I execute it from within phpMyAdmin and
produces the expected results. When executed in CI via $this->db->query($query), where $query is the query below, the code fails with error. My debugging indicates that the problem is the several SQL statements. The code executes the first line up to the first semicolon (SET @sql = NULL and then returns an error.

Here is the statement:

SET @sql = NULL;

SELECT GROUP_CONCAT(DISTINCT CONCAT( 'MAX(IF(date = ''', `date`, ''', list_value1, NULL)) AS ', REPLACE(date,'-','I') ) ) INTO @sql FROM (SELECT t.item_id, t.item_name,t.date, (SELECT sum(t2.list_value1) FROM form_results_listitems t2 where t2.item_id = t.item_id AND t2.date=t.date group by t2.item_id) as list_value1 FROM form_results_listitems t WHERE `list_unique_id` = 50 AND date between '2014-06-01' AND '2014-06-20' group by date,item_id) as form_results_listitems1;

SET @sql = CONCAT('SELECT item_id, ', @sql, ' FROM (SELECT t.item_id, t.item_name,t.date, (SELECT sum(t2.list_value1) FROM form_results_listitems t2 where t2.item_id = t.item_id AND t2.date=t.date group by t2.item_id) as list_value1 FROM form_results_listitems t WHERE `list_unique_id` = 50 group by date,item_id) as form_results_listitems1 GROUP BY item_id');

PREPARE stmt FROM @sql;

EXECUTE stmt;

I hope that will shed some light, thanks in advance!

[eluser]CroNiX[/eluser]
Yes, CI will only execute one query per statment. This looks like a good case for a stored procedure and then use $this->db->call_function('your_procedure()', $param1, $param2); to execute.

[eluser]petkodil[/eluser]
Thanks for the suggestion. I had no experience with stored procedures, but after some reading seems like this could be my thing.
I have one other problem now - I created a test stored procedure but I can not invoke it and get its result using the suggested method $this->db->call_function('test_procedure');

I get this error:
db_error_heading
This feature is not available for the database you are using.

I guess I am missing something. I came across this thread that has some suggestions:
http://ellislab.com/forums/viewthread/71141/ but am not sure if I should stick to the call_function method or try any of those.

Thanks!