+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Update until 2 and no more... (/showthread.php?tid=53848)
Update until 2 and no more... - El Forum - 08-11-2012
[eluser]Alhazred[/eluser]
I have a table with a field called "banner" defined as
int not null default 0
this field must be incremented after some operations, this is the update query
Code:
$set = array('banner' => $row['banner']+1); //$row['banner'] contains the old number of banners
$this->db->where('id',$row['id']);
$this->db->update($table,$set);
if ( $this->db->affected_rows() == 1)
{
$inserzione['active_banner']--;
}
else //update error
{
$this->db->trans_rollback();
return FALSE;
}I've checked and $this->db->affected_rows() is 1, but the else is executed and I don't get why.
What can I try to do to understand the problem?
Update until 2 and no more... - El Forum - 08-11-2012
[eluser]Alhazred[/eluser]
Of course I've also tried to print the generated sql for the update query and it is correct, if I use it on phpmyadmin the query is executed and the field correctly updated.
Update until 2 and no more... - El Forum - 08-11-2012
[eluser]PhilTem[/eluser]
If I got your problem right, it would be easier for you to run a slightly different query with help of the CI user's guide
[quote author="CI user's gudie" date="0"]
set() will also accept an optional third parameter ($escape), that will prevent data from being escaped if set to FALSE. To illustrate the difference, here is set() used both with and without the escape parameter.
Code:
$this->db->set('field', 'field+1', FALSE);
$this->db->insert('mytable');
// gives INSERT INTO mytable (field) VALUES (field+1)
$this->db->set('field', 'field+1');
$this->db->insert('mytable');
// gives INSERT INTO mytable (field) VALUES ('field+1')That means for your code:
Code:
$this->db->set('banner', 'banner + 1', FALSE);
$this->db->where('id', $row['id']);
$this->db->update($table);and it should run over and over and over again without stopping at $row['banner'] == ( 1 OR 2 ) (for 'I don't even know the reason why'
)
Update until 2 and no more... - El Forum - 08-11-2012
[eluser]Alhazred[/eluser]
To debug printing some values I also added an exit; in a not convenient place, now I've removed it, put somewhere else and I think I've found the problem.
My query is inside a while which is inside a transaction, this is the code with the debug lines
Code:
$this->db->trans_begin();
while($inserzione['active_banner'] > 0)
{
//row to update selection which ends with
$row = $result->row_array();
$set = array('banner' => $row['banner']+1); //$row['banner'] contains the old number of banners
$this->db->where('id',$row['id']);
$this->db->update($table,$set);
echo $this->db->last_query()."<br />"; //debug string
if ( $this->db->affected_rows() == 1)
{
$inserzione['active_banner']--;
}
else //update error
{
$this->db->trans_rollback();
echo 'Inside the else<br />'; //debug string
exit; //stop to see the printed strings
return FALSE;
}
}
if ($this->db->trans_status() !== FALSE)
{
$this->db->commit();
return TRUE;
}
else
{
$this->db->rollback();
return FALSE;
}The output is
UPDATE `prodotti` SET `banner` = 3 WHERE `id` = '1-1344638814-2'
UPDATE `prodotti` SET `banner` = 3 WHERE `id` = '1-1344638814-2'
Inside the else
for both the iteration, the value to set is 3.
I think that happens as follow:
$inserzione['active_banners'] = 2
1st iteration
entry with 1-1344638814-2 id is selected
banner = 2
banner + 1 = 3
UPDATE `prodotti` SET `banner` = 3 WHERE `id` = '1-1344638814-2' is executed and succeeds
$inserzione['active_banners'] = 1
2nd iteration
entry with 1-1344638814-2 id is selected again
banner = 2
banner + 1 = 3
UPDATE `prodotti` SET `banner` = 3 WHERE `id` = '1-1344638814-2' is executed and result_rows is 0 because the banner value is already 3
The problem is that the 2nd iteration reads again the value actually stored into the db, not the updated one from the 1st iteration, so the "new" value is again 3 and not 4.
I think I have to change the logic.