How do I see the results of unique records added after adding records to mysql

Question

I'm a PHP noob.

I have a database that I will update weekly with a CSV. So far I've managed to upload the file to the server, open the file with PHP, and insert the data in my table.

Now I want to return the unique records that were added (on screen or in a file). How do I do this?

Answer 1

What makes them unique? We need a little more information about your schema.

Anyway, you can easily add a date_added column that defaults to CURRENT_TIMESTAMP. Then you could run a query that sorts the date_added descending to find out what was added.

With regard to only pulling unique results.. well, you shouldn't be re-inserting a unique row, so if you correctly avoid doing that, then the date_added will only reflect truly new entries.

Here is a code sample:

First, add the date_added column to your table:

ALTER TABLE your_table ADD COLUMN date_added TIMESTAMP DEFAULT CURRENT_TIMESTAMP

The DEFAULT CURRENT_TIMESTAMP will take care of using the current date automatically, so you do not have to change anything about your existing INSERT code.

With this column in place, you can now easily check which records were added for a specific date:

-- Rows for March 29, 2010
SELECT DISTINCT * FROM your_table WHERE DATE(`date_added`) = '2010-03-29';

The DISTINCT will make sure you only see unique rows that were added on that date.

Here's another handy query, which will pull "today's records":

SELECT DISTINCT * FROM your_table WHERE DATE(`date_added`) = CURRENT_DATE;

The only thing you need to remember is that existing records will have a date of 0, since the column did not exist at the time of their inserts.

Answer 2

If you're inserting the new rows with a timestamp, you can do something like this

// Insert CSV into the DB with this timestamp
$todays_insert_time = time();

...

// Get unique rows from today
$unique_rows = mysql_query("SELECT DISTINCT `whatever_column_makes_the_row_unique` FROM `some_table` WHERE `insert_time`=" . $todays_insert_time);