Incrementing a counter in the database

[eluser]Phil Sturgeon[/eluser]
Some advice that will solve every problem you ever have with running MySQL from PHP.

1.) Output the final query and see if it looks right.

Code:

ob_clean();
echo $this->db->last_query();
exit();

2.) If it looks fine, try running it in phpMyAdmin or another GUI.

3.) If it runs fine, your PHP is not running the query correctly (don't find this one happening so often).

4.) If it fails, you get an error message telling you what's wrong. Then, correct your query.

Debugging my friend, is a wonderful thing.

[eluser]franto[/eluser]
[quote author="Phil Sturgeon" date="1243619735"]Some advice that will solve every problem you ever have with running MySQL from PHP.

1.) Output the final query and see if it looks right.

Code:

ob_clean();
echo $this->db->last_query();
exit();

2.) If it looks fine, try running it in phpMyAdmin or another GUI.

3.) If it runs fine, your PHP is not running the query correctly (don't find this one happening so often).

4.) If it fails, you get an error message telling you what's wrong. Then, correct your query.

Debugging my friend, is a wonderful thing.[/quote]

Thanks for help, I'm newbie in Code Igniter, and didnt know how to write SQL statement from Active Record. I have to confess Im Senior Flex developer and I'm used to debug daily many hours but I'm not developing in PHP so much, this is just my sideproject

btw here is result:

UPDATE `wtu_messages` SET `offence_count` = 'offence_count + 5' WHERE `id` = '2'

problem is with difference quotes in SET part. It should be

UPDATE `wtu_messages` SET offence_count = offence_count + 5 WHERE `id` = '2'

But I don't know how to write it via Active record, so I will rather try to create SQL statement as a string. Or maybe you know how to fix it via Active Record?

[eluser]Phil Sturgeon[/eluser]
There we go, that'll be ActiveRecord trying to help again. Just do the following:

Code:

$this->db->set('company_count = company_count + 1');

Setting the 3rd param as false will stop XSS cleaning, but it will not stop the automatic quote marks for what it believes to be strings or values.

[eluser]franto[/eluser]
this made SQL statement even worse

UPDATE `wtu_messages` SET `offence_count` = offence_count + 1 = '' WHERE `id` = '10'

[eluser]franto[/eluser]
so, this works

$this->db->set('offence_count','offence_count + '.$value, false);

thanks for help again. I'm following you now on Twitter, I hope I will help you someday

[eluser]xwero[/eluser]
why not use the query method? then you have no problems with the single quotes.

[eluser]franto[/eluser]
cause I'm learning Active Record I know there I can create SQL as string, but just was curious if that can be done via Active Record

[eluser]Phil Sturgeon[/eluser]
Strange. The following works so I assumed that would:

Code:

$this->db->update('table', 'offence_count = offence_count + 1', array('id', 10));