FRIHOST • FORUMS • FAQ • TOS • BLOGS • DIRECTORY
You are invited to Log in or Register a Frihost Account!

mysql problem

   Frihost Forum Index -> Scripting -> Php and MySQL
 


alalex
I have a problem with a mysql database, which i use to create news for a site.
I can do that allright, but i use the id field set to auto_increment to display the news number, and if i delete all teh news, when i was at number 5, the next new I create is not 1, is 6!
for example:

news 1: asdjh
news 2: askfh
news 3: askjh

and if i delete all of them, and create a new one, I see:

news 4:asklñjdf
Is there a way to 'reset' the id auto_increment field?
Alie
You can use REPLACE sql command instead of DELETE and then INSERT INTO... For examle you want to delete number 3 and add something new instead.

If you want to reset all numbers you need to clear the whole sql table: TRUNCATE TABLE sql command.
hexkid
alalex wrote:
Is there a way to 'reset' the id auto_increment field?

Code:
alter table WHATEVER_YOUR_TABLE_NAME_IS auto_increment=1


SQL example (not PHP!):
Code:
-- EDIT2: removed extra underscore in primary_key
create table x (id int auto_increment primary key, v varchar(10));
insert x values (null, 'one'), (null, 'two');
delete from x;
insert x values (null, 'three'), (null, 'four');

-- EDIT: MOVED delete line to after select

select * from x; -- shows ids 3 and 4
delete from x;
alter table x auto_increment=1;
insert x values (null, 'five'), (null, 'six');
select * from x; -- shows ids 1 and 2


Last edited by hexkid on Fri Dec 29, 2006 2:49 am; edited 3 times in total
alalex
ok thanks Very Happy
Reply to topic    Frihost Forum Index -> Scripting -> Php and MySQL

FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2007 Frihost, forums powered by phpBB.