need help about database query builder

need help about database query builder - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: General Help (https://forum.codeigniter.com/forumdisplay.php?fid=24)
+--- Thread: need help about database query builder (/showthread.php?tid=68073)

need help about database query builder - ngangchill - 05-21-2017

e.g

table: points

userId | totalPoints

1 | 1000
2 | 500
3 | 2000
....................
...........

so ,
i have a 20 to 30 sets of raw data[userId/totalPoints] that i want to update points table.

condition:
1. if raw data [total points] is greater then original value that presents in database , then i need to update points table data with raw data .

2. if raw data [total points] is less then original value that presents in database , then i raw data (totalpoints) will be added with original value present in database.

so, how can i write database query for this problem??

Thanks for help.

RE: need help about database query builder - Rufnex - 05-21-2017

Try soemthing like that:

Code:
UPDATE  points

SET     totalPoints = IF(totalPoints < 2000, 2000, totalPoints )

RE: need help about database query builder - reactionstudio - 05-22-2017

If you are:

Using Active Record
Updating a specific user and know their user_id

Then you could try something like this:

Code:
$newTotalPoints = 123456;

$qry = $this->db->select('totalPoints')->get_where('points',array('user_id'=>$user_id));

if( $qry->num_rows() ) {

    $currentTotalPoints = $qry->row_array()['totalPoints'];

    if( $newTotalPoints > $currentTotalPoints ) {

        $this->db->where('user_id',$user_id)->update('points',array('totalPoints'=>$newTotalPoints));

    } else if( $newTotalPoints < $currentTotalPoints ) {

        $this->db->where('user_id',$user_id)->update('points',array('totalPoints'=>$newTotalPoints+$currentTotalPoints));

    }

}