Tell me more ×
ExpressionEngine® Answers is a question and answer site for administrators, end users, developers and designers for ExpressionEngine® CMS. It's 100% free, no registration required.
up vote 2 down vote favorite

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?

share|improve this question
add comment

2 Answers

up vote 2 down vote accepted

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 ...');
share|improve this answer
add comment
up vote 1 down vote

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.

share|improve this answer
 
An excellent point thanks for mentioning. –  c.cam108 Mar 3 at 10:04
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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