MySQL

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

Please support our MySQL advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Sep 2006

Posts: 22

Reputation: komrad is an unknown quantity at this point

Solved Threads: 1

komrad

Offline

Newbie Poster

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

Jan 7th, 2009

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.

Global Domain International - Income For Life
#1 Home Based Business
Get massive downlines and 100% retention for your MLM business

•

Join Date: Aug 2007

Posts: 165

Reputation: Fest3er is an unknown quantity at this point

Solved Threads: 18

Fest3er

Offline

Junior Poster

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

Jan 9th, 2009

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?

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
  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;
  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.