Solved

Error handling in PHP

Posted on 2016-09-19
5
50 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 5

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 6

Accepted Solution

by:
Mukesh Yadav earned 250 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 5

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 51

Assisted Solution

by:Julian Hansen
Julian Hansen earned 250 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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Using Quotation Marks in PHP This question (http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_28217211.html) seems to come up a lot for developers who are new to PHP.  And it got me thinking, "How can we explain the rule…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
This video teaches users how to migrate an existing Wordpress website to a new domain.
The viewer will learn how to dynamically set the form action using jQuery.

762 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now