Executing multiple SQL statements with Database class

Question

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?

colincameron · Accepted Answer · 2013-03-01 15:47:09Z

2

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 ...');

answered Mar 1, 2013 at 15:47

colincameron

3451 silver badge12 bronze badges

Add a comment |

Bhashkar Yadav · Accepted Answer · 2013-03-02 12:26:58Z

1

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.

answered Mar 2, 2013 at 12:26

Bhashkar Yadav

5,07216 silver badges23 bronze badges

Add a comment |

Stack Exchange Network

Executing multiple SQL statements with Database class

2 Answers 2

Your Answer

Hot Network Questions

Executing multiple SQL statements with Database class

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions