1

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.

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.