Executing muliple sql statements with Codeigniter

Question

So it turns out Codeigniter struggles to execute more than 1 SQL statement at a time.

On way round this is to use the trans_start and trans_complete functions, however, this does not work for creating temp tables.

    $this->db->trans_start();
    $this->db->query( 'CREATE TEMPORARY TABLE tmptable_1;');
    $this->db->query( 'SELECT * FROM banner_data WHERE id = '.$id.';');
    $this->db->query( 'UPDATE tmptable_1 SET id = NULL;');
    $this->db->query( 'INSERT INTO banner_data SELECT * FROM tmptable_1;');
    $this->db->query( 'DROP TEMPORARY TABLE IF EXISTS tmptable_1;');
    $this->db->trans_complete(); 

The above code generates the following error:

A table must have at least 1 column

CREATE TEMPORARY TABLE tmptable_1;

Filename: models/Banners_generator_model.php

Is there a way to execute a whole block of SQL in one go?

Answer 1

I figured out a way around this:

    $this->db->trans_start();
    $this->db->query( 'CREATE TEMPORARY TABLE tmptable_1 SELECT * FROM banner_data WHERE id = '.$id.';');
    $this->db->query( 'UPDATE tmptable_1 SET id = NULL;');
    $this->db->query( 'INSERT INTO banner_data SELECT * FROM tmptable_1;');
    $this->db->query( 'DROP TEMPORARY TABLE IF EXISTS tmptable_1;');
    $this->db->trans_complete(); 

By merging the original first two lines, the database builds the table columns based on the structure of the table you are copying from.