Not sure I understood the task, but here is some food for thought for an alternative schema:
CREATE TABLE Departament(
DepartamentID varchar(25) not null,
Floor varchar(25) not null,
CONSTRAINT pk_dept PRIMARY KEY (DepartamentID, Floor),
) ENGINE=InnoDB;
CREATE TABLE Employee(
ID varchar(25) not null,
DepartamentID varchar(25),
Floor varchar(25),
CONSTRAINT pk_ID PRIMARY KEY (ID),
CONSTRAINT fk_dept FOREIGN KEY (DepartamentID, Floor)
REFERENCES Departament (DepartamentID, Floor)
) ENGINE=InnoDB;
CREATE TABLE Attendant (
ID varchar(25) not null,
DepartamentID varchar(25) not null,
Floor varchar(25) not null,
CONSTRAINT pk_attendent PRIMARY KEY (ID),
CONSTRAINT ak_attendent UNIQUE (DepartamentID, Floor),
CONSTRAINT fk_... REFERENCES Employee ...,
CONSTRAINT fk_... REFERENCES Department ...,
) ENGINE=InnoDB;
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();
Best Answer
You cannot or should not manipulate files within a store procedure (which is basically the same as an event, but with a schedule) due to scope and security reasons.
MySQL events and stored procedures are supposed to only modify or read data using SQL, and not go outside of the database server responsibilities. If MySQL server could read or execute arbitrary files, that would be considered a vulnerability and would be patched (with the exception of
LOAD DATA
or very few other very specific commands). While certain plugins allow accessing or executing arbitrary data, that is considered an exception, and not the normal way of using MySQL.Normally PHP applications access MySQL as a resource, not the other way round. You may want to setup a UNIX cron or Window task scheduler at your OS to do what you are trying to do (executing PHP code) instead, rather than using the database.