Search the Community
Showing results for tags 'transactions'.
Found 3 results
-
Hi everyone, I have an application that makes use of a transaction, inserting and updating values in different tables. One of the first tables it inserts into has an auto increment ID field. This ID values is used / updated in some other tables. Everything works fine. If however the transaction fails then the auto_increment values are upset - meaning that the sequence is jumped. For ex. if I had auto_increment values from 1 to 6 in this table and then the insert query fails for an auto_increment value of ID=7, then this value of ID = 7 is lost because the next time the query is run the auto_increment ID value will be equal to 8 and accordingly updated in the tables. ID value = 7 is lost. Is there any way to prevent this ? Please know that this is a multi - user application. Thanks.
-
I'm currently studying IT and I'm a beginner with PHP and MySQL. Though I want to make a registration form for a school. The registration form consist of three parts (basic info, address info and courses info). The MySQL database will consist of four tables (users, addresses, users_has_courses and courses). The goal for this registration form is to record all inscriptions in MySQL and maybe into a Excel. My idea was to store all data into a session, when the registration is complete, the user can confirm and all the data should be read into the MySQL. This insertion would include 1 write to users, 1 write to addresses and multiple writes to users_has_courses. This form will be used on the registration day, so I expect around ten computers handling the registrations. What happens when some users press the button at the same time? If they are all asking for the next unused ID, they will all select the same ID and write to it? What if I plan to write to a MySQL and Excel after each other, will Excel get the same problems? Thank you!
-
Hi, I have a application (written in PHP with MySQL db on backend) where http request w/set variables are coming into a specific URL. Note: that URL then grabs those $_REQUEST variables and process them - matching to set records based on set variables in the url string. Ok, all this is working 100% - no issues. However, I'm seeing where duplicate entries are, occassionally logging the same value - when those two or more http requests hit that url at the same time. So, my question is, "what's the best way to ensure transactional logging where only one MySQL entry or select set of queries and inserts can be performed via the PHP script - one at a time?' Note: the statements are just basic "MySQLi SELECT, INSERTS, or UPDATES" - all that works great. The issue (again) is i need to ensure ONLY one transaction - or inbound request is processed - one at a time to avoid duplicate entries where a 'unique' integer is being incremented and assigned to a table (that's where the duplicate is found; just that column). I'm thinking making that block of code a transaction - for example: "Yes, this is the MySQL driver; i will update it to MySQLi to fast implementation so exclude those comments... I know about PDO but no time to refactor all the code - that's later project). Anyway, just want opinion if my thinking is correct and setting the batch of SQL statements in the transaction will then allow only one set of entries to the db tables - thus, eliminating the duplicate entries I'm seeing from two executions hitting at exact same time. <html> <head> <title>ShotDev.Com Tutorial</title> </head> <body> <? $objConnect = mysql_connect("localhost","root","root") or die(mysql_error()); $objDB = mysql_select_db("mydatabase"); //*** Start Transaction ***// mysql_query("BEGIN"); //*** Query 1 ***// $strSQL = "INSERT INTO customer "; $strSQL .="(CustomerID,Name,Email,CountryCode,Budget,Used) "; $strSQL .="VALUES "; $strSQL .="('".$_POST["txtCustomerID"]."','".$_POST["txtName"]."','".$_POST["txtEmail"]."' "; $strSQL .=",'".$_POST["txtCountryCode"]."','".$_POST["txtBudget"]."','".$_POST["txtUsed"]."') "; $objQuery1 = mysql_query($strSQL); $strSQL = "INSERT INTO customer "; $strSQL .="(CustomerID,Name,Email,CountryCode,Budget,Used) "; $strSQL .="VALUES "; $strSQL .="('".$_POST["txtCustomerID"]."','".$_POST["txtName"]."','".$_POST["txtEmail"]."' "; $strSQL .=",'".$_POST["txtCountryCode"]."','".$_POST["txtBudget"]."','".$_POST["txtUsed"]."') "; $objQuery2 = mysql_query($strSQL); if(($objQuery1) and ($objQuery2)) { //*** Commit Transaction ***// mysql_query("COMMIT"); echo "Save Done."; } else { //*** RollBack Transaction ***// mysql_query("ROLLBACK"); echo "Error Save [".$strSQL."]"; } mysql_close($objConnect); ?> </body> </html>
