Merging and Adding data with SQLite

Question

I am writing a PHP script that runs on a cron and pulls JSON data from an API [ title (text), path (text), visitors (integer) ] and stores it in a SQLite database. Every time it runs, if it sees an existing title, it should add the new visitors count to the existing visitors. If not, it should add the new data as a new row.

Here's a simplified look at my loop:

foreach($results as $printresults) {

//this iterates though $title, $path and $visitors

$existing_visitors = $db->query("SELECT SUM(visitors) FROM topten WHERE
title='$title'");
while ($existing_vis_row = $existing_visitors->fetch(SQLITE_NUM)) {


$dupe_enter = $db->query("UPDATE topten SET title='$title', path='$path',
 visitors='$existing_vis_row[0]' WHERE title='$title' ");
    }

$db->query("INSERT INTO topten (id,title,path,visitors,time) VALUES 
(NULL, '$title', '$path', '$visitors', '$time');");

}

Then I'll do a SELECT to pull DISTINCT rows ordered by visitors and write this to a file. Since the UPDATE query adds all the visitors to these rows, it doesn't matter that there will be all the dupes. On a certain timeout, I'll drop the whole table and start collecting again, so the file doesn't get too unwieldy.

The problem is that it is adding the summed visitor counts on every pass of the loop making the visitor counts totally out of whack. But I couldn't find a better way to simply add the data together every time the script was run.

Answer 1

avoid dupes. set a unique key and use INSERT OR REPLACE ... instead of doing it yourself.

something like CREATE UNIQUE INDEX 'title_path' ON topten (title, path). this will make impossible to have two records with the same title and path fields. so, if you just do a blind INSERT ...., you'd get a conflict error if it would be a dupe.

so, just use INSERT OR REPLACE ...., this would first check any unique index and if there's already a record, it would be erased, then it would do the insert. of course it's all atomic (so other process checking won't see the record disappear and reappear).

Answer 2

pseudo-code:

 for($json_records as $rec){
     $row = SELECT visitors FROM topten WHERE title = $rec['title']
     if($row) 
         //record exists, add visitors and update
         $sum_visitors = $row['visitors'] + $rec['visitors']
         UPDATE topten SET visitors = $sum_visitors WHERE title = $rec['title'] 
     else 
         //record doesn't exist, insert new
         INSERT topten (title, visitors) VALUES ($rec['title'], $rec['visitors'])
 }

Maybe?