Delete duplicate with same ID PHP/PDO/MYSQL

Question

I'm new to databases and I need your help I'm working on a website and I have few problems. I'm using PDO. There is a user table, with unique ID, name and password. User enters his username and password and is put to the next page where he enters time, date and a third parameter. Those 3 parameters are put into anoter database with user's ID (ID is set with PHP's session). This table has no primary key, since user can insert multiple entries. Sounds great, but... I want to delete duplicates, or even better prevent user to insert a duplicate. I tried with this:

$alter = "ALTER IGNORE TABLE data ADD UNIQUE INDEX (ring_time, ring_date, bell_mode)";
$query = $db->prepare($alter);
$query->execute();

try {                           
    $insert_s = "INSERT INTO data(id, ring_time, ring_date, bell_mode) VALUES(?, ?, ?, ?)";

    $query = $db->prepare($insert_s);

    $query->execute(array($_SESSION['id'], $_POST['ring_time'], $_POST['ring_date'], $_POST['mode']));
} catch(PDOException $e) {
    echo "<p class='already_entered'>Already entered.</p>"
}

This works somehow right, but if different user inserts same values, it catches the error, even though his ID is different. I'm pretty sure that $alter statement is wrong. The other thing is: I want to delete every entry which date (the table's date entry is 'date' type) is past today's day. I tried with:

DELETE FROM data WHERE CURDATE() > ring_date";

but with no success, it deletes stuff, but not the right way.

Answer 1

It seems like just the session id would be 'unique' for defining the User? And maybe a separate table would hold the multiple bell_mode values for each user? You have to think through the application to figure out what is "unique"; we cannot do it for you.

CURDATE() is a date, not a datetime. NOW() is a datetime.

CURDATE() < ring_date -- before midnight this morning

It is clumsy to have two separate columns for a "date" and a "time". I strongly suggest you combine those two columns into a DATETIME datatype; I'll call it ring_dt. DATE(ring_dt) and TIME(ring_dt) are the 'date' and 'time' parts of ring_dt. It's easier to take a DATETIME apart than to put it together.

CURDATE() < ring_dt -- same as above
ring_dt > NOW() - INTERVAL 1 DAY  -- more than 24 hours ago (to the second)
ring_dt > CURDATE() - INTERVAL 1 DAY  -- before yesterday's 12am. (not very useful)

- INTERVAL 1 DAY and - INTERVAL 24 HOUR have the same effect.