Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2002
    Posts
    6

    Question Unanswered: Inserting rows in specified order???

    Ok, my first post here guys so please don't make fun of me too much

    So, I got this silly idea to be able to insert entries/rows into specific locations. For example:

    Let's say I have 3 columns

    $query = "CREATE TABLE lessons (
    EntryID INTEGER AUTO_INCREMENT PRIMARY KEY,
    Title VARCHAR(30), Text TEXT)";

    (This is how the table is setup now, but I understand I may be totally wrong in this setup...)

    These entries are displayed one per page in my application w/next and prev buttons. My question regards wanting to insert a new row between two existing.

    My idea is to create an "orderID" column to reference the rows display order (1,2,3,4, etc.) and write my "add" script to insert the newly created row between two existing, by referencing the "previous/above entry" the script would then add +1 that number assigning the position of the newly created row to follow the reference number, and add +! to all following numbered rows to ensure the correct order.

    So I'm probabally going about this half blind and back asswards, like you can't tell from the post I'm quite new to mySQL and PHP, any help Would be greatly appreciated. Thanks.
    JOSH

  2. #2
    Join Date
    Feb 2001
    Location
    NC, USA
    Posts
    200
    Hi Josh

    There is no need to insert the records into the database in any particular order. What's important, is that you are able to retrieve them in the correct order. This is done using the ORDER BY clause in your query.

  3. #3
    Join Date
    Feb 2002
    Posts
    6

    Lightbulb

    Hi Paul,

    Ok, I can see that putting them in order within the database is totally un-necesary, but then how do I go about refrencing their order, don't I need something like my idea (the counting portion) to allow the display order to be updated?
    Last edited by josh013; 02-28-02 at 16:53.

  4. #4
    Join Date
    Feb 2001
    Location
    NC, USA
    Posts
    200
    Not sure I fully understand, but a simple solution is;

    Create the order id column and populate it with the relevent data.

    The starting page is determined by the first query using WHERE orderID=1. Then when you click the next button, you issue another query by passing the current orderID to the script which will call WHERE orderid=$current_orderid+1.

  5. #5
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    josh013, I understand what you are saying. At our site we have a lot of applications that use dropdown boxes, list options. Generally you would think that the lists should be in alphabetic order (ORDER BY Description), but no the endusers wish to order the list by most popular or whatever. So we have a table like this

    Code integer,
    Description varchar(200),
    DisplayOrder integer

    Where we return the values ORDER BY DisplayOrder.

    If you need to insert a row between 2 rows then you would update DisplayOrder = DisplayOrder + 1 where DisplayOrder >= NewDisplayOrder

    Code:
    select Code,Description,DisplayOrder 
    from table
    order by DisplayOrder
    
    Code        Description DisplayOrder 
    ----------- ----------- ------------ 
    1           XXXX        1
    4           DDDD        2
    5           LLLL        3
    2           ZZZZ        4
    3           MMMM        5
    
    update table
    set DisplayOrder = DisplayOrder + 1
    where DisplayOrder >= 4
    
    insert table (Code,Description,DisplayOrder) values (6,'0000',4)
    
    Code        Description DisplayOrder 
    ----------- ----------- ------------ 
    1           XXXX        1
    4           DDDD        2
    5           LLLL        3
    6           0000        4   <-- New Record  
    2           ZZZZ        5
    3           MMMM        6

  6. #6
    Join Date
    Feb 2002
    Posts
    6

    Talking

    WOOOO-HOOOOO!!!!

    Thanks guys!

    I can't belive this. I've been searching for info on how to do this for a week. My first post here and litterally within hours I've got an answer.

    dBforums ROCK!

    Thanks again.

    JOSH

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •