php - Algorithm to track the price of a particular item every 5 minutes -

i have php script fetches price of particular item api. script executed cron job runs every 5 minutes. price of item remains same of time, , changes 4 or 5 times day.

this how have table structured of now:

create table if not exists `product_details` (   `id` int(11) not null auto_increment,   `start` datetime not null,   `end` datetime not null,   `price` decimal(11,2) not null,   primary key (`id`) ) engine=innodb  default charset=latin1 auto_increment=38 ; 

in front-end, i'd display prices during various periods. if price changed 3 times during day, there 3 time ranges, , corresponding prices.

example:

2015-05-31 01:55:00 pm 2015-05-31 04:25:00 pm   price = $22.33  2015-05-31 04:25:00 pm 2015-05-31 08:25:00 pm   price = $33.54  2015-05-31 08:25:00 pm 2015-05-31 10:25:00 pm   price = $37.37 

if insert new record every time script gets executed, there 200+ records in single day, , it'd messy quickly. execute insert query if fetched price different previous one.

this how script now:

$last_row = get_last_low(); $current_price = get_current_price();  if ($last_row['price'] == $current_price)  {     update product_details set `end`=%s order id desc limit 1; } else  {     insert product_details (`start`, `end`, `price`)      values (now(), now(), $current_price) }  foreach (get_all_records() $item)  {     display(); } 

if user specifies, i'd display increase/decrease in price next each entry. should perhaps add value_increased field in table? i'm totally clueless how structure table correctly, in best way possible, don't have later on.

how change it? (i posted approach show i've tried far; answer doesn't have stick it. i'm open ideas, long it's better hack.)

since have previous , new prices when insert, can calculate value_increased , store itself. yes, add value_increased field in table.

instead of updating 'end' every time query api, if keep track of last run time in code, update once before insert (have null on initial insert).

something like

$last_row = get_last_low(); $current_price = get_current_price();  if ($last_row['price'] != $current_price)  {     update product_details set `end`=%s 'end' null;      insert product_details (`start`, `end`, `price`)      values (now(), null, $current_price) } 

also, if displaying current price of items query becomes more simpler -

select * product_details 'end' null; 

instead of more complicated (with multiple products, you'd have latest 'end' , join again corresponding price. of course, if thing doing showing records, wouldn't of factor.

the single update means you'd hit db far fewer times (i.e. many times there price change, instead of many times hit api)

cons

the downside if application crashes, won't know when last got price (if tracking each , every price mandatory, you'd have query database on api side time after last price changed. example

1.00 pm    $1.1 1.15 pm    $2.0 1.30 pm    $2.0 1.45 pm    $2.0 --- application crash ---- 

with current program db have

1.00 pm     $1.1 1.45 pm     $2.0 

so, error recovery, you'd query api price changes after 1.45 pm. modified program db have

1.00 pm     $1.1 null        $2.0 

so, error recovery, you'd have query api price changes after 1.00 pm. note in both cases, number of records returned api same, it's api's rerun has (depends on when last price change was) larger dataset filter out.