3

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";
2

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();
  • Thank you. Where the problem lies with that is currently UID is unique and the PHP checks if the UID exists (ON DUPLICATE KEY UPDATE...), if so, then update those rows, otherwise create new UID. Does the trigger method account for this or will I have to change things around? – jblz Oct 6 '15 at 18:36
  • It is the same, can you add your SQL code in php in your question? – oNare Oct 6 '15 at 18:38
  • 2
    oNare thank you very much, that works perfectly. I'm not sure how I'd iterate through each thing from the form (thing1, thing2, thing3) etc without calling the SQL over and over again for each thing however I'll try and figure it out. You've helped massively here. Thank you – jblz Oct 12 '15 at 23:39
  • You can make only one insert for the 3 things, if you want i can edit my post and I'll add it. – oNare Oct 13 '15 at 0:48
  • That'd be appreciated oNare, if it wouldn't be too much trouble I'd be very grateful to see it. – jblz Oct 13 '15 at 18:05

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.