Incrementing a counter in the database

[eluser]CI Simon[/eluser]
I'm having a problem with a function that needs to update a counter for the records found.

It's working fine on my local development machine but fails to update on the live server.

Can anyone give me an idea of where i've gone wrong.

Thanks,
Simon

Code:

function getAdvertiserEmailList ($region_id, $category_id)
    {
        $this->db->select('*');
        $this->db->from('fq_company');
        $this->db->join('fq_company_category', 'company_id = fq_company.id');
        $this->db->where('region_id', $region_id);
        $this->db->where('category_id', $category_id);
        $query = $this->db->get();

        $advertiser_id_array = array();
        $output = "";
        
        //Check if any companys are found
        if ($query->num_rows() > 0) {

            foreach ($query->result() as $advertisers) {
                
                    $advertiser_id_array[] = $advertisers->id;
    
                    $output .= $advertisers->company_email;
                    $output .= ", ";
                }
            
            $output = substr ($output, 0, -2);
            
            // Update company_count for emailed companies
            $this->db->set('company_count', 'company_count + 1', FALSE);
            $this->db->where_in('id', $advertiser_id_array);
            $query = $this->db->update('fq_company');
        
        }
        
        return $output;
    }

[eluser]CI Simon[/eluser]
I am drawing a conclusion that this is a problem with MySQL 4.1.20 (local) v MySQL 5.0.45 (local). I am going to upgrade database on live server and see what happens.

[eluser]xwero[/eluser]
I'm doing the same thing on lower version mysql databases and it works fine. Maybe there is a problem getting all the ids you need in the $advertiser_id_array?

[eluser]CI Simon[/eluser]
Oops 4.1.20 is the live database. Ive tried so many ways of doing this and they all seem to work locally and not live.

This also works locally but not live:

Code:

if ($query->num_rows() > 0) {

            foreach ($query->result() as $advertisers) {
                
                    $advertiser_id_array[] = $advertisers->id;
    
                    $output .= $advertisers->company_email;
                    $output .= ", ";
                    
                    $count = $advertisers->company_count;
                    $count = $count + 1;
                    
                    $sql = "UPDATE fq_company SET company_count = '$count' WHERE id = '$advertisers->id'";
                    $this->db->query($sql);
                    
                }
            
            $output = substr ($output, 0, -2);
            
        
        }

[eluser]CI Simon[/eluser]
OK - so having upgrade the database on the live server I have confirmed that it's not a database version issue. I have connected my localserver version to the live database and still doesn't work so all I can think of it that it could be Windows vs Linux issue. :-S

[eluser]Mirage[/eluser]
Simon -

I just sent you an email. Try logging the queries to see what's going on.

Cheers,
m

[eluser]CI Simon[/eluser]
Mirage,

Thanks for the suggestion - I was already trying to work out how to log database activity to the log files but hadn't been able to work it out. Eventually on Saturday I found some info here http://ellislab.com/codeigniter/user-gui...rrors.html
that explains for to log anything into the log files using log_message.

As usual when you know how it's easy with CodeIgniter!

Thanks again,
Simon

[eluser]Sumon[/eluser]
[quote author="CI Simon" date="1220732406"]OK - so having upgrade the database on the live server I have confirmed that it's not a database version issue. I have connected my localserver version to the live database and still doesn't work so all I can think of it that it could be Windows vs Linux issue. :-S[/quote]

i am not clear how is it be an operating system issue.

Mirage : would you please let me know what you suggest to resolve.

[eluser]CI Simon[/eluser]
[quote author="Sumon" date="1220877852"]i am not clear how is it be an operating system issue.[/quote]

It wasn't anything to do with the operating system. I still do not 100% understand what was happening but it was to do with the join in the original query and trying to use the ID field which was picking up the ID from the wrong table.

Why was it working locally - i need to do further tests but I think it was simply coincidence.

Of course I didn't post enough details for anyone to pick this up :red: but I learnt some stuff along the way.

[eluser]franto[/eluser]
I have exactly same problem, and dont know how to solve it

function increment_offence_count($message_id, $value)
{
$this->db->set('offence_count', 'offence_count + '.$value);
$this->db->where('id', $message_id);
return $this->db->update($this->_table);
}

if I use insert instead of update, it insert new row, but update didnt update anything. Does anybody knows what can be problem?

THank you