jimfog Posted September 2, 2013 Share Posted September 2, 2013 So far when it comes error handling with queries in a function I follow the following logic: $result1 = $connection->query("insert into users values (NULL,'" .$name. "','" .$lastname . "','".$email."','". $passwd."','". $hash."','". $usertype."')"); if (!$result1) { throw new Exception('error'); } else{$result2=$connection->query("insert into business_users values ('".$connection->insert_id."','" .$address."','".$url ."','".$phone. "','".$city. "','".$municipality. "','".$buztype. "')"); } if(!$result2) { throw new Exception('error'); return false;} The problem with the above flow is that the first query might finish successfully while the second might not. The goal is that if one of them fails the other should not run at all...more specifically I do not want the the first query running if the second fails, something which the above code fails to do. Even if the 2nd insertion fails the 1st will be performed. I do not want partial entries in the DB...what control structure I should follow so that both insertions take place or none of them. Link to comment Share on other sites More sharing options...
birbal Posted September 3, 2013 Share Posted September 3, 2013 You need to look into "Transaction" Link to comment Share on other sites More sharing options...
jimfog Posted September 3, 2013 Author Share Posted September 3, 2013 You need to look into "Transaction" Aren't these only for financial stuff? Are there any serious performance penalties in using them? Link to comment Share on other sites More sharing options...
thescientist Posted September 3, 2013 Share Posted September 3, 2013 Aren't these only for financial stuff? Are there any serious performance penalties in using them? look it up first http://en.wikipedia.org/wiki/Database_transaction http://www.tutorialspoint.com/mysql/mysql-transactions.htm Link to comment Share on other sites More sharing options...
jimfog Posted September 4, 2013 Author Share Posted September 4, 2013 (edited) I got a glimpse on transactions. One question I have is if with transactions error checking code such as the one below have any sense any more: if (!$result1){ throw new Exception('Αδυναμία εκτέλεσης εντολής'); return false;} Is the above pointless now? I tried to use the mysqli->begin_transaction method but I got "Fatal error" Call to undefined method". Why that? I have PHP 5.4.4 on my system Edited September 4, 2013 by jimfog Link to comment Share on other sites More sharing options...
justsomeguy Posted September 4, 2013 Share Posted September 4, 2013 The mysqli class does not have a method called begin_transaction: http://www.php.net/manual/en/class.mysqli.php Link to comment Share on other sites More sharing options...
jimfog Posted September 4, 2013 Author Share Posted September 4, 2013 The mysqli class does not have a method called begin_transaction: http://www.php.net/manual/en/class.mysqli.php Τhen what is this? http://www.php.net/manual/en/mysqli.begin-transaction.php Link to comment Share on other sites More sharing options...
justsomeguy Posted September 4, 2013 Share Posted September 4, 2013 One thing it is, is not listed in the list of properties and methods under the class synopsis on the page I linked to. The other thing it is is a feature of PHP 5.5. You can use autocommit and commit to set up transactions. Link to comment Share on other sites More sharing options...
jimfog Posted September 4, 2013 Author Share Posted September 4, 2013 You can use autocommit and commit to set up transactions. I finally did the above...set autocommit(FALSE). Link to comment Share on other sites More sharing options...
jimfog Posted September 5, 2013 Author Share Posted September 5, 2013 I have one last q. After the commit, should I set autocommit(TRUE)? It is not stated clearly in the manual what to do after the transaction is finished. Link to comment Share on other sites More sharing options...
justsomeguy Posted September 5, 2013 Share Posted September 5, 2013 Depends what you want to do. You only need to disable autocommit if you are using transactions, so if you're not using transactions and don't want to call commit after every query then turn autocommit back on. Link to comment Share on other sites More sharing options...
jimfog Posted September 6, 2013 Author Share Posted September 6, 2013 Depends what you want to do. You only need to disable autocommit if you are using transactions, so if you're not using transactions and don't want to call commit after every query then turn autocommit back on. So, by calling commit does that mean also that autocommit is set to true? Here is a code example where I want to apply a transaction to these 2 queries only $connection->autocommit(FALSE);; $result1 = $connection->query("insert into users values (NULL,'" .$name. "','" .$lastname . "','".$email."','". $passwd."','". $hash."','4')"); $result2=$connection->query("insert into business_users values('".$connection->insert_id."','" .$address."','".$url ."','".$phone. "','".$city. "','".$municipality. "','".$buztype. "')"); $connection->commit(); Link to comment Share on other sites More sharing options...
justsomeguy Posted September 6, 2013 Share Posted September 6, 2013 The commit method does not change the autocommit setting, it simply commits what you have sent to the server if it hasn't already been committed. If you want things to get autocommitted then you need to change the autocommit setting. Link to comment Share on other sites More sharing options...
jimfog Posted September 6, 2013 Author Share Posted September 6, 2013 Anyway...I tried to experiment with a transaction but it does not work and I cannot understand why...here is the code: $connection->autocommit(FALSE); $result1 = $connection->query("insert into users values (NULL,'" .$name. "','" .$lastname . "','".$email."','". $passwd."','". $hash."','4')"); $result2=$connection->query("insert into business_users values('".$connection->insert_id."','" .$address."','".$url ."','".$phone. "','".$city. "','".$municipality. "','".$buztype. "')"); $connection->commit(); $connection->autocommit(TRUE); I messed the 2nd query on purpose. It does not executed....but according to transactions definition neither the first query should. The 1st query though does execute. So we have the problem now of partial data in the db. Any ideas what might be wrong? Link to comment Share on other sites More sharing options...
birbal Posted September 6, 2013 Share Posted September 6, 2013 you need to detect when does your queries are failing and according to that you need to rollback. check method for 'rollback' in whatever API you are using Link to comment Share on other sites More sharing options...
justsomeguy Posted September 6, 2013 Share Posted September 6, 2013 Note that you need to rollback before committing if there was a problem. Link to comment Share on other sites More sharing options...
jimfog Posted September 6, 2013 Author Share Posted September 6, 2013 Yes. rollback was the missing "link". Link to comment Share on other sites More sharing options...
jimfog Posted September 9, 2013 Author Share Posted September 9, 2013 (edited) This is the code I finally concluded: $connection->autocommit(FALSE); $result1 = $connection->query("insert into users values (NULL,'" .$name. "','" .$lastname . "','".$email."','". $passwd."','". $hash."','4')"); $connection->rollback(); $result2=$connection->query("insert into business_users values('".$connection->insedress."','".$url ."','".$phone. "','".$city. "','".$municipality. "','".$buztype. "')"); $connection->commit(); $connection->autocommit(TRUE); Τhe reason I am showing it is that I encountered an error where the data of the first query entered the db while of the second's(there was a foreign key violation) did not. Since I am using rollback this should not be the case. By seeing the code above is the use of the rollback a correct one? Furthermore...if a there was an error in the transaction(meaning the queries were not performed) what error checking mechanism should I use to be notified about it? Edited September 9, 2013 by jimfog Link to comment Share on other sites More sharing options...
justsomeguy Posted September 9, 2013 Share Posted September 9, 2013 You only use rollback if you actually want to roll the transaction back. You are sending the first query, and then immediately rolling it back (basically canceling it), then running the second query, and then committing that one. So the first query never takes effect because you always roll it back. You need to check for errors with the queries and roll back the entire transaction if any query had an error, that is what rollback is for. Rollback is like undo for queries that have not been committed yet. Link to comment Share on other sites More sharing options...
jimfog Posted September 9, 2013 Author Share Posted September 9, 2013 You only use rollback if you actually want to roll the transaction back. You are sending the first query, and then immediately rolling it back (basically canceling it), then running the second query, and then committing that one. So the first query never takes effect because you always roll it back. You need to check for errors with the queries and roll back the entire transaction if any query had an error, that is what rollback is for. Rollback is like undo for queries that have not been committed yet. Αm I going to check the for errors with the usual way? meaning...if(!$result){......rollback}. result is the variable where the query is stored. Link to comment Share on other sites More sharing options...
justsomeguy Posted September 9, 2013 Share Posted September 9, 2013 Yes, it will return false if there was an error with the query. Link to comment Share on other sites More sharing options...
jimfog Posted September 10, 2013 Author Share Posted September 10, 2013 Τhis is the code I have concluded(based on an example from the PHP manual).Tell me what you think. So far it works OK but I have not tested it for all scenarios yet: $all_query_ok=true; $connection->autocommit(FALSE); $result1 = $connection->query('update users set password ="NULL",hash="NULL",email="NULL",usertype="5" where users.email="'.$sessionmail.'"')?null:$all_query_ok=false; $result2= $connection->query('delete from favorites where (select users.user_ID from users where favorites.userID=users.user_ID)')?null:$all_query_ok=false; if($all_query_ok) {$connection->commit(); return $accoudel='1'; $connection->autocommit(TRUE); } else {$connection->rollback(); $connection->autocommit(TRUE); return false; } Link to comment Share on other sites More sharing options...
thescientist Posted September 10, 2013 Share Posted September 10, 2013 (edited) the query will already return false if there's an error, no need for an extra variable and the tertiaries. http://php.net/manual/en/pdo.query.php $connection->autocommit(FALSE);$result1 = $connection->query('update users set password ="NULL",hash="NULL",email="NULL",usertype="5" where users.email="'.$sessionmail.'"');$result2 = $connection->query('delete from favorites where (select users.user_ID from users where favorites.userID=users.user_ID)');if($result1 && $result2) { //it was successful}else{ //it wasn't successful} Also, any lines of code put after a return statement won't get executed, so you're if should have the return statement last. Edited September 10, 2013 by thescientist Link to comment Share on other sites More sharing options...
jimfog Posted September 10, 2013 Author Share Posted September 10, 2013 Ι am not using PDO, but MySqli. Link to comment Share on other sites More sharing options...
justsomeguy Posted September 10, 2013 Share Posted September 10, 2013 That doesn't matter, mysqli::query also returns false if a query fails, right? Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now