Create slug to include id and title when inserting record - php MySQL

Question

Possible duplicate:
Access the auto increment value during an INSERT INTO statement

I would like to create a pool for my urls when creating pages to store in the database. The pool should be title-id (e.g. titlename-234 ). I already have a function to remove the header, but how can I get the identifier before inserting the record?

Thank you very much in advance

Answer 1

You should create a trigger, something like this:

 CREATE TRIGGER trigger_name BEFORE INSERT ON table FOR EACH ROW SET slug = CONCAT(NEW.title, "-", NEW.id); 

I’m not sure that you will be able to access the identifier column before writing it in the database (unless, of course, you yourself create your identifiers, and do not use auto-increment).

If you use DB auto-increment (and you should be), try to create a trigger AFTER INSERT and update your line in the trigger. Thus, even though you are updating it AFTER your insertion, it will still work before you can run any other queries (e.g. SELECT).

HERE is trigger documentation.

---

I was wrong. Apparently, you cannot update the table you insert into (using the AFTER INSERT trigger), this will throw an exception. So, there are two possible ways to do this using only SQL:

Ugly way:

 DELIMITER $$ CREATE TRIGGER `create_slug` BEFORE INSERT ON `events` FOR EACH ROW BEGIN SET new.id = (SELECT MAX(id) FROM events) + 1; SET new.slug = CONCAT(new.menu_name, "-", new.id); END$$ DELIMITER ; 

It cancels your AUTOINCREMENT database. In fact, do not do this.

Or you can create a procedure:

 DELIMITER $$ CREATE PROCEDURE insert_event(MenuName VARCHAR(30), Content TEXT, PhotoName TEXT) BEGIN INSERT INTO events (menu_name, content, photo_name) VALUES (MenuName, Content, PhotoName); SET @id = LAST_INSERT_ID(); UPDATE events SET slug = CONCAT(menu_name, "-", @id) WHERE id = @id; END$$ DELIMITER ; 

And instead of calling (how you called):

 $query = mysql_query("INSERT INTO events (menu_name, content, photo_name) VALUES ('{$menu_name}', '{$content}', '{$photo_name}');", $connection); 

just call

 $result = mysql_query("CALL insert_event('{$menu_name}', '{$content}', '{$photo_name}');",$connection ); 

Again, I strongly recommend using mysql_query. It is outdated, terminated and unsafe. You should check mysqli or PDO.