Using varialbes with more than one sql operation.

[eluser]Nonox[/eluser]
Hi, I have a question about delete/updates/insert using $this->db->query. I tried to run something like this:

Code in the model script.

Code:

for ($i = 1; $i <= 10; $i++ )
    {
        $id = $this->input->post('field'.$i);
        $sql .= "DELETE FROM table WHERE id =".$this->db->escape($id).";";
    }
    $this->db->query($sql);

Return error:

A Database Error Occurred

Error Number: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ';DELETE FROM table WHERE id =2' at line 1

When I use pure PHP code this works, but I dont konw how I have to use this with CI.

Any help would be very much appreciated.
Thanks!

[eluser]manilodisan[/eluser]
I don't think it will work. I suggest you to execute the query inside the loop:

Code:

for ($i = 1; $i <= 10; $i++ )
{
        $id = $this->input->post('field'.$i);
        $this->db->query("DELETE FROM table WHERE id =".$this->db->escape($id)."");
}

[eluser]xwero[/eluser]
Why would you use different sql statements?

Code:

$ids = array();
for ($i = 1; $i <= 10; $i++ )
{
   $ids[] = $this->input->post('field'.$i);
}
$this->db->query("DELETE FROM table WHERE id IN (".$this->db->escape(implode(',',$ids)).")");

But i would advise you to add a check to make sure the posted field has a value and to make sure the ids array is not empty.

[eluser]Nonox[/eluser]
First of all I want to thank you for your response.

manilodisan: You rigth, if I execute in that way works properly, but I need to make a group with some sql statements in a variable and execute all together.

xwero: Is true, I can use the IN function in this case... but... What happen if I need to do something like this?:

DELETE FROM table WHERE id =1;
INSERT INTO table VALUE (1,'test');


It's just an example.

(Sorry for my school english)

[eluser]xwero[/eluser]
I think the db->query method is designed for one sql statement. But i wonder are multiple statements in a query processed faster then multiple queries? And how do you use it in pure php as the mysql_query documentation states :

mysql_query() sends an unique query (multiple queries are not supported)

[eluser]Nonox[/eluser]
I was revising and I had a confucion, you have reason, I'm going to change the logic for to run one sql statement at a time.

THANKS YOU AGAIN!