#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2000
    Posts
    5
    Rep Power
    0
    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

    question|etc...
    1 |blah
    3 |blah blah

    how do I get it to insert the value as question 2 instead of 4?
  2. #2
  3. .Net Developer
    Devshed Novice (500 - 999 posts)

    Join Date
    Feb 2000
    Location
    London
    Posts
    987
    Rep Power
    15

    <<how do I get it to insert the value as question 2 instead of 4?
    >>


    thelgm,
    Here You can insert your values directly.

    INSERT INTO tablename(question,etc) VALUES(3,'blah blahblah blah')

    --Notes:

    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...







    ------------------
    SR -
    webshiju.com
    www.jobxyz.com-IT Career Portal
    ezipindia.com--WebStudio


    "The fear of the LORD is the beginning of knowledge..."
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2000
    Posts
    5
    Rep Power
    0
    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.)?
  6. #4
  7. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    61
    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.

Similar Threads

  1. autonumber primary key
    By densma in forum MS SQL Development
    Replies: 7
    Last Post: March 8th, 2005, 12:46 PM
  2. Inserting checkbox values from an array into MySQL
    By outlyr242 in forum PHP Development
    Replies: 1
    Last Post: February 9th, 2004, 04:28 PM
  3. primary key
    By durilai in forum MySQL Help
    Replies: 1
    Last Post: February 6th, 2004, 02:16 PM
  4. show primary key
    By weebeng in forum Oracle Development
    Replies: 2
    Last Post: January 19th, 2004, 08:08 AM
  5. auto_increment primary key field
    By ez_d_ in forum MySQL Help
    Replies: 4
    Last Post: December 29th, 2003, 06:08 PM

IMN logo majestic logo threadwatch logo seochat tools logo