Pulling previous and next record so I can reorder items.

[dprichard]

I am working on a document section for my website and have a table that has something like this:

 

doc_category_id

doc_category_name

doc_category_order

 

I want the user to be able to reorder the items and make them appear in the order they want.  Is it possible to list them category with the doc_category_id and doc_category_order of the record right before and right after the one they are viewing so that I can make a small form with up and down arrows that when submitted will change the current record order up or down one, but also change the previous record down one if they move the current item or or change the change the next record up one if they move the current item down? 

[dprichard]

Here is what I have come up with so far.  I have these two forms that show the doc_cat_order and will subtract one if you click the up arrow and add one if you click the down arrow.  I did the if / else to keep it from going negative or up eternally.  I am not sure if this is something that should be in the php forum or mysql cause I don't know if it is something I will do in the programming or in the query.

 

<form action="<?php echo $editFormAction; ?>" method="POST" name="up" id="up">
                          <input name="doc_cat_order" type="hidden" id="doc_cat_order" value="<?php 
					  $order_down = $row_doc_category['doc_cat_order'];
					  if ($order_down == 1) {
					  echo ($row_doc_category['doc_cat_order']);}
					  else {
					  echo ($row_doc_category['doc_cat_order'] - 1);
					  } ?>">
                          <input name="doc_cat_id" type="hidden" id="doc_cat_id" value="<?php echo $row_doc_category['doc_cat_id']; ?>">
                          <input name="submit2" type="image" src="/images/arrow_up_blue.png" alt="Move Up" width="16" height="16">
                          <input type="hidden" name="MM_update" value="up">

                                                                                                </form>
                        </td>
                        <td class="icon_box"><form action="<?php echo $editFormAction; ?>" method="POST" name="down" id="down">
                          <input name="doc_cat_order" type="hidden" id="doc_cat_order" value="<?php 
					  $order_down = $row_doc_category['doc_cat_order'];
					  $count = $row_cat_count['TOTAL'];
					  if ($order_down == $count) {
					  echo ($row_doc_category['doc_cat_order']);}
					  else {
					  echo ($row_doc_category['doc_cat_order'] + 1);
					  }
					  
					  
					  ?>">
                          <input name="doc_cat_id" type="hidden" id="doc_cat_id" value="<?php echo $row_doc_category['doc_cat_id']; ?>">
                          <input name="submit" type="image" src="/images/arrow_down_blue.png" alt="Move Down" width="16" height="16">
                          <input type="hidden" name="MM_update" value="down">

                                                                                                </form>

[fenway]

Sounds you just want to update the sortorder of all displayed items when you submit....

[dprichard]

Yes, but the issue I am having is this.  Say I have three books

 

Book ID: 1

Book Name: First Book

Book Sort Order: 1

 

Book ID: 2

Book Name: Second Book

Book Sort Order: 2

 

Book ID: 3

Book Name: Third Book

Book Sort Order: 3

 

I pull them up on the page ORDER BY Sort Order.  I want to give the user the ability to click on the up arrow and move book three up above book 2.  The way I am doing it now is by subtracting one from the sort order and refreshing the page.  The problem I am running into is that it takes two clicks to move up the bottom item or move down the top item because when I click on the up arrow it makes the Book Sort Order for book 3 a 2 which makes it equal to book 2.  Sorry, I am just learning to work with real PHP and not Macromedia PHP so I am not sure if I am explaining it that well or how to get around this issue.

 

Thanks in advance for any help!

[fenway]

I understand... why not have a sortorder dropdown, first, second, third, fourth, etc... and have it prepopulated correctly, then the user can change it, and you can save back fixed/defined values.

[bubblegum.anarchy]

hmm... what about a sort order based on linked lists.

 

using a < prev_book_id > instead of a sort order value

 

book.id = 1

book.name = 'First Book'

book.prev_book_id = 0

 

book.id = 2

book.name = 'Second Book'

book.prev_book_id = 1

 

book.id = 3

book.name = 'Third Book'

book.prev_book_id = 2

 

grab the ordered list with:

 

SELECT * FROM book ORDER BY prev_book_id

 

moving the book either way would require:

    the selected books prev_book_id to be set to the prev_book_id of the shifted book

 

and the next requirements depend on the direction of the shift.. as is to shift up and switched to shift down

 

    the shifted books prev_book_id to be set to the id of the selected book

    and the prev_book_id of the book that references the selected book to be set to the id of shifted book

 

well something like that anyway.

[fenway]

Linked lists are so horrible in a DB... hierachal data is bad too.