Jump to content
Larry Ullman's Book Forums

How To Insert A Field With Values From Two Other Fields In One Step


Recommended Posts

I have a membership table which contains the following fields (as example):

 

member_id (primary key, auto increment = 123)

lastname (= Smith)

firstname (= David)

publicname (= 123SD)

 

Note that the value of the field publicname is in fact the combination of the member_id and the first characters of the fields lastname and firstname, respectively.

 

I manage to insert the values of the first three fields to create a new record as the first step, then create the value for field publicname and insert it into the newly created record as the second step.

 

My question is, whether it is possible to insert a new record in just ONE sql query: two values for fields firstname and lastname are provided via form input while values for fields member_id and publicname are generated by a php script.

 

If it is possible, any hint how this could be achieved?

 

Thanks!

Link to comment
Share on other sites

The following allows you to get the auto-increment value of a DB table:

http://stackoverflow.com/questions/15821532/get-current-auto-increment-value-for-any-table

 

You could use that query as a subquery in your INSERT to set the publicname field.

With that said though, even though you're only executing one query by doing that, because the query has a subquery, you're still essentially executing two.

 

Unless you need to index and heavily search on the publicname field, I think a better idea is to dynamically generate the publicname value on SELECT, and never bother with it on INSERT.

You could either use MySQL's CONCAT function to create it on the DB side, or you could easily create the value on the PHP side. It's up to you.

Link to comment
Share on other sites

Thanks HartleySan.

 

I notice that (as from real test), the

SELECT `AUTO_INCREMENT`

code will get the *future* ID to be inserted if I subsequently execute an insert query. I assume there would be a chance my actual insert would generate a member_id that's different than the value being captured in the first query, IF there's another transaction that has been executed in between my two queries - is this possible (although extremely slim)?

 

Anyway, I found your second suggestion much easier for me, and with these advantages:

 

1) Everything is inserted in just one query (i.e., only the lastname and firstname values), and the "auto_increment ID" is guaranteed to be unique for that query.

 

2) The publicname value can be generated by SELECT whenever is needed, and can be in any pattern (good flexibility for future changes).

 

Thanks again!

Link to comment
Share on other sites

I'm glad you went with the second suggestion. I also agree that it's a better solution.

Also, you are right that (while extremely slim) a query could go between your other two queries and muck things up.

If you were to go that route, which I wouldn't recommend anyway, then you'd probably want to use a transaction to ensure the integrity of the data.

 

Anyway, glad you got what you wanted.

Link to comment
Share on other sites

 Share

×
×
  • Create New...