CODEWITHSUNDEEP
mysqlsqldatabase
user2129623
user2129623

Reputation: 2257

Updating table data in optimal manner

I am getting twitter data by my query at every 3 hours. One of the field url is key here. For each url I have retweet_count value.

Sometimes in next 3 hour I get similar url records as previous. But retweet_count comes with updated new value.

In each query I am gettong about >200 urls.

One way is during each insert I check whether that url exist or not. If not then insert directly. If already exist then fetch previous rt_count value and compare with newer, if different then update it.

Gradually my data will grow to lacks of record in table. Where above case will take too much delay.

Is there any better way to do this? any optimistic solution for this?

$insertQuery2 = "INSERT INTO frrole_article_sentiment (`url`, `sentiment`, `title` , `time` , `img_url` , `rt_count` , `tweet_count`, `today`, `youtube_url`, `hash`) VALUES ('".$url."','".$sentiment."','".$title."','".$time."','".$img_url."','".$rt_count."','".$tweet_count."','".$today."', '".$is_youtube."', '".$hash."')";

                    if (!mysqli_query($con,$insertQuery2))

                    {

                        //die('Error: ' . mysqli_error($con));

                    }

Upvotes: 0

Views: 54

Answers (1)

Marco Bonzanini
Marco Bonzanini

Reputation: 766

Since you are using MySQL, you can use the ON DUPLICATE KEY UPDATE feature: http://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html

Your query will become something like:

INSERT INTO frrole_article_sentiment (url, rt_count)
VALUES('$url', '$rt_count')
ON DUPLICATE KEY UPDATE rt_count=$rt_count;

("url" assumed to be PK, query shortened for simplicity, you should include all the fields)

Upvotes: 1

Related Questions