比较数据库的时间值以停止重复条目

Question

I am inserting values into a database.

If the time the values were created is less than 24 hours then I insert like this,

$stmt = $con->prepare('
    INSERT INTO taggedPlaces
    (id, created_time, place_id, city, country, latitude, longitude, state, street, zip, name)
    VALUES
    (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
');

foreach($graphObject['tagged_places']->data as $data) {
if (time() - strtotime($data->created_time) < 86400) {
      $stmt->execute(array(
       $data->id,
       $data->created_time,
       $data->place->id,
       $data->place->location->city,
       $data->place->location->country,
       $data->place->location->latitude,
       $data->place->location->longitude,
       $data->place->location->state,
       $data->place->location->street,
       $data->place->location->zip,
       $data->place->name
   ));
}
}

Everytime I return to the page it takes the same entries and continuously adds them to the database.

I would like to say something like

if $data->created_time ==  any created_time value in the DB then don't add this value, 

as well as currently I am doing 

 if (time() - strtotime($data->created_time) < 86400)

to make sure it is not older then 24 hours.

How can I add this condition?

Answer 1

Option 1: (Recommeded)

Make id the primary key for your table, taggedPlaces:

ALTER TABLE taggedPlaces ADD PRIMARY KEY(id)

Then change your insert statement to use INSERT IGNORE which will skip duplicate inserts.

Option 2:

Make created_time a unique field in taggedPlaces:

ALTER TABLE taggedPlaces ADD UNIQUE(created_time);

Then, again, use INSERT IGNORE to skip duplicates.

Option 3: (Not recommeded, but will work)

Prior to running your insert, perform another query to check if $data->created_time is already in the table:

$check = $con->prepare('
    SELECT id FROM taggedPlaces
    WHERE created_time = ?   
');

$check->execute(array($data->created_time));

if (count($check->fetchAll()) == 0) {
    // No duplicates found. Proceed...
}