Reset auto increment in php

Suppose i have a database that store report from employees which first column is reportID(auto increment), staffID,name,department and so on.

Everytime i submit i report from my php page it will display increment number (reportID) in my php page. So lets say i got 3 report ID stored in the table, i want to delete three record, and then add 1 new record to the table, but now the reportID display as 4 because the past three reportID are 1,2,3, so how do i reset the new record to 1 instead of 4? is there a code to reset it? This is my deletereport.php

<?php
include('adminconfig.php');
include('config.php');

$reportID = mysql_real_escape_string($_GET['id']);

$sql="DELETE FROM `report` WHERE `reportID`='$reportID'";
$result=mysql_query($sql);

if(!$result){
   die('invalid query:'.mysql_error());
 }
 else
 ?>
<p style="font-family:arial;color:#0066CC;font-size:30px;">One row deleted...</p>
<?php
header('Refresh:3; url=viewreportdb.php');
die;

?>

In this example i am going to explain How to reset AUTO INCREMENT to one in MySQL PHP.

There is many way to reset AUTO_INCREMENT to 1in MySQL

Using SQL Query

Run the query in your database.

ALTER TABLE tablename AUTO_INCREMENT = 1;

Example:

ALTER TABLE student_data AUTO_INCREMENT = 1;

Using PHP code

index.php

<?php

$host='localhost';
$username='root';
$password='';
$conn=mysqli_connect($host,$username,$password,"student");
mysqli_query($conn,"ALTER TABLE category AUTO_INCREMENT = 1");
echo "Auto_increment reset to 1 Successfully ! ";

?>

This MySQL tutorial explains how to reset sequences using the AUTO_INCREMENT attribute in MySQL with syntax and examples.

Description

You can reset the next value assigned by the AUTO_INCREMENT at any time using the ALTER TABLE statement in MySQL.

Syntax

In MySQL, the syntax to reset the AUTO_INCREMENT column using the ALTER TABLE statement is:

ALTER TABLE table_name AUTO_INCREMENT = value;

Example

Let's look at an example of how to reset the next value assigned to an AUTO_INCREMENT column in a table in MySQL.

For example, if we had a suppliers table that was defined as follows:

CREATE TABLE suppliers
( supplier_id INT(11) NOT NULL AUTO_INCREMENT,
  supplier_name VARCHAR(50) NOT NULL,
  account_rep VARCHAR(30) NOT NULL DEFAULT 'TBD',
  CONSTRAINT suppliers_pk PRIMARY KEY (supplier_id)
);

We could reset the next value in the sequence for the supplier_id field (which is the AUTO_INCREMENT field in the suppliers table) with the following ALTER TABLE statement:

ALTER TABLE suppliers AUTO_INCREMENT = 1;

This example would change the next value in the AUTO_INCREMENT field (ie: next value in the sequence) to 1 for the supplier_id column in the suppliers table. Now when a new record is inserted into the suppliers table, the supplier_id column will be assigned the value of 1 in the newly created record.

TIP: Because there can only be one AUTO_INCREMENT field in a table, you only need to specify the table name (and not the field name) in the ALTER TABLE statement when resetting the next value in the sequence.

phpmyadmin

Perhaps you could just select the phpMyAdmin Operations tab:

• In phpMyAdmin, click on the table you want to reset or change the AUTO_INCREMENT value
• Click on the Operations Tab
• In the Table Options box find the auto_increment field.
• Enter the new auto_increment starting value
• Click on the Go button for the Table Options box.

Since this one of the most frequently asked questions for phpmyadmin, you can learn more about this in this blog : http://trebleclick.blogspot.com/2009/01/mysql-set-auto-increment-in-phpmyadmin.html

Supplemental Info

For an empty table, another way to reset the auto_increment attribute is to run

TRUNCATE TABLE mydb.tablename;

Don't run this if you have data in it. If you want to hose the data, then be my guest.

In phpmyadmin, just click the SQL tab, enter the command, and run it.

For a nonempty table, you may want to adjust the auto_increment attribute to the highest existing id in use in case higher entries were deleted.

First, optimize the table

OPTIMIZE TABLE mydb.mytable;

Next, locate the highest value for the auto_increment column (say it is id)

SELECT MAX(id) maxid FROM mydb.mytable;

Suppose the answer returns 27. Goto the Operations tab and enter 28.