Page 1 of 1
php resetting of autoincrement
Posted: Tue Jan 12, 2010 11:46 am
by Da_Elf
im using the code
Code: Select all
$sqlauto = "ALTER TABLE 'User'" . " auto_increment = 1";
mysql_query($sqlauto,$dbConn);
and it doesnt seem to do anything what so ever.
Re: php resetting of autoincrement
Posted: Tue Jan 12, 2010 11:54 am
by Benwahballz
Da_Elf wrote:im using the code
Code: Select all
$sqlauto = "ALTER TABLE 'User'" . " auto_increment = 1";
mysql_query($sqlauto,$dbConn);
and it doesnt seem to do anything what so ever.
I havent looked at the doc for it, but the first thing that sorta pops into my mind would maybe be to use backticks rather than single quotes (replace ' with `). Cant hurt to try

Re: php resetting of autoincrement
Posted: Tue Jan 12, 2010 11:55 am
by pickle
Try echoing the query & run it manually - see what error you get.
Re: php resetting of autoincrement
Posted: Tue Jan 12, 2010 11:58 am
by Da_Elf
originalliy i had it as
Code: Select all
$sqlauto = 'ALTER TABLE `User`' . ' auto_increment = 1';
and it didnt do anything either. i got the code from doing it in mysql and clicking on the create php code button
Re: php resetting of autoincrement
Posted: Tue Jan 12, 2010 12:01 pm
by SidewinderX
What exactly are you expecting it to do? You can always do
Code: Select all
mysql_query($sqlauto,$dbConn) or die(mysql_error());
to see if there is an error.
Re: php resetting of autoincrement
Posted: Tue Jan 12, 2010 12:15 pm
by Da_Elf
no error came up. however what im wondering is that usually when i have 10 entries and i delete one of them i just do an auto increment reset to 1 and it automatically puts it to the next highest number (10 being the next to be uses after deleting). maybe when doing it through php code i have to reset it to the exact number needed
yes.. did a test to set it to something way over the last one like 200 and it did a reset to 200 so it worked in this case. so it means i need to check the database after the delete is done to find out the last entry and add one and set the autoincrement to whatever that is
had to use
Code: Select all
$queryz = "SELECT * FROM User ORDER BY UserID DESC LIMIT 1 " ;
$resultz = mysql_query($queryz);
$rowz=mysql_fetch_assoc($resultz);
$newnumber = $rowz[ 'UserId' ];
$newnumbe = $newnumber +1;
$newnumb = "000000000" . $newnumbe;
$newnum = substr($newnumb,-10,10);
$sqlauto = 'ALTER TABLE `User`' . ' auto_increment = ' . $newnum;
mysql_query($sqlauto,$dbConn) or die(mysql_error());
Re: php resetting of autoincrement
Posted: Tue Jan 12, 2010 1:19 pm
by Benwahballz
I thought you were emptying out a table, and wanted to reset the auto increment counter in which that case it would let you reset it to 1.I believe that it will reset the counter to the next highest available number.
Re: php resetting of autoincrement
Posted: Tue Jan 12, 2010 1:29 pm
by Da_Elf
using phpmyadmin setting it to 1 will cause it to go to the last available incremental number. but when trying to do it via php code it didnt work. so i had to do it this way. however im getting a problem now when trying to place this in a function.
Code: Select all
function inc_f($table, $order, $theid){
$queryz = "SELECT * FROM $table ORDER BY $order DESC LIMIT 1 " ;
$resultz = mysql_query($queryz);
$rowz=mysql_fetch_assoc($resultz);
$newnumber = $rowz[ '$theid' ];
echo $newnumber . "</br>";
}
it come back blank if i was to use
Code: Select all
inc_f('User', 'UserId', 'UserId');
never mind. fixed it needed to take '$theid' out of the ''