[darb93]

Hi all,
I am trying to make my own auto increment with php and sql. It keeps returning a error Error: Duplicate entry '1' for key 'PRIMARY'. It would be great if you could help me. Thanks in advance.

<?php
$con = mysql_connect("localhost", "", "");
if (!$con)
	{
	die ('Count not connect: ' . mysql_error());
	}
mysql_select_db("", $con);
$sql2= "SELECT MAX (staffID) FROM staffDetails";
$sql3= $sql2 + 1;
$sql= "INSERT INTO staffDetails (staffID, surname , firstname, address , phoneNumber , jobTitle , username , password)
VALUES
($sql3,'$_POST[surname]','$_POST[firstname]','$_POST[address]','$_POST[phoneNumber]','$_POST[jobTitle]','$_POST[username]','$_POST[password]')";
if (!mysql_query($sql,$con))
	{
	die('Error: ' . mysql_error());
	}
?>
	<script type="text/javascript">
	alert("Member has been added");
	history.back();
	</script>
	<?php
mysql_close($con);
?>

[Dormilich]

the problem is line #9. $sql2 is a string and when you add 1 the string is converted to 0, resulting in 1 (always).

despite the fact that one should always use the built-in AUTO_INCREMENT for an auto increment and that the mysql extension is deprecated and outdated and that you are wide open to SQL Injection, you would have to query and fetch your first SQL statement to get the number.

[darb93]

Thanks for the reply.

Is there a way of converting my string $sql2 to a int value.

In the project I am currently doing we are not allowed use the built in auto increment. We had a choice of making our own AUTO_INCREMENT or a random ID generator

[Dormilich]

PHP automatically converts the string to a number, in this case 0.

but you don’t want the string converted, you want the result of that query. so you have to query the DB.