• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
SQL: SET column1+=value

Hi everyone,

I'm wondering if there is a way to do this:

$sql = "UPDATE Review
  SET numHelpful +=".$numHelpful.", numNotHelpful +=".$numNotHelpful."
  WHERE reviewID =".$reviewID;

Basically what is happening is that i'm creating a voting system. If user click 'isHelpful', then in DB the column value will be incremented by 1. Same goes for 'NotHelpful'.

I was wondering if you can straight away += into database, rather than get from DB, increment by 1, then update back in. But apparently the former method doesnt work.

I don't think you can do it this way. You have to update the latest value.

Another thing i thought of could be, just adding a record with "numHelpful" or "numNotHelpful". This way you dont have to get the latest value but instead you could just echo out the count of records. After the vote has expired, you could clear the table.

$this->db->insert('vote', 'numHelpful');
echo $this->vote_model->get('numHelpful');

You can't use "field += 1", but you can "field = ( field + 1 )" , so your query will be

$sql = "
    numHelpful = ( numHelpful  + ".$numHelpful." ),
    numNotHelpful = ( numNotHelpful + ".$numNotHelpful." )
    reviewID =".$reviewID;

the () are also optional, but in my opinion the query gets more readable.


Have you checked out the "set" methods in the database user guide [just below insert() and insert_batch()]? Pretty much exactly for doing what you are doing...


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  

  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2019 MyBB Group.