INSERTing data in TABLE A and ensuring there is a correspoding INSERTion in TABLE B

bluwulf

Ok here is my situation....:

I have some data to insert in a MySQL DB that I got from a web based interface (upon the user pressing a "SUBMIT" button)

I want the data to be inserted in TABLE A and there exist corresponding data that I would (well...must be) inserted in TABLE B such that for each record in TABLE A there one (and only one) corresponding reccord in TABLE B

Given the fact that there are several persons connecting to the web interface and thus may make "submissions" for insertions in the DB how do U suggest I go about implementing this so as to ensure that the data gets inserted in TABLE A and the corresponding data gets inserted in TABLE B....?

My plan wuz to insert the data in TABLE A (which as an AUTO_INCREMENT column) then using LAST_INSERT_ID() acertain the AUTO_INCREMENT "id" from TABLE A and insert the correspondig data in TABLE B and "manually" (thus avoiding the use of an AUTO_INCREMENT column...which I would much prefer to use) give it the "ID" returned from LAST_INSERT_ID() thus ensuring that each record in TABLE A corespond to one and only one record in TABLE B...what do U think.......(all this came up before I started reading about TRANSACTION....nevertheless I feel more "compitent" with my "method")

devinemke

insert the data is table A
use the [man]mysql_insert_id[/man] to grab the last ID
insert the data in table B using that same ID

what is the difference between A and B?

bluwulf

Originally posted by devinemke
1. insert the data is table A
2. use the [man]mysql_insert_id[/man] to grab the last ID
3. insert the data in table B using that same ID

what is the difference between A and B?

Why should I use [man]mysql_insert_id[/man] instead of LAST_INSERT_ID() ?

The difference is that there is some data that isn't by definition an attribute (in particular system date and time and update date and time) of TABLE A and thus I would much prefer to keep it in a different table (TABLE 😎 in TABLE A there is allowed to submit there own dates and time (by definition that date and time is an attribute of TABLE A)

I'm considering this in my design (I don't know if it will make a difference):

That I would have a table_A_ID and table_B_ID where table_B_ID is a foreign key that references TABLE A and there would B a one to one relationship between TABLE A and TABLE B.....in fact thats exactly how I would want to implement the design...What I'm trying to ensure is that (given the fact that several persons mayB inserting data in DB at a given time) for each insertion in TABLE A there must be a corresponding insertion in TABLE B

sanam

Well in case lot of people will be adding information in the database, in that case you should use "mysql_insert_id" not LAST_INSERT_ID. See the difference: (from mysql manual)

The value of mysql_insert_id() is affected only by statements issued within the current client connection. It is not affected by statements issued by other clients.

Also note that the value of the SQL LAST_INSERT_ID() function always contains the most recently generated AUTO_INCREMENT value, and is not reset between statements because the value of that function is maintained in the server.

bluwulf

Thanx for that info sanam

bluwulf

But sanam don't I have to worry about this ?

mysql_insert_id() converts the return type of the native MySQL C API function mysql_insert_id() to a type of long (named int in PHP). If your AUTO_INCREMENT column has a column type of BIGINT, the value returned by mysql_insert_id() will be incorrect. Instead, use the internal MySQL SQL function LAST_INSERT_ID() in an SQL query.

Or is there a work around for this ? I would want to use BIGINT given the fact that I don't have any idea how many records will eventually be in theze table !

Also according to the MySQL manual :

Returns the last automatically generated value that was inserted into an AUTO_INCREMENT column. mysql> SELECT LAST_INSERT_ID();
-> 195

The last ID that was generated is maintained in the server on a per-connection basis. This means the value the function returns to a given client is the most recent AUTO_INCREMENT value generated by that client.

So then sanam isn't it best to use LAST_INSERT_ID() ?