Storing undetermined amounts of user information (MySQL)

Question

I've been looking into the best way of storing an undetermined amount of information submitted by a user. A friend of mine suggested using nested tables, however these don't appear to be a thing in MySQL.

The application will allow users to store pieces of information per day (each day is a blank slate so to speak)

What I have currently is

-Users
--ID
--email
--password

-Things
--UID (made from date and user ID)
--Thing1
--Thing2

This works fine. The UID is the users ID and the date combined (i.e 71420150404) as each day will be different but I'm open to changing this. The application checks to see if there are any entries for that UID and if there isn't, creates a new row.

The problem I have is I'd like the user to be able to select how many pieces of information they would like to add per day. So instead of the static 'Thing1, Thing2' the user could theoretically have this go up to 'Thing100', and I'm fairly sure adding these as columns isn't the best way to go about this.

I looked into if its possible to store an array in a cell and I'd access it like that through PHP but the research I came across all suggests I shouldn't do this. Creating a new table per user also seems very inefficient.

What is the best way to go about this?

EDIT - Including original PHP insertion code in response to an answer

Insert

$sql = "INSERT INTO things (uid, thing1, thing2) 
VALUES (:uid, :thing1,:thing2) 
ON DUPLICATE KEY UPDATE thing1 = :thing1, thing2 = :thing2"; 

Retrieve

$sql = "SELECT * FROM things WHERE uid=$uid";

Answer 1

You can use the same table with another PK(Primary Key) and just one thing field.

Example:

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

You can make a trigger in action BEFORE UPDATE so when you add a new row, the trigger make the job of increment the thing_id field, like SET NEW.thing_id=(SELECT MAX(thing_id)+1 FROM db1.Things WHERE UID=NEW.UID); and that's all.

With this you can add N things for the same UID.

EDIT:

I've added 2 field to your current design, thing_id and thing_date.

TEST:

mysql> # New Table structure
mysql> CREATE TABLE `test`.`Things` (
    ->   `UID` BIGINT NOT NULL,
    ->   `thing_id` INT(7) ZEROFILL UNSIGNED NOT NULL,
    ->   `thing` VARCHAR(255) NULL,
    ->   `thing_date` DATETIME DEFAULT NULL,
    ->   PRIMARY KEY (`UID`, `thing_id`));
Query OK, 0 rows affected (0.01 sec)

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

mysql> # First message
mysql> SET @UID="71420150404";
Query OK, 0 rows affected (0.00 sec)

mysql> SET @MSG="Hello!";
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO test.Things(UID,thing_id,thing,thing_date)
    -> SELECT
    -> @UID,
    ->     IFNULL(MAX(thing_id),0)+1,
    ->     @MSG,
    ->     NOW()
    -> FROM test.Things
    -> WHERE UID=@UID
    -> ON DUPLICATE KEY UPDATE thing=@MSG;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> # SECOND SELECT 
mysql> SELECT * FROM test.Things;
+-------------+----------+--------+---------------------+
| UID         | thing_id | thing  | thing_date          |
+-------------+----------+--------+---------------------+
| 71420150404 |  0000001 | Hello! | 2015-10-06 15:00:20 |
+-------------+----------+--------+---------------------+
1 row in set (0.00 sec)

mysql> # Second message
mysql> SET @UID="71420150404";
Query OK, 0 rows affected (0.00 sec)

mysql> SET @MSG="Hello2!";
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO test.Things(UID,thing_id,thing,thing_date)
    -> SELECT
    -> @UID,
    ->     MAX(IFNULL(thing_id,0))+1,
    ->     @MSG,
    ->     NOW()
    -> FROM test.Things
    -> WHERE UID=@UID
    -> ON DUPLICATE KEY UPDATE thing=@MSG;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> # Third SELECT 
mysql> SELECT * FROM test.Things;
+-------------+----------+---------+---------------------+
| UID         | thing_id | thing   | thing_date          |
+-------------+----------+---------+---------------------+
| 71420150404 |  0000001 | Hello!  | 2015-10-06 15:00:20 |
| 71420150404 |  0000002 | Hello2! | 2015-10-06 15:00:20 |
+-------------+----------+---------+---------------------+
2 rows in set (0.00 sec)

mysql> # Third message
mysql> SET @UID="71420150404";
Query OK, 0 rows affected (0.00 sec)

mysql> SET @MSG="Did you see there\'s 3 messages?!";
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO test.Things(UID,thing_id,thing,thing_date)
    -> SELECT
    -> @UID,
    ->     MAX(IFNULL(thing_id,0))+1,
    ->     @MSG,
    ->     NOW()
    -> FROM test.Things
    -> WHERE UID=@UID
    -> ON DUPLICATE KEY UPDATE thing=@MSG;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> # Last SELECT
mysql> SELECT * FROM test.Things;
+-------------+----------+----------------------------------+---------------------+
| UID         | thing_id | thing                            | thing_date          |
+-------------+----------+----------------------------------+---------------------+
| 71420150404 |  0000001 | Hello!                           | 2015-10-06 15:00:20 |
| 71420150404 |  0000002 | Hello2!                          | 2015-10-06 15:00:20 |
| 71420150404 |  0000003 | Did you see there\'s 3 messages?! | 2015-10-06 15:00:20 |
+-------------+----------+----------------------------------+---------------------+
3 rows in set (0.00 sec)

mysql> # fourth message
mysql> SET @UID="71420150404";
Query OK, 0 rows affected (0.00 sec)

mysql> SET @MSG="4th!";
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO test.Things(UID,thing_id,thing,thing_date)
    -> SELECT
    -> @UID,
    ->     MAX(IFNULL(thing_id,0))+1,
    ->     @MSG,
    ->     NOW()
    -> FROM test.Things
    -> WHERE UID=@UID
    -> ON DUPLICATE KEY UPDATE thing=@MSG;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM test.Things;
+-------------+----------+----------------------------------+---------------------+
| UID         | thing_id | thing                            | thing_date          |
+-------------+----------+----------------------------------+---------------------+
| 71420150404 |  0000001 | Hello!                           | 2015-10-06 15:00:20 |
| 71420150404 |  0000002 | Hello2!                          | 2015-10-06 15:00:20 |
| 71420150404 |  0000003 | Did you see there\'s 3 messages?! | 2015-10-06 15:00:20 |
| 71420150404 |  0000004 | 4th!                             | 2015-10-06 15:06:01 |
+-------------+----------+----------------------------------+---------------------+
4 rows in set (0.00 sec)

mysql> 

Every @ is a user defined variable, change them for your Php variables and everything work just the same.

YOUR QUERY:

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();