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

#1
[eluser]Volkof[/eluser]
Hi everyone,

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

Code:
$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.

#2
[eluser]bigbusty[/eluser]
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.


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

#3
[eluser]vitoco[/eluser]
You can't use "field += 1", but you can "field = ( field + 1 )" , so your query will be

Code:
$sql = "
UPDATE
    Review
SET
    numHelpful = ( numHelpful  + ".$numHelpful." ),
    numNotHelpful = ( numNotHelpful + ".$numNotHelpful." )
WHERE
    reviewID =".$reviewID;

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

Slds

#4
[eluser]CroNiX[/eluser]
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...

http://ellislab.com/codeigniter/user-gui...tml#insert


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


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