I'm trying to retrieve the id of one table A to insert into another table B. I cannot use last_insert_id() as i have not inserted anything into A. Any ideas on how to do this nicely?
$n = mysql_query("SELECT max(id) FROM tablename");
doesn't seem to work, nor does
$n = mysql_query("SELECT max(id) FROM tablename GROUP BY id");
This question is related to
mysql
lastinsertid
You could descendingly order the tabele by id and limit the number of results to one:
SELECT id FROM tablename ORDER BY id DESC LIMIT 1
BUT: ORDER BY
rearranges the entire table for this request. So if you have a lot of data and you need to repeat this operation several times, I would not recommend this solution.
I have different solution:
SELECT AUTO_INCREMENT - 1 as CurrentId FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbname' AND TABLE_NAME = 'tablename'
You can get maximum column value and increment it:
InnoDB uses the following algorithm to initialize the auto-increment counter for a table t that contains an AUTO_INCREMENT column named ai_col: After a server startup, for the first insert into a table t, InnoDB executes the equivalent of this statement:
SELECT MAX(ai_col) FROM t FOR UPDATE;
InnoDB increments by one the value retrieved by the statement and assigns it to the column and to the auto-increment counter for the table. If the table is empty, InnoDB uses the value 1.
Also you can use SHOW TABLE STATUS and its "Auto_increment" value.
I think to add timestamp to every record and get the latest. In this situation you can get any ids, pack rows and other ops.
Source: Stackoverflow.com