Get current auto_increment value

I am doing mysql insert for my table using php. Autoincrement column name is "link_id" and "alias" colum is used to make SEO friendly url.

During my insert, I would like to attach link_id value at the end of my alias column.

So I need to know what is the being inserted link_id value.

I do not want to do another query.

mysql_insert_id() does not work, since its using previous query, and not current query.

Thanks

From stackoverflow

• you could use mysql_insert_id() ++, though that won't always be reliable.

  what would be better would be to insert the query, then append the current id using CONCAT()

  longneck : mysql_insert_id() ++ will not work because mysql_insert_id() is connection specific. it's a safe assumption that only one insert occurs to this table.

• I think this - MySQL: Get next auto increment value can help.

  You should use SHOW TABLE STATUS:

  $query = mysql_query(SHOW TABLE STATUS LIKE tablename); 
  $row = mysql_fetch_array($query); 
  $next_id = $row[‘Auto_increment’] ;
  

  It will give you the current status of auto_increment column.

  EDITED:

  In one query you can do it like this:

  INSERT INTO table_schema.table_name(column_name) 
  VALUES ((SELECT AUTO_INCREMENT 
           FROM INFORMATION_SCHEMA.TABLES 
           WHERE TABLE_SCHEMA = 'table_schema' 
              AND TABLE_NAME = 'table_name'))
  

  It will give you the new auto_increment value in the column column_name.

  melfar : Your assumption on $next_id is prone to race conditions, as pointed out by Dereleased.

• Have you considered a trigger in MySQL? Something like:

  DELIMITER //
  CREATE TRIGGER `add_alias_id` AFTER INSERT ON `someTable`
      FOR EACH ROW BEGIN
          UPDATE 
              `someTable` 
          SET 
              `alias` = CONCAT(`alias`,NEW.`link_id`)
          WHERE
              `link_id` = NEW.`link_id`;
      END;    
  //
  DELIMITER ;
  

  EDIT: I was recently working for a company whose flagship software used to assume that max(id) + 1 = next id. The problem is concurrency; it's rare, but two people can get the same id, causing all sorts of chaos. Be extremely careful trying to predict the value.

• I handle this type of scenario on the SELECT end.

  For Example: insert into tablename(aliasroot) values('index.php?link_id=');

  This would give you for example

  In my select I would do 'select concat(aliasroot,link_id) as alias from tablename'

  melfar : That's the best way IMO. Although I would do concatenation on the PHP end, keep mysql a little less busy.

  Kwebble : I prefer this solution because you don't duplicate data and keep formatting for presentation usage out of the database.