Jeelani Posted January 6, 2015 Share Posted January 6, 2015 Hi guys, I have been trying to handle MySQL unique rows issue in PHP but unable to do this. Please help someone. Here is my condition. I have two columns in a table. 1. Device_Model 2. Department I have setup unique key on both the columns, I mean no one can insert the same duplicate row again in this. but when I am trying to catch an exception its not working while insert statement. When I am inserting different values then its ok but on duplicate values its just saying nothing. try{$stmt->execute();$errorinfo = $conn->errorInfo();print_r($errorinfo);if(isset($errorinfo[2])){$err = 2;throw new Exception("$err");} } catch(Exception $ex){ echo "Duplication Problem". $ex->getMessage(); } Link to comment Share on other sites More sharing options...
justsomeguy Posted January 6, 2015 Share Posted January 6, 2015 If it's not throwing the exception then the if statement is not matching. Check what the manual says about that:http://php.net/manual/en/pdo.errorinfo.php Link to comment Share on other sites More sharing options...
Jeelani Posted January 11, 2015 Author Share Posted January 11, 2015 (edited) try { $stmt->execute(); $errorInfo = $stmt->errorInfo(); echo "<br>". print_r($errorInfo); /* Here is showing some result on success only */ if($errorInfo[0] !== 00000) { $err = 2; throw new Exception("$err"); } } print_r($errorInfo); on success its returning the below output: Array ( [0] => 00000 [1] => [2] => ) but when I am inserting duplicate values its just saying nothing no error at all and the row is not inserted in db. Please let me know if I am making any mistake. Edited January 11, 2015 by Jeelani Link to comment Share on other sites More sharing options...
Ingolme Posted January 11, 2015 Share Posted January 11, 2015 I'd say that the error code is a string, not a number, which means that the exception will always be thrown because the !== operator also does a type check and it's checking for a number. Either use != instead of !==, or test for "00000" instead of 00000 Link to comment Share on other sites More sharing options...
Jeelani Posted January 11, 2015 Author Share Posted January 11, 2015 I tried this also. but not working if($errorInfo[0] != "00000") { $err = 2; throw new Exception("$err"); } Link to comment Share on other sites More sharing options...
Ingolme Posted January 11, 2015 Share Posted January 11, 2015 What is the program doing? You should use var_dump($errorInfo) to see what exactly you're getting. Link to comment Share on other sites More sharing options...
Jeelani Posted January 11, 2015 Author Share Posted January 11, 2015 (edited) I used var_dump also but same thing no error on duplication and on success also no error with var_dump. Do I have to make any changes in PHP to get the error displayed? Using PHP 5.4.16 $stmt->execute(); $errorInfo = $stmt->errorInfo(); echo "<br>". var_dump($errorInfo); if($errorInfo[0] != "00000") { $err = 2; throw new Exception("$err"); } Edited January 11, 2015 by Jeelani Link to comment Share on other sites More sharing options...
Ingolme Posted January 11, 2015 Share Posted January 11, 2015 By no error what do you mean? Do you mean that the error code is 00000? When it doesn't work, what is being displayed by var_dump()? Link to comment Share on other sites More sharing options...
Jeelani Posted January 11, 2015 Author Share Posted January 11, 2015 (edited) There are two cases in my code. 1. If I use print_r($errorInfo); then on successful inserting row it return Array ( [0] => 00000 [1] => [2] => ) but on unsuccess doesn't return anything. 2. If I use var_dump($errorInfo); then on execution it returns nothing whether success or not. Edited January 11, 2015 by Jeelani Link to comment Share on other sites More sharing options...
Ingolme Posted January 11, 2015 Share Posted January 11, 2015 var_dump() actually doesn't return a value, it outputs the data without the need for an echo statement, so remove the echo part. The reason we're using var_dump() at the moment is to determine what the problem is. var_dump() gives a lot of information about what's contained in a variable, it never outputs nothing. Link to comment Share on other sites More sharing options...
Jeelani Posted January 11, 2015 Author Share Posted January 11, 2015 What I just noticed here is that when I execute the below code, if I insert a unique row then $errorInfo = $stmt->errorInfo(); and returns "1" also the below code is executed but if I insert a duplicate row then $errorInfo = $stmt->errorInfo(); is not executed and the below are escaped automatically even echo "<br>This is error no.". print_r($errorInfo); is also not executed. $stmt->execute(); $errorInfo = $stmt->errorInfo(); echo "<br>This is error no.". print_r($errorInfo); if($errorInfo[0]!="00000") { $err = 2; throw new Exception("$err"); } Link to comment Share on other sites More sharing options...
Ingolme Posted January 11, 2015 Share Posted January 11, 2015 If you're still using a try-catch block I suggest removing it for now. Link to comment Share on other sites More sharing options...
Jeelani Posted January 11, 2015 Author Share Posted January 11, 2015 Ya I just tried with var_dump($errorInfo); on unique row it returns array (size=3)0 => string '00000' (length=5)1 => null2 => null but on duplicate row it returns nothing. Link to comment Share on other sites More sharing options...
Ingolme Posted January 11, 2015 Share Posted January 11, 2015 Can you show all the code on your page? Wrap your code in [code] tags Link to comment Share on other sites More sharing options...
Jeelani Posted January 11, 2015 Author Share Posted January 11, 2015 (edited) Here it is: [/color] [color=#b22222;]<?php[/color] [color=#b22222;]if (isset($_POST['submit']))[/color] [color=#b22222;]{[/color] [color=#b22222;]$m = trim($_POST['PModel']);[/color][color=#b22222;]$n = trim($_POST['Dept_user']);[/color] [color=#b22222;]if(!empty($m) && !empty($n))[/color] [color=#b22222;]{[/color] [color=#b22222;]$pmod = cleartextentry($_POST['PModel']);[/color] [color=#b22222;]$dept_usr = cleartextentry($_POST['Dept_user']);[/color] [color=#b22222;]$vend = cleartextentry($_POST['vendor']);[/color] [color=#b22222;]$rmks = cleartextentry($_POST['remark']);[/color] [color=#b22222;]$PPDate=dateset($_POST['PPurDate']);[/color] [color=#b22222;]$PWEnd = dateset($_POST['Wend']);[/color] [color=#b22222;]$url = checkurl($_POST['url']);[/color] [color=#b22222;]$url = clearurl($_POST['url']);[/color] [color=#b22222;]$stmt = $conn->prepare("INSERT INTO printers(Printer_Model, Department_User, Printer_PurDate,Warranty_EndDate, Vendor, URL, Remarks)VALUES(:pmod, :dept_user, :ppdate, :warend, :vndr, :url, :rmks)");[/color][color=#b22222;]$stmt->bindParam(':pmod', $pmod);[/color][color=#b22222;]$stmt->bindParam(':dept_user', $dept_usr);[/color][color=#b22222;]$stmt->bindParam(':ppdate', $PPDate);[/color][color=#b22222;]$stmt->bindParam(':warend', $PWEnd);[/color][color=#b22222;]$stmt->bindParam(':vndr', $vend);[/color][color=#b22222;]$stmt->bindParam(':url', $url);[/color][color=#b22222;]$stmt->bindParam(':rmks', $rmks);[/color] [color=#b22222;]try[/color] [color=#b22222;]{[/color] [color=#b22222;]if(($PPDate!=NULL)&&($PWEnd!=NULL))[/color][color=#b22222;]{[/color][color=#b22222;]$DateErr = dateverify($PPDate, $PWEnd);[/color][color=#b22222;]if($DateErr=="DateError")[/color][color=#b22222;]{[/color][color=#b22222;]$err=1; [/color][color=#b22222;]throw new Exception("$err");[/color][color=#b22222;]}[/color][color=#b22222;]}[/color] [color=#b22222;]$stmt->execute();[/color][color=#b22222;]$errorInfo = $stmt->errorInfo();[/color] [color=#b22222;]var_dump($errorInfo);[/color] [color=#b22222;]if($errorInfo[0]!="00000")[/color][color=#b22222;]{[/color][color=#b22222;]$err = 2;[/color][color=#b22222;]throw new Exception("$err");[/color][color=#b22222;]}[/color] [color=#b22222;]//echo "<br><br><br>Last Inserted ID: " . $conn->lastInsertId();[/color] [color=#b22222;]}[/color] [color=#b22222;]catch(Exception $ex)[/color] [color=#b22222;]{[/color] [color=#b22222;]switch($ex->getMessage())[/color] [color=#b22222;]{[/color] [color=#b22222;]case 1:[/color] [color=#b22222;]echo "Printer Warranty End Date must be atleast 6 months. 'The record is not inserted' ";[/color] [color=#b22222;]break;[/color][color=#b22222;]case 2:[/color] [color=#b22222;]echo "Duplication Problem";[/color] [color=#b22222;]break;[/color] [color=#b22222;]}[/color] [color=#b22222;]}[/color] [color=#b22222;]$conn = null;[/color] [color=#b22222;]}[/color] [color=#b22222;]else[/color] [color=#b22222;]{[/color] [color=#b22222;]echo "<br>The values are empty";[/color] [color=#b22222;]}[/color] [color=#b22222;]}[/color] [color=#b22222;]if(isset($_POST['reset']))[/color] [color=#b22222;]{[/color] [color=#b22222;]header('Location:[/color][color=#b22222;]http://localhost/cartridges/pages/red.php[/color][color=#b22222;]');[/color] [color=#b22222;]}[/color] [color=#b22222;]?>[/color] Edited January 11, 2015 by Jeelani Link to comment Share on other sites More sharing options...
Ingolme Posted January 11, 2015 Share Posted January 11, 2015 Like I said earlier, test the code outside of the try-catch block. Link to comment Share on other sites More sharing options...
Jeelani Posted January 11, 2015 Author Share Posted January 11, 2015 Ok. I will check let u know now. Link to comment Share on other sites More sharing options...
Jeelani Posted January 11, 2015 Author Share Posted January 11, 2015 Here is the code out of try catch block. When I am inserting unique row I am getting the below. array (size=3) 0 => string '00000' (length=5)1 => null2 => null But on duplicate values I am getting ( ! ) Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'Abc-Abc' for key 'Printer_Model'' in C:wampwwwcartridgespagesinsertform.php on line 80 ( ! ) PDOException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'Abc-Abc' for key 'Printer_Model' in C:wampwwwcartridgespagesinsertform.php on line 80Call Stack#TimeMemoryFunctionLocation10.0000155920{main}( )..insertform.php:020.0000175328execute ( )..insertform.php:80 [/color][color=#008000;]<?php[/color] [color=#008000;]if (isset($_POST['submit']))[/color] [color=#008000;]{[/color] [color=#008000;]$m = trim($_POST['PModel']);[/color][color=#008000;]$n = trim($_POST['Dept_user']);[/color] [color=#008000;]if(!empty($m) && !empty($n))[/color][color=#008000;]{[/color][color=#008000;]$pmod = cleartextentry($_POST['PModel']);[/color][color=#008000;]$dept_usr = cleartextentry($_POST['Dept_user']);[/color][color=#008000;]$vend = cleartextentry($_POST['vendor']);[/color][color=#008000;]$rmks = cleartextentry($_POST['remark']);[/color][color=#008000;]$PPDate=dateset($_POST['PPurDate']);[/color][color=#008000;]$PWEnd = dateset($_POST['Wend']);[/color][color=#008000;]$url = checkurl($_POST['url']);[/color][color=#008000;]$url = clearurl($_POST['url']);[/color][color=#008000;]$stmt = $conn->prepare("INSERT INTO printers(Printer_Model, Department_User, Printer_PurDate,Warranty_EndDate, Vendor, URL, Remarks)VALUES(:pmod, :dept_user, :ppdate, :warend, :vndr, :url, :rmks)");[/color][color=#008000;]$stmt->bindParam(':pmod', $pmod);[/color][color=#008000;]$stmt->bindParam(':dept_user', $dept_usr);[/color][color=#008000;]$stmt->bindParam(':ppdate', $PPDate);[/color][color=#008000;]$stmt->bindParam(':warend', $PWEnd);[/color][color=#008000;]$stmt->bindParam(':vndr', $vend);[/color][color=#008000;]$stmt->bindParam(':url', $url);[/color][color=#008000;]$stmt->bindParam(':rmks', $rmks);[/color] [color=#008000;]$stmt->execute();[/color][color=#008000;]$errorInfo = $stmt->errorInfo();[/color] [color=#008000;]var_dump($errorInfo);[/color] [color=#008000;]$conn = null;[/color] [color=#008000;]}[/color] [color=#008000;]else[/color] [color=#008000;]{[/color] [color=#008000;]echo "<br>The values are empty";[/color] [color=#008000;]}[/color] [color=#008000;]}[/color] [color=#008000;]if(isset($_POST['reset']))[/color] [color=#008000;]{[/color] [color=#008000;]header('Location:http://localhost/cartridges/pages/red.php');[/color] [color=#008000;]}[/color] [color=#008000;]?>[/color] Link to comment Share on other sites More sharing options...
Jeelani Posted January 11, 2015 Author Share Posted January 11, 2015 So can you please suggest me how to handle this error for duplication. Link to comment Share on other sites More sharing options...
Ingolme Posted January 11, 2015 Share Posted January 11, 2015 You shouldn't need to use try and catch for your own error handling. Just set the var $err without throwing exceptions and use your switch() like usual. Save the try-catch for real exceptiond thrown by PHP classes, like this particular case that you're having right now. // At the beginning of your document$err = 0; // Initialize the error code variable//// . . .//// Checking for exceptions thrown by PDO.try { $stmt->execute();} catch (PDOException $e) { $errorInfo = $stmt->errorInfo(); if($errorInfo[0] == '23000') { // 23000 is the error code for duplicate key $err = 2; } else if($errorInfo != '00000') { // There's some other error that's not a duplicate key $err = 3; }}//// . . .//// Later on:if($err) { // Any number but 0 evaluates to true switch($err) { // All your error codes in here }} Link to comment Share on other sites More sharing options...
Jeelani Posted January 11, 2015 Author Share Posted January 11, 2015 Thanks alot. Now its working perfectly. 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