CODEWITHSUNDEEP
phpmysqlunique
Sakura
Sakura

Reputation: 93

Mysql database autoincreament id not increases in ascending order

I have created one table in mysql phpmyadmin,and i gave autoincreament for "id" and created one more column for image which should not be same image for same id thats why i have changed it to unique. now the problem is that id has autoincreamented but if i'll fire my insertion query it's inserted id number for exa:-1 to 10 and next time again i'll fire my query its insert id number like:-40 to 50 and now its taking directly 1000 to 1010 and so on. i'm using simple query:-

mysqli_query($con,"insert into Wheel_Place_Images(place_image_url,creation_date,last_modification_date,status)values('$image_url','$date','$date','1')");

i can't understand what is the problem here.

Upvotes: 2

Views: 405

Answers (1)

Hassaan
Hassaan

Reputation: 7662

You can reset or set initial value to auto increment in MySQL using following code.

Use this:

ALTER TABLE `Wheel_Place_Images` AUTO_INCREMENT=1001;

or if you haven't already added an id column, also add it

ALTER TABLE `Wheel_Place_Images` ADD place_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
ADD INDEX (place_id);

You don't need to insert place_id manually.

mysqli_query($con,"INSERT INTO `Wheel_Place_Images`
                     (place_image_url,creation_date,last_modification_date,status)
                     Values ('$image_url','$date','$date','1')");

UPDATED ANSWER

I have very simple trick to avoid gap in auto incremented ids.

Just get the max high value from id field then add 1 to it.

$row = mysqli_query($con, "SELECT MAX(place_id) AS `maxid` FROM `Wheel_Place_Images`");
if ($row) {
    $maxid = $row->maxid + 1; 
}

Then

mysqli_query($con,"INSERT INTO `Wheel_Place_Images`
                     (place_id, place_image_url,creation_date,last_modification_date,status)
                     Values ('$maxid','$image_url','$date','$date','1')");

Upvotes: 1

Related Questions