October 10th, 2000, 08:52 AM
I am maintaining a quiz script, and I want to know how to insert a row into a mysql table using the lowest availalble primary key as the index.
Here's my situation
3 |blah blah
how do I get it to insert the value as question 2 instead of 4?
October 10th, 2000, 09:36 AM
<<how do I get it to insert the value as question 2 instead of 4?
Here You can insert your values directly.
INSERT INTO tablename(question,etc) VALUES(3,'blah blahblah blah')
if you want to insert a row lesser than the highest number in the table then you have to issue a select statement first and find out a number that is not availbe in that fields..may be you have to do a looping or something..
i would suggest you to create a primary key with auto-increament field so you don't have to take care of which row it is inserting...
www.jobxyz.com-IT Career Portal
"The fear of the LORD is the beginning of knowledge..."
October 10th, 2000, 09:47 AM
Yeah, I thought of that.
I do have it auto_increment
but say I have questions 1-10. I delete 6 because I don't like it. As it stands, if I make a question to replace it, it'll insert it as question 11.
Would it be better to update the table so any questions greater than the one I delete are shifted down (10 becomes 9, 9 becomes 8, etc.)?
October 10th, 2000, 02:04 PM
You shouldn't use your primary key as a means to order the display of data. The purpose of the primary key is to give the database a method for tracking records.
Ordering record display should be the function of another column, which is not auto-incremented, (maybe UNIQUE) but handled by your application logic. For this column, as Shiju says, you will have to read existing records to see which numbers are available, and loop through all records if you need to shift order up or down.