Hi,

I use the following code from phpmyadmin to reset one of my table's auto increment value from 1.

SET @count = 0;
UPDATE `mytable` SET `mytable`.`id` = @count:= @count + 1;
ALTER TABLE `mytable` AUTO_INCREMENT = 1;

But I want to run this query from PHP file and set a cron job everyday to do this. Table's storage engine type is INNODB. And id is not related in any of my tables (not a foreign key), so its ok to reset. Reason to reset is beacuse everyday million rows gets inserted and deleted once the day is completed. So increasing values for id does look right to me.

I have tried following code from php file, but did not get through with my desired result.

$query = "SET @count = 0";
$result = $db->Execute($query);

$query = "UPDATE `mytable` SET `mytable`.`id` = @count:= @count + 1";
$result = $db->Execute($query);

$query = "ALTER TABLE `mytable` AUTO_INCREMENT = 1";
$result = $db->Execute($query);

Could any one suggest to achieve this please?

If the table rows get deleted and you want to reset the primary key just use TRUNCATE mytable;

Forget to mention that, there are still data remains after the delete

You do TRUNCATE TABLE mytable

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.