Increment field of mysql database using codeigniter's active record syntax

Understanding query fundamentals

Gaining a deeper understanding of the core components forming this update query helps hone your fluency in CodeIgniter's Active Record. Let's dissect:

The set method

• Enables direct field manipulation without extra SQL commands.
• The 'field = field+1', FALSE statement leverages SQL arithmetic, while FALSE prevents expression quoting.

The where method

• It's your SQL WHERE clause; selects which record(s) need updating.

The update method

• This is your "use the force!", applying the changes.

To round-off, make sure every user input is safe and sterilized before being part of your SQL phenomena.

Advanced increment use cases

The art of incrementing extends beyond the basics. Here's your tour across different arenas:

Condition-based increments

For rating systems or reward mechanisms, a condition-based bonus:

/* I'm feeling generous, let's give 'em bonus! */
$this->db->set('score', 'CASE WHEN bonus_available THEN score + 10 ELSE score + 1 END', FALSE)->where('player', 'PlayerTwo')->update('scoreboard');


Multi-updates

Combine an increment with other field updates. Neat for data refreshing:

/* Multi-tasking like a pro! */
$this->db->set(array(
    'score' => 'score+2',
    'last_updated' => 'NOW()'
), '', FALSE)->where('player', 'PlayerTwo')->update('scoreboard');


Joins magnified

Where multiple tables are involved, a join in action:

/* Everyone wants a piece of PlayerTwo's success! */
$this->db->set('b.score', 'b.score+1', FALSE);
$this->db->join('bonus_table b', 'a.bonus_id = b.id', 'left');
$this->db->where('a.player', 'PlayerTwo');
$this->db->update('scoreboard a');


Pro tips

Importantly, strategy goes beyond syntax:

• Prior to bulk operations, back up data.
• Understand the locking behavior in your database.
• Use transactions for data-sensitive increments.
• Monitoring performance impacts can save you from server sweating.