how to run mysql queries in succession

[phplearner2008]

Dear all,

I am trying to insert data to two tables in mysql, first in temp table and then in profile table through php.It works fine but sometimes I get a "maximum execution time of 30 seconds exceeded error". When I checked the database I found that data had been inserted into temp table but error had occurred before data was inserted into profile table.

I want both the mysql queries to run in succession before another user tries to insert data. I also want a kind of rollback that is if data is inserted into temp and error occurs before inserting to profile, I want data to be deleted from temp table too. My code is as follows. Any help would be appreciated.

 

<?php

session_start();

set_time_limit(120);

include('C:\Program Files\Apache Group\Apache2\htdocs\matrimonial\config.php');

$NAME = $_SESSION['NAME'];

$AGE  = $_SESSION['AGE'];

$DOB_MONTH = $_SESSION['DOB_MONTH'];

$DOB_DATE  = $_SESSION['DOB_DATE'];

$DOB_YEAR  = $_SESSION['DOB_YEAR'];

$GENDER = $_SESSION['GENDER'];

 

mysql_select_db($dbname,$con);

 

$sql = "INSERT INTO temp(NAME,AGE,DOB_MONTH,DOB_DATE,DOB_YEAR,GENDER) VALUES('$NAME','$AGE','$DOB_MONTH','$DOB_DATE','$DOB_YEAR',

'$GENDER')";

if(!mysql_query($sql,$con))

{

die("Query: " . $sql . "\n\n" . mysql_error());

header('Location: http://localhost/matrimonial/Templates/RegistrationFailed.htm');

 

}

else

{

$q = "INSERT INTO profile(NAME,AGE,DOB_MONTH,DOB_DATE,DOB_YEAR,GENDER) VALUES('$NAME','$AGE','$DOB_MONTH','$DOB_DATE','$DOB_YEAR',

'$GENDER')";

$qresult = mysql_query($q) or die("Query: " . $q . "\n\n" . mysql_error());;

if($qresult)

{

$query = "SELECT * FROM profile ORDER BY MATRIMONIAL_ID DESC LIMIT 1";

$queryresult = mysql_query($query) or die("Query: " . $query . "\n\n" . mysql_error());

$row = mysql_fetch_array($queryresult);

$MATRIMONIAL_ID = $row['MATRIMONIAL_ID'];

$MYLOGIN = $row['MATRIMONIAL_ID'];

$NAME = $row['NAME'];

$QUESTION = $row['QUESTION'];

$ANSWER = $row['ANSWER'];

$EMAIL = $row['EMAIL'];

 

$sql1 = "INSERT INTO profile_contact(MATRIMONIAL_ID,CONTACT_ADDRESS,COUNTRY_CODE,AREA_CODE,CONTACT_PHONE,CONTACT_MOBILE)

VALUES('$MATRIMONIAL_ID','$CONTACT_ADDRESS','$COUNTRY_CODE','$AREA_CODE','$CONTACT_PHONE','$CONTACT_MOBILE')";

$result = mysql_query($sql1) or die("Query: " . $sql1 . "\n\n" . mysql_error());

if($result)

{

$_SESSION['MATRIMONIAL_ID'] = $MATRIMONIAL_ID;

$_SESSION['MYLOGIN'] = $MYLOGIN;

$_SESSION['NAME'] = $NAME;

$_SESSION['QUESTION'] = $QUESTION;

$_SESSION['ANSWER'] = $ANSWER;

$_SESSION['EMAIL'] = $EMAIL;

header('Location: http://localhost/matrimonial/Templates/RegistrationSuccessful.php');

}

else

{

 

header('Location: http://localhost/matrimonial/Templates/RegistrationFailed.php');

}

} else

header('Location: http://localhost/matrimonial/Templates/RegistrationFailed.php');

}

mysql_close($con);

?>

 

[trq]

Why exactly are you making two tables of identical data?

[ratcateme]

also if you first query fails you have

  die("Query: " . $sql . "\n\n" . mysql_error());

which stops the script dead so you header redirect is never reached

and a similar situation with your second query where you say

$qresult = mysql_query($q) or die("Query: " . $q . "\n\n" . mysql_error());;
if($qresult)

there is no point in the if. if the query failed the script would end. so if the if is reached it is always going to be true

also try using [ code ] [/ code ] (with out the spaces) around your code it makes it easier to read

 

Scott.

 

 

[phplearner2008]

The die statement which you are referring to is for my debugging purpose(to know the type of error caused) I will remove it when everything works fine. Please ignore it.

 

I plan to ignore temp table, but still I have to insert data to profile table and profile_contact table. How can I make sure that both the queries take place in succession and another user does not insert data between these two queries.

[Philip]

How can I make sure that both the queries take place in succession and another user does not insert data between these two queries.

Take a look at mysql's lock tables

[uniflare]

I think to try keep things simple, why is it important that data is not inserted between those queries?

 

Give us an example situation where this would be a problem, maybe there is a better method.

 

lock tables, you will have to lock, then unlock straight away, thats 4 queries in total, unless those 2 queries are seconds apart, it wont make a difference.

[RichardRotterdam]

How can I make sure that both the queries take place in succession and another user does not insert data between these two queries.

Take a look at mysql's lock tables

 

That or transactions

[haku]

If you are inserting the same data into two tables, your database structure is flawed. Why don't you explain what you are trying to do so that someone can help you put together a better DB structure.