way to inserting data into a MYSQL table data in an ordered way

[komrad]

Hello all,

I'd like to know if there is a way to insert into a MYSQL table data in an ordered way?
Right now as a workaround I've literally made a table of questions with questionID as primary key, nextQuestion and prevQuestion pointing to other questionIDs. Basically I've made a two way linked list from scratch. Is there anything built into MYSQL that implements this?

Thanks in advance.

[Fest3er]

I don't know if there is anything built into MySQL to do this. It's been a while since I've had to 'track' autoincrement values, but that's where you can look. But you can get close using autoincrement.

Basically, you create a table where the question_id is an auto-increment value. You would insert the first question using, for example, -1 for the previous_id and -1 for the next_id. For the rest of the questions, you would insert the question using LAST_INSERT_ID() for the previous_id and -1 for the next_id, then you would update the record with question_id=LAST_INSERT_ID()-1 and set its next_id to LAST_INSERT_ID().

This will work as long as nothing else is inserting rows into the table. I believe autoincrement is guaranteed to increment by one with each record inserted. You could create many separate lists of questions as long as you don't interrupt the insert process. (I don't know if there's a way to reset the LAST_INSERT_ID value.)

Want pseudo-pseudo code?

LOCK TABLE question_list;
  INSERT INTO question_list
          SET text='The text of the question',
              previous_id='-1',
              next_id='-1';

  INSERT INTO question_list
          SET text='Inside question text',
              previous_id=LAST_INSERT_ID(),
              next_id=-1;
  UPDATE question_list
     SET next_id=LAST_INSERT_ID()
   WHERE question_id=LAST_INSERT_ID()-1;

  INSERT INTO question_list
          SET text='Inside question text',
              previous_id=LAST_INSERT_ID(),
              next_id=-1;
  UPDATE question_list
     SET next_id=LAST_INSERT_ID()
   WHERE question_id=LAST_INSERT_ID()-1;

  INSERT INTO question_list
          SET text='Last question text',
              previous_id=LAST_INSERT_ID(),
              next_id=-1;
  UPDATE question_list
     SET next_id=LAST_INSERT_ID()
   WHERE question_id=LAST_INSERT_ID()-1;
  UNLOCK TABLES;

So this isn't built into MySQL, but I think it accomplishes what you want to do.