Adding an integer to primary key auto-increment mysql php ajax

Question

I have a form that I submit with an ajax call where the primary key which is the 'id' column is set to auto increment.

When I post without stating an ID it works fine and items are entered to the table in an increment of 1.

What I am trying to do is tell my query what ever the next auto increment is...for example it is now at 5, I want it to add 10 to the 5 that it currently is at and so it enters the id as 15. can someone help me with the syntax because I can't seem to get it to work..again it posts fine without the id stated in the query.

here is the query as it stands not working. BTW I already know it is not a great idea to mess with the primary key but in this situation it is a quick fix to a yucky problem that I will address after they are back up and running.

$sql = "INSERT INTO Products
    (id, item, description, shelf, active) 
VALUES 
    ('".id."'+ 10, '".$item."', '".$description."', '".$shelf."', '".$active."')";

Thanks

If you've "messed up" things with auto increment you can reset them, if this is your problem... — AnTrakS, Sep 03 '18 at 14:51
Instead of trying to insert the id value, change your auto-increment value in the table definition. — Sloan Thrasher, Sep 03 '18 at 14:51
You would have to retrieve the highest value of the ID prior to the insert. However, you would have no way to know if another query incremented that value between the value you retrieve and the insert statement. Why would you want to do this in a query? — Sloan Thrasher, Sep 03 '18 at 14:54
Here is some basic information on options with auto increment https://dev.mysql.com/doc/refman/8.0/en/example-auto-increment.html — r lo, Sep 03 '18 at 15:15
You can change the auto increment with an `ALTER TABLE` statement, see https://stackoverflow.com/questions/970597/change-auto-increment-starting-number. But why do you want to mess with the auto increment value/column? — Progman, Sep 03 '18 at 15:33

score 3 · Accepted Answer · answered Sep 03 '18 at 15:29

You can try to set the auto_increment index and do the insert query right after. Something like that:

// Pseudocode
$id = $sql("SELECT 'AUTO_INCREMENT' FROM  INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'yourDbName' AND TABLE_NAME='Products')");

$sql("ALTER TABLE Products AUTO_INCREMENT = ".($id + 15));

$sql = "INSERT INTO Products
(item, description, shelf, active) VALUES 
(".$item."', '".$description."', '".$shelf."', '".$active."')";

Let mysql set the id and go on with the indexing.

Adding an integer to primary key auto-increment mysql php ajax

1 Answers1