PHP calling wrong number from MYSQL Database

autoIncrement Issue?

[briesm]

Hey, so I've got an online mySQL database, every once and a while I have a program that retrieves all entries from it. Each entry is identified by a primary key called order_Number
Now, I have this set on auto increment so that it is always a new number, 1 greater than the last. But when I remove all entries, it loses its place and defaults back to 1. Anyone know how to overcome this issue?

[boxrec]

This isn't a php question it's mysql, off the top of my head so please test these out and post the results

DELETE FROM TABLE (tbl_name) where order_Number = X; will not reset the counter even if the table is empty

you could do it this way ....

SELECT last_insert_id() from tbl_name; gives the last value of counter ie 999

TRUNCATE TABLE (tbl_name); empties the table and resets the counter to 0

ALTER TABLE tbl_name AUTO_INCREMENT = 999; will set the counter to 999

[lobo235]

"ALTER TABLE `table_name` AUTO_INCREMENT = 56"

[briesm]

Right before I delete, can I somehow retrieve the auto Index and save it to a variable? Then I could Alter table and set the autoindex to that.

Edit: How about this:

$sql = "SELECT LAST_INSERT_ID() FROM customerMaster";
$reference = mysql_Query($sql);
$reference = $reference++;
$sql = "ALTER TABLE customerMaster AUTO_INCREMENT = $reference";
mysql_Query($sql);