Finding new entries in a database ?

[Expert-World.co]

Hi, I am updated my database table each day with a few thousand rows of data.
The data relates to products and contains a unique product code

Each day sales information comes in about the product and the update may also contain new products.

Data is like this:
time_stamp: 1267354256
prod_id: DB78HAL
day_sales: 308.88

time_stamp: 1267354256
prod_id: J8K8HAL
day_sales: 343.77

time_stamp: 1267354256
prod_id: GEF8H34D
day_sales: 25.84

time_stamp: 1267354256
prod_id: JEELH55
day_sales: 557.81

Then the next day
time_stamp: 1267854251
prod_id: DB78HAL
day_sales: 257.22

time_stamp: 1267854251
prod_id: J8K8HAL
day_sales: 45.75

time_stamp: 1267854251
prod_id: PET58GY
day_sales: 587.83

time_stamp: 1267854251
prod_id: GEF8H34D
day_sales: 789.80

time_stamp: 1267854251
prod_id: JEELH55
day_sales: 507.85

So you see the third entry is a new one.

What would be the best way to find these new arrivals ?

[Kruptein]

you should pass them trough a function which defines weither a new product should be added or updated

[Phaelax]

I suppose you could search for the prod_id of any records already in the database as you input the new rows. If there isn't a previous record matching the prod_id then it must be new. You have any control over how data is added to the database?

[diafol]

What do you want to do? Search for new additions - based on timestamp or decide whether to add or update?

[Expert-World.co]

Hi,
Thanks for replying. :)

To answer your questions,
yes, I do have full control over the updating.

Whether or not the product is new or not, the rows are just Inserted into the table and given the timestamp fro that day.

Since there is only one update a day - all have the same timestamp for that day.

Each day, I want to display all the new ones, that is why I want to identify them.

Thanks

[diafol]

OK, I'm afraid I'm still a bit unsure as to what you actually need - too much Christmas cheer on my part I think. Hmm, if you want to display today's inserts, just have a timestamp range to check:

//get today's date in unix format (default)
$unixdate = date('Y-m-d'); 
//=======check for specific date request from a form - OPTIONAL=======//
if(isset($_POST['date_to_check'])){
 $rawdate = trim($_POST['date_to_check']);
 //assume date from form is in UK notation - dd/mm/yyyy - or change to your preferred format
 $d = substr($rawdate,0,2);
 $m = substr($rawdate,3,2);
 $y = substr($rawdate,6,4);
 if($checkdate($m, $d, $y))$unixdate = mysql_real_escape_string($rawdate);
}
//====================================================//

//check for unix timestamp from midnight to almost midnight on the specified date
$r = "SELECT * FROM table WHERE time_stamp BETWEEN UNIX_TIMESTAMP('$unixdate 00:00:00') AND UNIX_TIMESTAMP('$unixdate 23:59:59')";
while($d = mysql_fetch_array($r)){
  //do something with the looped data
}

[diafol]

OK, I'm afraid I'm still a bit unsure as to what you actually need - too much Christmas cheer on my part I think. Hmm, if you want to display today's inserts, just have a timestamp range to check:

//get today's date in unix format (default)
$unixdate = date('Y-m-d'); 
//=======check for specific date request from a form - OPTIONAL=======//
if(isset($_POST['date_to_check'])){
 $rawdate = trim($_POST['date_to_check']);
 //assume date from form is in UK notation - dd/mm/yyyy - or change to your preferred format
 $d = substr($rawdate,0,2);
 $m = substr($rawdate,3,2);
 $y = substr($rawdate,6,4);
 if($checkdate($m, $d, $y))$unixdate = mysql_real_escape_string($rawdate);
}
//====================================================//

//check for unix timestamp from midnight to almost midnight on the specified date
$r = "SELECT * FROM table WHERE time_stamp BETWEEN UNIX_TIMESTAMP('$unixdate 00:00:00') AND UNIX_TIMESTAMP('$unixdate 23:59:59')";
while($d = mysql_fetch_array($r)){
  //do something with the looped data
}