Update MySQL row containing retweet_count if it is retweeted

Question

I've been logging tweets into a MySQL database using the Twitter API and PHP using the following function:

 function saveTweets($screen_name) {
global $link;

$screen_name = dbEscape(strtolower(trim($screen_name)));
if (!$screen_name) { echo "<p><strong>Error: No screen name declared.</strong></p>\n"; return false; }

$row = dbGetRow("SELECT `id` FROM `twitter` WHERE `screen_name`='$screen_name' ORDER BY `id` DESC LIMIT 1");
$last_id = $row['id'];


$url = "http://api.twitter.com/1/statuses/user_timeline.xml?screen_name=$screen_name&count=1500&include_rts=true" ;
if ($last_id) { $url .= "&since_id=$last_id" ; }
$ch = curl_init($url);
curl_setopt ($ch, CURLOPT_RETURNTRANSFER, TRUE);
$xml = curl_exec ($ch);
curl_close ($ch);

$affected = 0;
$twelement = new SimpleXMLElement($xml);
foreach ($twelement->status as $status) {
    $text = dbEscape(trim($status->text));
    $time = strtotime($status->created_at);
    $id = $status->id;
    $retweet_count = $status->retweet_count;
 dbQuery("INSERT INTO `twitter`        (`id`,`screen_name`,`time`,`text`,`hidden`,`retweet_count`) VALUES     ('$id','$screen_name','$time','$text','n','$retweet_count') ON DUPLICATE KEY UPDATE $row[retweet_count] = VALUES('$retweet_count')");
    $affected = $affected + dbAffectedRows();
}

return "<p>".number_format($affected)." new tweets from $screen_name saved.</p>\n" ;
}

 echo saveTweets('Phil');
?>

This all works to log tweets, times and who posted, but while the retweet_count will log at the time of entry - so if I log it when the tweet is written that is likely to be 0 - I can't get

 ON DUPLICATE KEY UPDATE $row[retweet_count] = VALUES('$retweet_count')");

to function such that when I run the query at a later time it updates the row if the tweet has been retweeted.

The majority of entries for retweet_count are the same (0 or 1), so I can't set retweet_count to UNIQUE, though primary key - id - should be unique and I also have an auto incrementing column id - colid - though that is not actually pulled from the twitter api so it can be unique and is set to be UNIQUE.

Is there a better function to update a row that already contains an int? Thanks

score 1 · Answer 1 · answered May 08 '12 at 17:20

1

Your syntax is incorrect. It should just be:

ON DUPLICATE KEY UPDATE column_name = new_value, ...

That is:

ON DUPLICATE KEY UPDATE `retweet_count` = '$retweet_count'

Or, if you really want to use the VALUES() function (not especially useful in your case - it's really only useful when inserting multiple rows with a single INSERT statement):

ON DUPLICATE KEY UPDATE `retweet_count` = VALUES(`retweet_count`)

answered May 08 '12 at 17:20

eggyal

122,705
18
212
237

While I would agree it is likely my syntax, and thank you for replying so quickly, unfortunately those suggestions didn't update the database. The VALUES() function was just an example I'd found in another post on here, so not that fussed about using it. – Phil May 08 '12 at 19:44

Update MySQL row containing retweet_count if it is retweeted

1 Answers1