0

I am having a mysql table

content_votes_tmp

+------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+------------------+------+-----+---------+----------------+ | up | int(11) | NO | MUL | 0 | | | down | int(11) | NO | | 0 | | | ip | int(10) unsigned | NO | | NULL | | | content | int(11) | NO | | NULL | | | datetime | datetime | NO | | NULL | | | is_updated | tinyint(2) | NO | | 0 | | | record_num | int(11) | NO | PRI | NULL | auto_increment | +------------+------------------+------+-----+---------+----------------+

surfers can vote up or vote down on posts i.e. content, a record gets inserted everytime a vote is given same as rating , in the table along with other data like ip , content id

Now i am trying to create cronjob script in php which will SUM(up) and SUM(down) of votes

like this,

mysqli_query($con, "SELECT SUM(up) as up_count, SUM(down) as down_count, content FROM `content_votes_tmp` WHERE is_updated = 0 GROUP by content")

and then by using while loop in php i can update the main table for the specific content id,

but i would like to set the records which are part of SUM to be marked as updated i.e. SET is_updated = 1, so the same values wont get summed again and again.

How can i achieve this ? using mysql query ? and work on same data set as , every second/milisecond the records are getting inserted in the table ,.

i can think of another way of achieving this is by getting all the non-updated records and doing sum in the php and then updating every record.

Improve this question
4
  • Are you using InnoDB or MyISAM tables for your MySQL server? Commented Sep 16, 2014 at 17:38
  • @JoachimIsaksson using MyISAM .. Commented Sep 16, 2014 at 17:39
  • InnoDB can use transactions (MyISAM cannot). Just wanted to make sure whether it was an option or not. Commented Sep 16, 2014 at 17:49
  • @JoachimIsaksson is it possible if i change the table from mysiam to innodb ? Commented Sep 16, 2014 at 17:50

1 Answer 1

Reset to default
1

The simplest way would probably be a temporary table. Create one with the record_num values you want to select from;

CREATE TEMPORARY TABLE temp_table AS SELECT record_num FROM `content_votes_tmp` WHERE is_updated = 0;

Then do your calculation using the temp table;

SELECT SUM(up) as up_count, SUM(down) as down_count, content FROM `content_votes_tmp` WHERE record_num IN (SELECT record_num FROM temp_table) GROUP by content

Once you've received your result, you can set is_updated on the values you just calculated over;

UPDATE `content_votes_tmp` SET is_updated = 1 WHERE record_num IN (SELECT record_num FROM temp_table)

If you want to reuse the connection to do the same thing again, you'll need to drop the temporary table before creating it again, but if you just want to do it a single time in a page, it will disappear automatically when the database is disconnected at the end of the page.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

You sir are genius., TEMPORARY TABLE are new for me, but its the best way of working on fixed data set. thanks for the detailed answer.