CODEWITHSUNDEEP
phpmysqlsqlcsvauto-increment
bockymurphy
bockymurphy

Reputation: 61

PHP MYSQL set AUTO INCREMENT to MAX + 1

I have a PHP file that inserts data from a CSV file into the MYSQL DB using the LOAD DATA INFILE sql function.

If the CSV data is a duplicate it is not inserted because of the DB table indexing rules (unique).

$sql = "LOAD DATA LOW_PRIORITY LOCAL INFILE 
       '" . $makes_file . "' IGNORE 
       INTO TABLE make 
       FIELDS TERMINATED BY ',' 
       LINES TERMINATED BY '\n' (make, img_url)";

$link->query($sql) or die(mysqli_error($link));

For some reason the Auto Increment of the table is not correct from this process.

So I have used the following code to correct this. 

$get_max = $link->query("SELECT max(id) as max FROM `make` LIMIT 1");
while ($r = $get_max->fetch_assoc()) {
    $link->query("ALTER TABLE  `make` AUTO_INCREMENT = " . ($r['max'] + 1)) or 
    die(mysqli_error($link));
}

So if anybody knows:

  1. Why the Auto Increment is incorrect from the LOAD DATA sql or
  2. If there is a 'prettier' way to set the auto increment to the max(id) +1

Thanks

Upvotes: 3

Views: 7822

Answers (3)

Walid Reda
Walid Reda

Reputation: 1

Check your last id in your database manually and get it

Then insert new record and set id to Your last id plus say 3 or 4

Example

If your last taken id is 5000 Insert new record and set id to 5003 Then auto increment will works again from 5003.

Upvotes: 0

Aaron
Aaron

Reputation: 171

For MyISAM tables you can just set AUTO_INCREMENT to 0 and MySQL will use the current maximum value plus one.

$link->query('ALTER TABLE make AUTO_INCREMENT = 0');

From the docs:

You cannot reset the counter to a value less than or equal to any that have already been used. For MyISAM, if the value is less than or equal to the maximum value currently in the AUTO_INCREMENT column, the value is reset to the current maximum plus one.

Upvotes: 5

Charaf JRA
Charaf JRA

Reputation: 8334

This answer is about a prettier way to set the auto increment to the max(id) +1, You can do this with one Query only :

$link->query("ALTER TABLE  `make` AUTO_INCREMENT = ( SELECT max(id)+1 FROM `make` LIMIT 1 )" );

But your first solution should work if AUTO_INCREMENT is unique , check your database sheme

Upvotes: 1

Related Questions