Multiple Inserts in MySQL

[teynon]

I'm nitpicking right now... I want to process a form and insert data into a database in two separate tables (users and addresses). Now, normally I do it like this:

 

if (insert user query) {
    if (insert address query) {
    }
}

 

The problem is, this has a small margin for error, as if one succeeds, then the other fails, now I have a partial insert. Is there a way in MySQL to process both inserts at the same time dependent on each other? (IE if user and address insert both succeed only.)

 

You might say why are you separating the address and the user? I am blending all addresses across multiple types rather than having duplicate fields in a database.

User:

ID	int(11)			No	None	auto_increment	 	 	 	 	 	 	
PID	int(11)			No	None		 	 	 	 	 	 	
EMAIL	varchar(255)	latin1_swedish_ci		No	None		 	 	 	 	 	 	 
PASSWORD	varchar(100)	latin1_swedish_ci		No	None		 	 	 	 	 	 	 
FIRST	varchar(50)	latin1_swedish_ci		No	None		 	 	 	 	 	 	 
LAST	varchar(50)	latin1_swedish_ci		No	None		 	 	 	 	 	 	 
DOB	varchar(	latin1_swedish_ci		No	None		 	 	 	 	 	 	 
STATUS	int(2)			No	None		 	 	 	 	 	 	
TOKEN	varchar(50)	latin1_swedish_ci		No	None		 	 	 	 	 	 	 
IP	varchar(50)	latin1_swedish_ci		No	None		 	 	 	 	 	 	 
VERIFICATION	varchar(50)	latin1_swedish_ci		No	None		 	 	 	 	 	 	 
TS	timestamp		on update CURRENT_TIMESTAMP	No	CURRENT_TIMESTAMP	on update CURRENT_TIMESTAMP

Address:

ID	int(11)			No	None	auto_increment	 	 	 	 	 	 	
TID	int(3)			No	None		 	 	 	 	 	 	
PID	int(11)			No	None		 	 	 	 	 	 	
ADDR1	varchar(255)	latin1_swedish_ci		No	None		 	 	 	 	 	 	 
ADDR2	varchar(255)	latin1_swedish_ci		No	None		 	 	 	 	 	 	 
CITY	varchar(255)	latin1_swedish_ci		No	None		 	 	 	 	 	 	 
STATE	varchar(2)	latin1_swedish_ci		No	None		 	 	 	 	 	 	 
ZIP	varchar(5)	latin1_swedish_ci		No	None		 	 	 	 	 	 	 
TS	timestamp		on update CURRENT_TIMESTAMP	No	CURRENT_TIMESTAMP	on update CURRENT_TIMESTAMP

 

The inserts look like this:

 

INSERT INTO user (`EMAIL`, `PASSWORD`, `FIRST`, `LAST`, `DOB`, `STATUS`, `VERIFICATION`) VALUES ('{$_POST['Email']}', '{$pass}', '{$_POST['First']}', '{$_POST['Last']}', '{$dob}', '0', '{$code}')

INSERT INTO address (TID, PID, ADDR1, ADDR2, CITY, STATE, ZIP) VALUES ('1', '{$id}', '{$_POST['Street1']}', '{$_POST['Street2']}', '{$_POST['City']}', '{$_POST['state']}', '{$_POST['zip']}')

 

I might note before someone calls me on it, that the $_POST variables have been pre validated. Don't worry. I know.

[ebmigue]

Is there a way in MySQL to process both inserts at the same time dependent on each other? (IE if user and address insert both succeed only.)

 

Yes there is. Google for "php mysql transactions".

 

The conventional approach to your problem would be:

 

try{
   //begin transaction command.
    if (insert user query) {
         if (insert address query) {
        }
    }
   //commit transaction command.
}
catch(Exception $e){
   //rollback transaction command.
}

 

Hope it helps.

[teynon]

I was hoping for more of a blended insert statement so as to send both at literally the same time. Using try catch would still leave the possibility of a disconnect issue.

[ebmigue]

I was hoping for more of a blended insert statement so as to send both at literally the same time. Using try catch would still leave the possibility of a disconnect issue.

 

You mean like multiple variable assignment? Such thing is not support by SQL, AFAIK.

 

The work around is using transactions. There is very little chance that there would be a disconnect during script execution.

 

Or, you could use a stored procedure. The stored procedure will involve the two (or multiple inserts), but still you will be using transactions.

 

At least, if you use a stored procedure, there is only one DBMS-call in your php script instead of two (or more). But passing the parameters

to the stored procedure might be a nightmare.

 

Hope it helps.

[fenway]

The work around is using transactions. There is very little chance that there would be a disconnect during script execution.

Disconnect can happen at any time -- none is more likely than any other.

 

Besides, what's the problem with a partial insert in this case?

 

 

[ebmigue]

Disconnect can happen at any time -- none is more likely than any other.

True or not, this is an empirical matter, w/c involves a lot of variables, and no amount of experience can prove the truth of the case.

 

Besides, what's the problem with a partial insert in this case?

 

I don't even know what "partial inserts" mean. Please clarify.

[fenway]

By partial I meant the first statement and not the second.

[teynon]

The problem is that now I have user information, but not address information. I'd like to prevent that. (Like I said, the post was OCD.)

[ebmigue]

@OP

 

Did you try what I suggested?

Using stored procedures, and transactions? Or using try catch and transactions?