Multiple update in single query.

[eluser]Arun Joshi[/eluser]
I have to update multiple rows of my photos table. I have a dynamic built query like this.

Code:

$query    =    "
UPDATE photos SET description='des one' WHERE photo_id='4';
UPDATE photos SET description='des two'' WHERE photo_id='5';
UPDATE photos SET description='des 3' WHERE photo_id='6';
";

But when I tried this with $this->db->query($query); it shows error. When I copied this query to phpmyadmin, it executed successfully.

Any reason there for this?

Thanks
Arun

[eluser]sunny.by[/eluser]
[quote author="Arun Joshi" date="1285161527"]I have to update multiple rows of my photos table. I have a dynamic built query like this.

Code:

$query    =    "
UPDATE photos SET description='des one' WHERE photo_id='4';
UPDATE photos SET description='des two'' WHERE photo_id='5';
UPDATE photos SET description='des 3' WHERE photo_id='6';
";

But when I tried this with $this->db->query($query); it shows error. When I copied this query to phpmyadmin, it executed successfully.

Any reason there for this?

Thanks
Arun[/quote]

Hello, Arun.
I think error in next:
For execute your query CI in file mysql_driver.php use next function:

Code:

/**
     * Execute the query
     *
     * @access    private called by the base class
     * @param    string    an SQL query
     * @return    resource
     */    
    function _execute($sql)
    {
        $sql = $this->_prep_query($sql);
        return @mysql_query($sql, $this->conn_id);
    }

but mysql_query don't can work correct with your multiple query.

PS: sorry for my english

[eluser]Arun Joshi[/eluser]
@sunny.by,

How to solve this issue?

[eluser]sunny.by[/eluser]
@arun

Code:

$query = "UPDATE photos SET description='des one' WHERE photo_id='4'";
$this->db->query($query);
$query = "UPDATE photos SET description='des two'' WHERE photo_id='5'";
$this->db->query($query);
$query = "UPDATE photos SET description='des 3' WHERE photo_id='6"';
$this->db->query($query);

[eluser]Arun Joshi[/eluser]
Ok, I tried to avoid multiple db access. Thats why I created one single query. Anyway thanks for your time...

[eluser]sunny.by[/eluser]
Look http://php.net/manual/en/function.mysql-query.php

mysql_query() sends a unique query (multiple queries are not supported) to the currently active database on the server that's associated with the specified link_identifier.

[eluser]cahva[/eluser]
Even phpmyadmin will parse your sql and execute those line by line.

Smart thing IMO is to build an array and iterate it and use AR's update. Something like this:

Code:

$arr = array(
    4 => array('description' => 'des one'),
    5 => array('description' => 'des two'),
    6 => array('description' => 'des 3')
);

foreach ($arr as $id => $v)
{
    $this->db->update('photos',$v,array('photo_id' => $id);
}

If you have to do very large sets and you're running out of steam, then you should run large queries thru shell using php's exec(or passthru etc.) function and execute your sql there.

[eluser]RedIgniter[/eluser]
I think this link will help you.

http://ellislab.com/codeigniter/user-gui...tml#update

- Greg

[eluser]Truong Chuong DUong[/eluser]
Try to run multi query in same execute will save server cost. Only need to change a bit in file CodeIgniter\system\database\drivers\mysqli\mysqli_driver.php will make CodeIgniter support this feature:

/**
* Execute the query
*
* @param string an SQL query
* @return mixed
*/
protected function _execute($sql)
{
/*
Remove this line
return @$this->conn_id->query($this->_prep_query($sql));
*/

$sql = $this->_prep_query($sql);
$result = NULL;

/* execute multi query */
if (@$this->conn_id->multi_query($sql))
{
do
{
if ($result)
@$result->free();

$result = $this->conn_id->store_result()
}
while ($this->conn_id->next_result());
}

if ($result === FALSE)
$result = 1;

/* close connection */
return $result;//Only return last result

}


If you see @mysqli_query($this->conn_id, $sql); instead of @$this->conn_id->query($this->_prep_query($sql)) (old version)
Please use these code:


/**
* Execute the query
*
* @param string an SQL query
* @return mixed
*/
protected function _execute($sql)
{
/*
Remove these line
$result = @mysqli_query($this->conn_id, $sql);
return $result;
*/


$sql = $this->_prep_query($sql);
$result = NULL;

/* execute multi query */
if (@mysqli_multi_query($this->conn_id, $sql))
{
do
{
if ($result)
@mysqli_free_result($result);

$result = @mysqli_store_result($this->conn_id);
}
while (mysqli_next_result($this->conn_id));
}

if ($result === FALSE)
$result = 1;

return $result;//Only return last result

}