Any MySQL gurus out there?

[PhraSEOlogy]

Here's what I want to do but I can't find any reference to it in the docs - maybe it cant be done.

I have a table that has a column that auto_increments.

Whenever I insert a row I want to be able to use the auto_increment value to populate another column in that row.

e.g. lets say we insert a row (rownum) '001' and as it is inserted I want to be able to use the auto_increment value to create a value in another column '001.html'.

Is there some MySQL variable that can be used to say insert into table x (htmlval) value (auto_increment value concatenated with .html).

The only way I can see to do this is to populate the table and then sweep the table again reading the rownum value and updating the other column with the value.

I just wondered if anyone had any ideas of how it could be done as it is inserted.

[txbakers]

I do a similar thing, but with two passes.

[roblaw]

PhraSEOlogy,

Are you looping over a list for your initial insert? From what I know, there probably isn't a method to pull the number for use in the same insert statement. However, if you are working within a loop (or query), you should be able to call the ID of the last record (Look at docs on 'mysql_insert_id()') and then do a new insert with that value. It should allow you to keep things as 1 step (although not 1 insert query).

Hope that it helps a little. If someone knows different, I would love to hear it.

roblaw

PS: You probably want to post something on a site like Macromedia. There are some real DB studs there that are pretty quick to respond.

[PhraSEOlogy]

Thanks for the response guys.

I have decided to do it in two passes - insert - then update with the auto_inc value.

I dont want to make the code any more complex than I have to - Keep it simple - right?

The problem with doing really neat stuff (read overly complicated) is that months down the road you look at the code and say - whoo thats a neat bit of coding I wonder what it does...

Ah! bring back the good old days of BATCH jobs JCL and SDSF. I wonder if there is a COBOL to perl compiler?

[Just Guessing]

Can you do it with MAX(auto_increment_column) or MAX(auto_increment_column)+1?

Never tried it myself!

[txbakers]

I think that would work also, but then you're getting into aggregate functions in your select, which must be handled with a "group by" clause, making it more complicated.

But it could work.....

[TymeTSpare]

Here is something I've used in the past:

<code>
DBConnection.Open()

MySQL = "Insert Into Table1 (FirstName) values ('" & frmFirstname.Text &"'')"
Dim objCmd As New OdbcCommand(MySQL, DBConnection)
'Insert the variables into table one
objCmd.ExecuteNonQuery()
'Get last inserted Id from Table1 to update Table2.
Dim Id As String = ""
objCmd.CommandText= "SELECT LAST_INSERT_ID()"
If Not objCmd.ExecuteScalar() Is Nothing Then
Id = objCmd.ExecuteScalar().ToString()
End If

If Id.Length > 0 Then
objCmd.CommandText= "INSERT INTO Table2 VALUES ('" + Id + "')"
objCmd.ExecuteNonQuery()
DBConnection.Close()
</code>

Without knowing what programming language your using, there might be something there to use. The Sql statements would be the same.
;)

[coopster]

I wouldn't attempt the MAX aggregate function, just because you have no guarantee that 150 others aren't hitting the database table with the same operation at the same time. To the best of my knowledge, txbakers direction is best, two passes. It comes down to the internal handling of an AUTO_INCREMENT column in MySQL. There is some discussion in this thread regarding primary key and another field the same.... how to? [webmasterworld.com]

[PhraSEOlogy]

Wow,

Plenty of feedback on this question - much appreciated. Looking through the information provided in this thread it looks as though the auto_increment value gets updated at the time of insert - so I dont think I can do what I want to.

However, coopster's link shows it can be done semi-automatically using LAST_INSERT_ID() which will give me the value I need to update the row immediately after it is inserted.

TY.

[txbakers]

but there may be several "last Insert ID" entries between the one you entered and the one you think you are getting.

The safest way is with two passes, keyed on something more than just the auto_increment. I also check for userID to make sure I'm getting the auto_increment for that user.

[jollymcfats]

LAST_INSERT_ID() is a per-connection function and is not affected by other clients. You'll get the id you expect.

[coopster]

Guess I should have clarified -- by two passes I meant two query statements, an INSERT and an UPDATE (again, see the example in the link provided earlier).

jollymcfats is correct regarding the per-connection statement. Here are a couple of other threads that covered the topic...

How does mysql_insert_id work? [webmasterworld.com]
A race to the finish. [webmasterworld.com]

[PhraSEOlogy]

I will have complete control over the insert/update activity so I can safely use the insert followed by the update. I am using the database to store (thumbnail) images from different locations. The TIE between the location and the image is the row ID. So when I insert a row (using auto_increment) I want to be able to TIE the ID + location + image together.

Alternatively, I could have generated my own sequence number for each row - but then I would have to track the current sequence (max) value etc.