Error in MySql database

[prokhaled]

when I try to add new record to MySql datebase I face this error message:

---------------------------------------------
Error
SQL-query :

INSERT INTO `VisualBasicBank` (`BankID`, `Task`, `Author`, `EmailOrHomePage`, `DateSubmitted`, `Category`, `Url`, `Code`, `View`) VALUES ('130', 'NULLrty', 'NULLry', 'NULLrty', 'NULLrty', 'NULLrt', 'NULLrey', 'NULLery', 'NULLrey');

MySQL said:

Duplicate entry '127' for key 1
---------------------------------------------

How can I solve this problem. ?

note : when I remove row and try to add new one , it's added without problem but when I try to add new one it give me the same error.. What can I do ?

-

[scoutt]

uhhh what is your code?

[prokhaled]

there is not any error in code .. the code was work without any problem .. the error in database .. How can I repair the database..

also when I try to insert new row using phpmyadmin I face the same problem.

[scoutt]

you are getting that error because there is already an Bankid # of hte one you try to insert. the only way I see to fix this is to delete that table and start fresh.

double check you id categorey for duplicates.make sure it is set to auto increment and it is primary

[scoutt]

you are getting that error because there is already an Bankid # of hte one you try to insert. the only way I see to fix this is to delete that table and start fresh.

double check you id categorey for duplicates.make sure it is set to auto increment and it is primary

[prokhaled]

the table is auto increment and it is primary.

the problem occur because there is row it's ID=127 and auto next ID=127 also, it must=128 .. I do not know why this error happen ..

to fix this problem I must let auto next ID=128 but how can I do that ?

also

If I can remove all spaces form datebase I think that will solve the problem .. Can I remove spaces from database ?

Thanks

[scoutt]

yes that is why you are getting the error. the id category has to be auto increment, not the whole table. you can go in and edit the id field and change it so it is in order. or you can delete the table and start over with everything in order.

that is the problem with deleting rows so many times.

[The Hobo]

I always have weired autoincrement index errors too...

Is BankID the primary key? or is there another ID field?


What I always do, is count the number of rows. After that, and assuming that the ID's go in order without any numbers missing, I just set the ID with that number + 1. So:

$numrows = mysql_num_rows(mysql_query("SELECT * FROM tablename")) + 1; 

Then use the value stored in $numrows for the ID value. Just remember, as I already said, the ID's must not have any holes in it, ie, 1 2 3 5 6 8 10. Otherwise it wont work.

[scoutt]

that is one way, but as I have seen it and this is what happens on mine. if you have rows like so


1 2 3 5 6 7 10

and you inserted a new record it should be 11, so this record will fill the first available hole (empty row) it finds and inserts the record. so it will look like this

1 2 3 11 5 6 7 10

that is if the ID field is set to auto-increment. mine works this way.

[The Hobo]

I already stated that it wont work if they aren't in order without holes.

[scoutt]

I know and all I stated is that you don't need to do anything if ID field is auto icremented. Mysql will fill the holes with the new id.

[prokhaled]

yes, BankID the primary key .....

[scoutt]

but is it set to auto increment? if yes then you will have to delete some or clean out the database as you have something internal going on.

[The Hobo]

SCOUTT SKIZZLE!

[scoutt]

huh

[prokhaled]

I found out the cause of eror

the error happen because I set BankID as tinyint , the maximime of tinyint is 127 so error happend . I changed tinyint to int and the problem was solved.

Thanks

[chrisjk]

I use Medium Ints for auto incrementing fields; partly because when I set up a table in phpmyadmin with an auto incrementing field, it set the datatype to medium int (9) so I figured "why not?"

Whatsmore, I don't have any problems with auto increments. They're useful just to guarantee uniqueness

[blindlizard]

Once you do an insert on a table with autoincrements, how do you know what the value was short of doing another select of the max?

[scoutt]

you can't and why would you want to. just do a select on it.

[chrisjk]

The trouble with doing a SELECT after an INSERT is that there is every possibly that another record may be inserted before the SELECT is carried out, esp. on a system with a lots of users.

You could either get into table locking, or use another method such as entering a unique string of numbers/chars into a field, then looking up the auto increment field from that.

[scoutt]

why would that matter? if you do a select of everything and then get the num_rows it will give you a general idea of what the id is at.

but like I said, why would you......

[chrisjk]

You might want to get the ID of the field you just inserted.

e.g., a customer's order is inserted into a database, you now want to tell them the ID of their order. The ID is an autoincrementing field..

Perfectly possibly scenario.

[scoutt]

sure don't play fair.

ok if you are goin gthat way, then when the order is confirmed and you want to display the id, you would search for the username of that order, not the id, so you won't have to worry about another user ordering right behind you.

and if you don't keep the username in the order then you have problems from gate 1.

[chrisjk]

maybe you don't have a user-based system, people just enter a few details about themselves

What happens if they've made a few orders, you'd get more than 1 row returned

Anyway, to answer the original question ("how do you know what the value was short of doing another select of the max"), you can't really becase of what I said, which is why you need to think of another way of doing it.

[blindlizard]

So then it is not a good idea to use autoincrement for a primary key if you need to know the value.

[scoutt]

if you don't use auto increment then you are creating extra work for yourself. also it will lead to the same problem you asked about.

the reason I say that is you have to query the database and get the last id and then add 1 to it so you can insert the next ID. in the meantime somebody is doing the exact samething. so you aren't getting away from anything.

[chrisjk]

auto increment is a very useful feature, but the only way I can think of to get the ID of the record you just inserted is to insert some other unique identifier into the record which you know. Generating a random number is a good way.

That way, you can easily get the primary id of the correct record and not one that may have been inserted in the meantime

[TheGoldenShogun]

you dont always need to know the ID for the auto incrementing number before you insert it. All my inserts are on autoincrementing tables and if you just put a 0 in that field, it does it for ya.

INSERT into tblName VALUES (0, '$fname', '$lname');

and to grab that ID afterwards you can use the mysql_insert_id() function. I believe it takes the argument of the query so...

$theQuery = mysql_query ("INSERT into tblName VALUES(0, '$fname', '$lname')");

$theID = mysql_insert_id($theQuery);

or you use no argument and it grabs the last ID of the last query you've done.

[chrisjk]

wow, I can't believe I didn't know about mysql_insert_id()

Thanks man!

You can do it in the SQL statement too http://www.mysql.com/doc/G/e/Getting_unique_ID.html

[scoutt]

still doesn't solve what you said chris. somebody else could still add one to the database and you can still get the wrong id.

the best way is to have a unique category that you can query instead of the id.

and Golden: you don't have ot use a 0 just leave it blank

$theQuery = mysql_query ("INSERT into tblName VALUES('', '$fname', '$lname')");

[chrisjk]

Originally posted by scoutt
still doesn't solve what you said chris. somebody else could still add one to the database and you can still get the wrong id.

I assumed they would have covered that possibilty...oh well