n1concepts Posted March 22, 2013 Share Posted March 22, 2013 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> Quote Link to comment Share on other sites More sharing options...
DavidAM Posted March 22, 2013 Share Posted March 22, 2013 What?? I'm not sure I understand what you are saying is happening or what you want to happen. Especially since your code is inserting the same data twice; that is definitely going to give you duplicates. Give us a clearer explanation of what the script should be doing, and what it is actually doing. Then show us some real code. We may be able to help. And, by the way, you need to sanitize those user inputs: mysql_real_escape_string for the text, validate that the numerics are valid numbers (floats/ints) Quote Link to comment Share on other sites More sharing options...
Solution n1concepts Posted March 22, 2013 Author Solution Share Posted March 22, 2013 Sure - here's an example of the code (that previous example was something taken from the net at http://www.shotdev.com/php/php-mysql/php-mysql-and-transaction-begin-commit-rollback - <grin> ---- As for the actual code, guess I'm stuck there b/c I can't posted it do to legal reasons - NDA. I think i see what needs to happen and and I will provide an update within afew hours giving an actual example - code. thx for response (appreciated) and yes, seeing the code will help explain the issue & my objective.... Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.