MySQL - Multiple Queries - Any way to chain queries together / execute all in one statement?

[eluser]Unknown[/eluser]
In MySql/PHP, AFAIK, you cannot execute multiple sql statements in one mysql_query() to the database. The same seems to go for CodeIgniter and its Database class (Hmmm... correct me if I am wrong!!).

IE this does not work:

Code:

$this->db->query("UPDATE something...; SELECT something....;" );

In my situation I have a few methods that need to make "complicated" (well relatively complicated) updates to my tables.

Basically I need to create a temp table, fill it with the results of some query, and use those results to update another table.

My code ends up looking like:

Code:

$sql1 = "Create temp table... ; ";
$sql2 = "insert into temp table
             select something, count(*), sum(blah)
             from something
             ...
              ; " ;
$sql3 = " update table a, temp table b
          set a.X = ....... " ;

$sql4 = " drop temp table... ";

$this->db->query($sql1);
$this->db->query($sql2);
$this->db->query($sql3);
$this->db->query($sql4);

Is there a way that I can reduce the number of db->queries I need to make? Its a bit of an eyesore and seems an inefficient use of space. I guess I could store all the queries in an array and iteratively execute them, but I was hoping there would be some more elegant built-in function.
Ideally I would be able to create a stored procedure right? But I will be on shared hosting that may not support that functionality.

I was hoping I could do something like:

Code:

$this->db->query($sql1, $sql2, $sql3, $sql4);

Also, it would be nice if I didn't have to write the SQL inline with the PHP code. Is there any practice of separating the SQL from PHP similar to how views are separated from the models and controllers? Or would going with sprocs be the way to go here..?

Thanks,

a lowly database dude.

[eluser]Cristian Gilè[/eluser]
Hi tedtoy, for complicated queries AR is not a good choice. For maintainance and code readibiity using standard SQL queries is the best way.

To reduce the number of $this->db you could use an array:

Code:

$sql = array();
$sql['query1'] = "Create temp table... ; ";
$sql['query2'] = "insert into temp table
             select something, count(*), sum(blah)
             from something
             ...
              ; " ;
$sql['query3'] = " update table a, temp table b
          set a.X = ....... " ;

$sql['query4'] = " drop temp table... ";

$result = array();
foreach($sql as $key => $value)
{
}
    $result[$key] = $this->db->query($value);
}

At least partly, query bindings let you separate the SQL from PHP

Cristian Gilè

[eluser]Unknown[/eluser]
Thanks, I ended up doing something along the lines of:

Code:

$sql = array();
        
        $sql[] = " create temporary table IF NOT EXISTS temp_popular ( Id integer ) ;";

        $sql[] = " insert into temp_popular (Id)
                    select Id from L........ limit 3; ";

        $sql[] = " update links l, temp_popular tp
                    set l........
                    where l.Id = tp.Id ; ";
                    
        $sql[] = " drop table temp_popular ; " ;

        foreach ($sql as $sql_command)
        {    
            if ($debugging == 0)
            {
                $this->db->query($sql_command);
            }
            elseif ($debugging == 1)
            {
                echo $sql_command;
            }
        }
    }

But that's a neat idea you have in your example for storing the results from the queries. Thanks for the response,

-Teddy

[eluser]Ochetski[/eluser]
I guess using implode() before executing in this queries array would be a faster than a for loop. Not 100% sure. Mysql is faster than PHP.

You said that joining all in a row don't work, i guess multiple SELECTs would not work, but UPDATEs, DELETEs... don't work as well?

[eluser]SPeed_FANat1c[/eluser]
I thinks this is what you want:

http://ellislab.com/codeigniter/user-gui...tions.html


Your tables have to use InnoDB engine.

check if InnoDB is possible.

Code:

mysql> show engines;

if so, then check what engine is used for your table

Code:

mysql> show table status like 'table name';

if its not InnoDB then change it

Code:

mysql> alter table table_name engine = InnoDB;

Edit:
then you don't execute in on php query ut it act like one query - if on of queries fail, then all are backed to original state, like nothing has happened.