Welcome Guest, Not a member yet? Register   Sign In
Update until 2 and no more...
#1

[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;
}
It works until $row['banner']+1 is 1 or 2, next time the update must be performed it doesn't work.
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?
#2

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

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

[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')
[/quote]

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' Wink )
#4

[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 problem appears when $inserzione['active_banner'] is > 1 and for the 1st and 2nd iteration is selected the same entry to update.
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.




Theme © iAndrew 2016 - Forum software by © MyBB