MySQL – Entering Multiple Entries Where Count Increases by One

MySQLPHP

I do apologize for the uninspired title, I'm not sure how best to put it.

Another person helped me massively in another question I posted regarding the structuring of data.

The solution has left me with another bit of a brain ache. I will paste some of that users response to me as it reflects what I currently have, I do apologize if that is poor etiquette.

Basically I'm allowing users to store a certain amount of text information each day, for example, 7 entries daily. Entries referenced as things.

The current structure works as follows (UID is Users ID + Date):

Things
PK          PK
UID         thing_id  thing
71420150404 0000001   First thing
71420150404 0000002   Second thing

The user that helped me before came up with the following way to enter data:

INSERT INTO test.Things(UID,thing_id,thing,thing_date)
SELECT
    :uid,
    MAX(IFNULL(thing_id,0))+1,
    :thing1,
    NOW()
FROM test.Things
WHERE UID=:uid
ON DUPLICATE KEY UPDATE thing=:thing1,thing_date=NOW();

This works perfectly, however the problem is only one entry can be added at a time. A workaround I've found is to take all the entries (things) as an array in my PHP and execute the SQL statement over and over for each entry. This seems highly inefficient.

Though I have been getting the basics of SQL through online courses I haven't been able to work out how to a better solution to this through SQL itself.

Best Answer

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>
Related Question