Using $this->EE->db->query(); is it possible to execute a query as complicated as this:

CREATE TEMPORARY TABLE exp_temp_table (...);
INSERT INTO exp_temp_table () SELECT ...;
UPDATE exp_temp_table ... SET ...;
SELECT ... FROM exp_temp_table ...;

For obvious reasons I've removed most of the actual statements.

This code executes perfectly in phpMyAdmin, but when I try to use $this->EE->db->query(); I get a SQL error at INSERT INTO exp_temp_table ()... - is it not possible to execute queries with multiple statements? If not, is there some way I can work around this?


I was worried about the temporary table not persisting between queries but the following code worked correctly:

$this->EE->db->query('CREATE TEMPORARY TABLE exp_temp_table (...)');
$this->EE->db->query('INSERT INTO exp_temp_table () SELECT ...');
$this->EE->db->query('UPDATE exp_temp_table ... SET ...');
$query = $this->EE->db->query('SELECT ... FROM exp_temp_table ...');

I think, you should run queries via Transactions. You can find the doc from CodeIgnitor's user guide http://ellislab.com/codeigniter/user-guide/database/transactions.html and it also works within EE.

  • An excellent point thanks for mentioning. Mar 3 '13 at 10:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.