Mysql – Delete duplicate with same ID PHP/PDO/MYSQL

duplicationMySQLPHP

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.

Best Answer

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.

Related Solutions

MySQL Update – Updating a Unique Column with Unknown Values

Your requirements:

The problem is that the user can enter as many values and in whatever order he likes. If he enters less than there are in the database all of them should be removed and replaced with the new ones

suggest that a reasonable option would be your idea:

DELETE all the rows with same FK,
then INSERT all the news ones,

both (DELETE and INSERT statements) in one transaction. I tried in SQLdiddle and it worked just fine:

CREATE TABLE Names
(
ID int NOT NULL PRIMARY KEY AUTO_INCREMENT,
ID_ITEM int,
Name varchar(255) UNIQUE
) ;

INSERT INTO Names
  (ID_ITEM, Name)
VALUES
  (1, 'John'),
  (1, 'Amy'),
  (1, 'Joe') ;


START TRANSACTION ;
    DELETE FROM Names
    WHERE ID_ITEM = 1 ;

    INSERT INTO Names
      (ID_ITEM, Name)
    VALUES
      (1, 'John'),
      (1, 'Joe') ;
COMMIT ;

Then:

SELECT * FROM Names ;

returns:

ID   ID_ITEM   Name
--------------------
4     1        John
5     1        Joe

MySQL – Entering Multiple Entries Where Count Increases by One

As I'd suggested on comments, this is a trigger in action BEFORE INSERT to autogenerate a thing_id with the UID given.

TRIGGER:

USE `test`;

DELIMITER $$

DROP TRIGGER IF EXISTS test.Things_BEFORE_INSERT$$
USE `test`$$
CREATE DEFINER = CURRENT_USER TRIGGER `test`.`Things_BEFORE_INSERT` BEFORE INSERT ON `Things` FOR EACH ROW
BEGIN
SET NEW.thing_id=IFNULL((SELECT MAX(thing_id)+1 FROM test.Things WHERE UID=NEW.UID),1); -- If NULL set it to 1 as first thing_id for this UID
END
$$
DELIMITER ;

Note: Change the database and table to yours.

Test:

mysql> SELECT * FROM test.Things;
Empty set (0.00 sec)

mysql> 
mysql> USE `test`;
Database changed
mysql> 
mysql> DELIMITER $$
mysql> 
mysql> DROP TRIGGER IF EXISTS test.Things_BEFORE_INSERT$$
Query OK, 0 rows affected (0.00 sec)

mysql> USE `test`$$
Database changed
mysql> CREATE DEFINER = CURRENT_USER TRIGGER `test`.`Things_BEFORE_INSERT` BEFORE INSERT ON `Things` FOR EACH ROW
    -> BEGIN
    -> SET NEW.thing_id=IFNULL((SELECT MAX(thing_id)+1 FROM test.Things WHERE UID=NEW.UID),1); -- If NULL set it to 1 as first thing_id for this UID
    -> END
    -> $$
Query OK, 0 rows affected (0.12 sec)

mysql> DELIMITER ;
mysql> 
mysql> SET @UID='71420150404',@thing1='First Thing.',@thing2='Second Thing.',@thing3='Third Thing.';
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> INSERT INTO test.Things(UID,thing) 
    -> VALUES (@UID,@thing1),(@UID,@thing2),(@UID,@thing3);
Query OK, 3 rows affected, 1 warning (0.09 sec)
Records: 3  Duplicates: 0  Warnings: 1

mysql> 
mysql> SELECT * FROM test.Things;
+-------------+----------+---------------+------------+
| UID         | thing_id | thing         | thing_date |
+-------------+----------+---------------+------------+
| 71420150404 |  0000001 | First Thing.  | NULL       |
| 71420150404 |  0000002 | Second Thing. | NULL       |
| 71420150404 |  0000003 | Third Thing.  | NULL       |
+-------------+----------+---------------+------------+
3 rows in set (0.00 sec)

mysql>