Solved

Error handling in PHP

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

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 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 56

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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

It’s a strangely common occurrence that when you send someone their login details for a system, they can’t get in. This article will help you understand why it happens, and what you can do about it.
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
This video teaches users how to migrate an existing Wordpress website to a new domain.
Learn how to set-up PayPal payment integration in your Wufoo form. Allow your users to remit payment through PayPal upon completion of your online form. This is helpful for collecting membership payments, customer payments, donations, and more.

726 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