Forum Moderators: coopster
$auto_increment_id = mysql_insert_id();
That will hold the auto_increment value from your most recent insert, which you can then insert into other tables for indexing.
[us2.php.net...]
Can I ask another question though? I understand the "cheating" part of this, at least I understand exactly the usage you are describing and I often call them "sequence" fields. Basically they allow you to change the value so that it can be displayed in a certain order outside of any other logic except that a human being wants it sorted that way. Most often these "sequence" fields exist as another key column in the row. Something along the lines of...
+-------+--------+-----+----------+...where a
¦itemID ¦ iTypes ¦ Seq ¦ itemName ¦
+-------+--------+-----+----------+
¦100001 ¦ Fruits ¦ 100 ¦ Banana ¦
¦100002 ¦ Fruits ¦ 200 ¦ Apple ¦
¦100003 ¦ Fruits ¦ 300 ¦ Cherry ¦
¦100004 ¦ Veggie ¦ 200 ¦ Turnip ¦
¦100005 ¦ Veggie ¦ 300 ¦ Potato ¦
¦100006 ¦ Veggie ¦ 100 ¦ Carrot ¦
+-------+--------+-----+----------+
SELECT * FROM table WHERE iTypes = 'Fruits' ORDER BY itemName;would give us an alphabetical listing of the 'Fruits' items but the "maintainer-of-order" has the option of sorting it as they see fit, obviously enjoying bananas much more than apples -- and cherries even less, in this case.
It's fairly easy to resequence rows in this case because we deal with a particular subset of information, Fruits or Veggie for example. But if there were no secondary key and this table gets large, that is quite a bit of "resequencing" going on when the "maintainer-of-order" decides the rows are going to be different.
How are you approaching that, or am I way off target here?
You cannot write a row out and AUTO_INCREMENT more than one column at a time because MySQL allows only one AUTO_INCREMENT column per table. It must be indexed and it cannot have a DEFAULT value. This all happens when the table is created [dev.mysql.com]. That's simply a restriction on the feature itself.
So the next question might be, as it seems you are asking here, "can I apply that AUTO_INCREMENT column value to another column?" The answer is yes, of course, but not until after the AUTO_INCREMENT value has been set, which occurs after the row has been created with an INSERT statement. You see, that is when the AUTO_INCREMENT value gets updated. You can see this if you were to
SHOW TABLE STATUS LIKE 'table';and look at the
Auto_incrementcolumn that MySQL maintains for the table.
How then would you get the same value in the other column that we have been discussing here? Just like Salsa and ergophobe have directed, and exactly as you suspected, an additional query statement. What's nice is that MySQL has made it easy for you though with LAST_INSERT_ID() [dev.mysql.com], as mentioned by ergophobe. Here's a sample to show you how you can use it:
CREATE TABLE sequence (
sID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
sequence INTEGER UNSIGNED NOT NULL,
value CHAR(10),
PRIMARY KEY (sID)
);
--
INSERT INTO sequence (value) VALUES ('Value1');
UPDATE sequence SET sequence = LAST_INSERT_ID() WHERE sID = LAST_INSERT_ID();
INSERT INTO sequence (value) VALUES ('Value2');
UPDATE sequence SET sequence = LAST_INSERT_ID() WHERE sID = LAST_INSERT_ID();
INSERT INTO sequence (value) VALUES ('Value3');
UPDATE sequence SET sequence = LAST_INSERT_ID() WHERE sID = LAST_INSERT_ID();
INSERT INTO sequence (value) VALUES ('Value4');
UPDATE sequence SET sequence = LAST_INSERT_ID() WHERE sID = LAST_INSERT_ID();
INSERT INTO sequence (value) VALUES ('Value5');
UPDATE sequence SET sequence = LAST_INSERT_ID() WHERE sID = LAST_INSERT_ID();
--
SELECT * FROM sequence;
--
+-----+----------+--------+
¦ sID ¦ sequence ¦ value ¦
+-----+----------+--------+
¦ 1 ¦ 1 ¦ Value1 ¦
¦ 2 ¦ 2 ¦ Value2 ¦
¦ 3 ¦ 3 ¦ Value3 ¦
¦ 4 ¦ 4 ¦ Value4 ¦
¦ 5 ¦ 5 ¦ Value5 ¦
+-----+----------+--------+
