0

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?

3
Contributors
3
Replies
37
Views
2 Years
Discussion Span
Last Post by matrixdevuk
0

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

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.