auto_increment

[aysx]

create table testtable (

id int(4) not null auto_increment,

name varchar(30),

primary key (id) )

 

say i add some names to testtable. each name has an id, and the id is auto_increment. i have 50 names. then i decide to delete all the names except the first name i added to the database (that has an id of 1).

 

THEN i add another name to the database. i expect the id of the name to be 2, but instead it\'s... 51. o_O; kind of logical, seeing it\'s the fifty-first name i added to the database, no matter if i deleted the rest.

 

.. but i don\'t WANT it to be 51! i want it to be 2! 1-> 2, not -> 51! -___-; does anyone know if you can fix this? or is this just my computer acting up again X_x;

[shivabharat]

Yup! thats the problem with auto increment.....

 

Now the best way would be to remove the auto increment and use a simple quesry to detect the count and add that

 

i.e do a count and say u get 1 just do 1 + 1 =2 which would be ur next id number.

[Avalanche]

I can\'t get it to work...

 

what do I have to put as the ID for whatever I insert into my table if I use auto_increment?

 

I have the users define the first, last, age, and race variables on the previous page, and this is my code for the MySQL page:

 

[php:1:f7325e52b1]<?php

 

$conn=mysql_connect (\"localhost\", \"burnttoa_tehuser\", \"tehpass\") or die (\'I cannot connect to the database because: \' . mysql_error());

mysql_select_db (\"burnttoa_tehdb\",$conn);

 

$q1 = mysql_query(\"CREATE TABLE roleplayers (id int(4) not null auto_increment, first varchar(255), last varchar(255), age varchar(255), race varchar(255), PRIMARY KEY (id), UNIQUE id (id))\",$conn);

if ($q1==FALSE) {

print \"<code><b>Error:</b> Creation of table \"roleplayers\" failed!<br></code>\";

} else {

print \"<code>Creation of table \"roleplayers\" was sucessful!<br></code>\";

}

 

$q2 = mysql_query(\"INSERT INTO roleplayers VALUES (auto_increment,\'$first\',\'$last\',\'$age\',\'$race\')\",$conn);

if ($q2==FALSE) {

print \"<code><b>Error:</b> Something went wrong while adding your information!<br></code>\";

} else {

print \"<code>Succesfully added your information!<br></code>\";

}

 

mysql_close($conn);

 

?>[/php:1:f7325e52b1]

 

The first query (creating the table) goes fine, but then when I try to add the data it gives me the error message.

 

If I just set the id as 1, it will work correctly, but I need it to set the id to 2 if they add another thing and so on.

 

EDIT: Ahh... I get it now. I don\'t set the data ID to literally auto_increment, he just meant he was using auto_increment to increase the data IDs. But, still, how do I get it to work?

[Avalanche]

Nevermind, figured it out, just leave the id field blank.