PDA

View Full Version : PHP code for auto increment


Kan20
05-12-2010, 12:56 PM
Hi,

I'm using Wamp server on Windows XP machine.

MySQL's auto increment field doesn't reset wen I delete all the entries in the table. It continues with last inserted ID.

I want it reset or start with 1 wen there's no entry in the table. And den it should increment the ID.

So I wrote below code for auto increment.

include "Connections/database_connect.php";
$q2 = "select ID from products";
$result2=mysql_query($q2);
$num=mysql_numrows($result2);
$num++;
$ID = $num;
But still it gives problem. It starts with 0 and when I try to add entries it says:

Can't run query because Duplicate entry '0' for key 1.

Please help!

Thnks in advance.

Corrosive
05-12-2010, 01:27 PM
I don't think you can force the hand of auto_increment to my knowledge.

edbr
05-13-2010, 03:35 AM
mysql has the auto increment value . it will increment if you add an empty value '', or just create a new record

Kan20
05-13-2010, 08:12 AM
Okay thanks. But is my above code correct?

~ K

tux
05-13-2010, 08:54 AM
Not at home at the moment to check, but I'm sure this can be done.

I will post later when I can get to my PC.

From memory tho', select the field tab of the Auto Increment Field. I think its in the dialogue that comes up.

Paul

tux
05-13-2010, 10:25 AM
You can reset the auto increment to restart from 1 but you have to delete all records in the table. You can however set a higher number than the current one without deleting any records.

With the table open in SQL Manager choose Properties. Change the Auto Increment Value to zero to start again from 1 ( table must be empty ) or a higher number. Click refresh and save changes.

davidj
05-13-2010, 08:28 PM
Please be careful here

The reason why it doesn't reset is to do with the mechanism of the auto increment and the relationship between tables.

Your tables could have foreign keys using the auto incrementing field as the primary key. When you delete a primary record you don't want to reassign that ID as there could be tables which could falsely form a relationship with another table.

You really don't need to worry about resetting this field. I write apps in a development environment. I move it to a UAT environment and then it goes live. By the time it goes live I have auto-incrementing values reaching well into the thousands. I don't reset them. There is no need