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?