:: INSERT query in multiple tables

[phprock]

Adding record into multiple tables

I got 3 tables. project(project_id), project_stations(station_id,project_id) and assets(asset_id,asseet_project);
I got 3 quries for each of this tale.
INSERT INT TO project - SQL1
get project id (mysql_insert_id)
insert into project_station (SQL2)
insert into asset. ($SQL3)

I got one form. When user submit it insert record into 3 tables mentioned above. Which works fine. The problem is if project_staion SQl2 failed, the system will anyway execute SQL1. If the user hits submit button again, SQL1 will .. so there are duplicate entries.

Is there anyway, we can combine all queries into one ? so I can return true or false.

At the momenty I'm doing this way

$error=0;
run SQL1
if(!SQL1 SUCCESS){
$error=1;
}

run SQL2
if(!SQL2 SUCCESS AND empty($LAST_ID)){
$error=1;
}

run SQL3
if(!SQL3)){
$error=1;
}

if(empty($error)){
return true;
}

[bradgrafelman]

Which DBMS are you using?

The basic concept would be to start a transaction, perform the queries, and then commit the transaction after the final query was successfully executed. Otherwise, rollback the transaction so that none of the changes are actually saved.

EDIT: For example, here is the relevant page in the MySQL manual.