[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.
