?
Solved

Error handling in PHP

Posted on 2016-09-19
5
Medium Priority
?
145 Views
Last Modified: 2016-09-26
I am pretty new to PHP and hence I am sorry if my query is basic. I want some assistance on error handling.

The following PHP code (partial only) inserts data to MySQL table. I have done some reading and found that error 1062 & 1586 & 1859 relate to the Message: Duplicate entry '%s' for key %d. But even after putting this in my code, I am unable to get the code to display the error message I gave below instead of displaying Message: Duplicate entry '%s' for key %d

It always only displays Message: Duplicate entry '%s' for key %d though I have put the error handling in.

What am I doing wrong?

<html>
<body>
    <?php
    $con = mysql_connect("localhost","xx","xx");

    if (!$con)
    {
        die('Could not connect: ' . mysql_error());
    }


    if($_POST['podnd'] > 31 || $_POST['podny'] > date("Y") || $_POST['podnm'] > 12)
    {
        die('Error: Check the Dates entered' . mysql_error());
    }

    if(strlen($_POST['stagn']) != 7)
    {
        die('Error: Check the tag entered. Service Tag is a 7 charecter alphanumeric code'.mysql_error());
    }

    mysql_select_db("software_it", $con);

    $sql="INSERT INTO purchaseorders (ServiceTag, PurchaseOrderDate,PurchaseOrderYear,PurchaseOrderMonth) VALUES
        ('$_POST[stagn]','$_POST[podnd]','$_POST[podny]','$_POST[podnm]')";

    if (mysql_errno() == 1062 || mysql_errno() == 1586 || mysql_errno() == 1859 || mysql_errno() == 3026) 
    {
        print('Error: New Tags only. Please use other link for updating existing records' );
    }

    if (!mysql_query($sql,$con))
    {
        die('Error: ' . mysql_error());
    }
    echo "1 record added";
    mysql_close($con)
    ?>
</body>
</html>

Open in new window

0
Comment
Question by:Siddharth S
5 Comments
 
LVL 7

Expert Comment

by:D Patel
ID: 41805917
The only way we could fix it, is by rebuilding the entire database from scratch.

Dropping all tables and reloading them from a backup.

Step 1

Create a backup of the database by issuing:

mysqldump -uUSER -pPASSWORD DBNAME > /tmp/my_backup.sql

Replace USER with your MySQL username, PASSWORD with your MySQL password and DBNAME with the databasename you are looking to repair.

Step 2

Drop and recreate the database

drop database DBNAME;
create database DBNAME;

Step 3

Reload the import

mysql -uUSER -pPASSWORD DBNAME < /tmp/my_backup.sql
0
 
LVL 9

Accepted Solution

by:
Mukesh Yadav earned 1000 total points
ID: 41805924
Try this ;)

if (mysql_errno() == 1062 || mysql_errno() == 1586 || mysql_errno() == 1859 || mysql_errno() == 3026) 
{
    print('Error: New Tags only. Please use other link for updating existing records' );
}

if (!mysql_query($sql,$con))
{
    die('Error: ' . mysql_error());
}
echo "1 record added";

Open in new window

replace this above code with following code:
if (!mysql_query($sql,$con))
{
    if (mysql_errno() == 1062 || mysql_errno() == 1586 || mysql_errno() == 1859 || mysql_errno() == 3026) 
    {
         die('Error: New Tags only. Please use other link for updating existing records' );
    }
}
else
{
     echo "1 record added";
}

Open in new window


You are checking for error before executing the query.

@Dhara there is no need to rebuild DB again.
1
 
LVL 7

Expert Comment

by:D Patel
ID: 41805925
Also,

The duplicate key entry happens when you reach the upper limit of the auto increment field, in your case, being a signed int the upper boundary is 2147483647.

Once the last record is with the id 2147483647, when you insert a new record the id should be 2147483648. But mysql reduce it inside the valid range, and tries to insert the id 2147483647, therefore it produces a duplicate key entry.

IMHO it's better altering the index field, setting it to an unsigned int (4294967295) or an unsigned bigint (18446744073709551615). Probably the unsigned int is enough for your client's requirements and it uses 4 bytes instead of 8 of the big int so the index would do it better in terms of cpu and memory.

ALTER TABLE test MODIFY id INT UNSIGNED NOT NULL AUTO_INCREMENT;

The current solution works because, you are reusing the ids of the erased rows, the duplicate key error will happen again depending on how may rows were deleted from the table previously.
0
 
LVL 60

Assisted Solution

by:Julian Hansen
Julian Hansen earned 1000 total points
ID: 41806006
Just a note on your code
1. You are using the mysql library - this has been deprecated you should consider moving your code to MySQLi instead - a sample of how to do this is shown below

2. You are accessing your $_POST variables directly
a) You cannot guarantee that the $_POST variable will exist - if it does not you will get an error
b) You cannot guarantee that what is sent to you is not malicious - you need to sanitize your inputs

With respect to the errors you list - where are those being reported? In your code you are doing error checking before querying the database - which does not make sense?
<html>
<body>
 <?php
	// USE MySQLi - VERY SIMILAR TO MySQL
	
	$mysqli = new mysqli("localhost","xx","xx","database");

	if ($mysqli->connect_error) {
		die('Connect Error (' . $mysqli->connect_errno . ') '
			. $mysqli->connect_error);
	}

	// CHECK FOR EXISTENCE OF POST VARIABLES AND SANITIZE
	// BY CASTING TO int
	
	$podnd = empty($_POST['podnd']) ? 999 : (int)$_POST['podnd'];
	$podny = empty($_POST['podny']) ? 999 : (int)$_POST['podny'];
	$podnd = empty($_POST['podnm']) ? 999 : (int) $_POST['podnm'];
	
	if($podnd > 31 || $podny > date("Y") || $podnm > 12) {
		die('Error: Check the Dates entered' . mysql_error());
	}

	// CHECK FOR EXISTENCE OF stagn VARIABLE
	$stagn = empty($_POST['stagn']) ? '' : $_POST  ['stagn'];
	
	if(strlen($stagn) != 7) {
		die('Error: Check the tag entered. Service tag is a 7 charecter alphanumeric code'.mysql_error());
	}
	
	// SANITIZE S  RING BEFORE INSERTION INTO QUERY
	$stagn = $mysqli->real_escape_string($stagn);

	// USE HEREDOC FOR QUERY
	$sql = <<< QUERY
		INSERT  INTO purchaseorders (
			ServiceTag, 
			PurchaseOrderDate,
			PurchaseOrderYear,
			PurchaseOrderMonth) 
		VALUES (
		'{$stagn}',
		'{$podnd}',
		'{$podny}',
		'{$podnm}'
		)
QUERY;
		
	// YOU HAVE NOT DONE ANYTHING WITH THE DB YET - SO WHY ARE YOU CHECKING FOR ERROR HERE
	/*
	if (mysql_errno() == 1062 || mysql_errno() == 1586 || mysql_errno() == 1859 || mysql_errno() == 3026) 
	{
		print('Error: New Tags only. Please use other link for updating existing records' );
	}
	*/
	
	// RUN QUERY
	$result = $mysqli->query($sql);
	
	// PROCESS RESULT
	if (!$result) {
		die('Error: ' . $mysql->error);
	}
	
	echo "1 record added";
	$mysqli->close()
?>
</body>
</html>

Open in new window

0
 

Author Closing Comment

by:Siddharth S
ID: 41817225
Thanks guys, sorry for the late reply. Have been unwell but solved this.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
Suggested Courses
Course of the Month14 days, 10 hours left to enroll

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question