Jump to content

Handle Unique rows error in PHP


Jeelani

Recommended Posts

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

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 by Jeelani
Link to comment
Share on other sites

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

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 by Jeelani
Link to comment
Share on other sites

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 by Jeelani
Link to comment
Share on other sites

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

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

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 by Jeelani
Link to comment
Share on other sites

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

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

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...