MySQL - insert rows in one table and then update another with the auto increment ID

Question

I have 2 tables called applications and filters. The structure of the tables are as follows:

mysql> DESCRIBE applications;
+-----------+---------------------+------+-----+---------+----------------+
| Field     | Type                | Null | Key | Default | Extra          |
+-----------+---------------------+------+-----+---------+----------------+
| id        | tinyint(3) unsigned | NO   | PRI | NULL    | auto_increment |
| name      | varchar(255)        | NO   |     | NULL    |                |
| filter_id | int(3)              | NO   |     | NULL    |                |
+-----------+---------------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

mysql> DESCRIBE filters;
+----------+----------------------+------+-----+---------+----------------+
| Field    | Type                 | Null | Key | Default | Extra          |
+----------+----------------------+------+-----+---------+----------------+
| id       | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| name     | varchar(100)         | NO   |     | NULL    |                |
| label    | varchar(255)         | NO   |     | NULL    |                |
| link     | varchar(255)         | NO   |     | NULL    |                |
| anchor   | varchar(100)         | NO   |     | NULL    |                |
| group_id | tinyint(3) unsigned  | NO   | MUL | NULL    |                |
| comment  | varchar(255)         | NO   |     | NULL    |                |
+----------+----------------------+------+-----+---------+----------------+
7 rows in set (0.02 sec)

What I want to do is select all the records in applications and make a corresponding record in filters (so that filters.name is the same as applications.name). When the record is inserted in filters I want to get the primary key (filters.id) of the newly inserted record - which is an auto increment field - and update applications.filter_id with it. I should clarify that applications.filter_id is a field I've created for this purpose and contains no data at the moment.

I am a PHP developer and have written a script which can do this, but want to know if it's possible with a pure MySQL solution. In pseudo-code the way my script works is as follows:

1. Select all the records in applications
2. Do a foreach loop on (1)
3. Insert a record in filters (filters.name == applications.name)
4. Store the inserted ID (filters.id) to a variable and then update applications.filter_id with the variable's data.

I'm unaware of how to do the looping (2) and storing the auto increment ID (4) in MySQL.

I have read about Get the new record primary key ID from mysql insert query? so am aware of LAST_INSERT_ID() but not sure how to reference this in some kind of "loop" which goes through each of the applications records.

Please can someone advise if this is possible?

Answer 1

I don't think this is possible to do this with only one request to mysql.

But, i think this is a good use case for mysql triggers.

I think you should write it like this :

CREATE TRIGGER after_insert_create_application_filter AFTER INSERT
ON applications FOR EACH ROW
BEGIN
    INSERT INTO filters (name) VALUES (NEW.name);
    UPDATE applications SET filter_id = LAST_INSERT_ID() WHERE id = NEW.id;
END

This trigger is not tested but you should understand the way to write it.

If you don't know mysql triggers, you can read this part of the documentation.

Answer 2

This isn't an answer to your question, more a comment on your database design.

First of all, if the name field needs to contain the same information, they should be the same type and size (varchar(255))

Overall though, I think the schema you're using for your tables is wrong. Your description says that each record in applications can only hold one filter_id. If that is the case, there's no point in using two separate tables.

If there is a chance that there will be a many-to-one relationship, link the records via the relevant primary key. If multiple records in application can relate to a single filter, store filters.id in the applications table. If there are multiple filters for a single application, store applications.id in the filters table.

If there is a many-to-many relationship, create another table to store it:

CREATE TABLE `application_filters_mappings` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `application_id` int(10) unsigned NOT NULL,
  `filters_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`)
);