Page 1 of 2 12 LastLast
Results 1 to 15 of 20

Thread: Reorder database rows

  1. #1

    Fla Box Reorder database rows

    I am wanting for the user to be able to reorder the pages in the CMS. I have table called 'pages' with values 'id', 'title', and 'maintext'. What is the best way to do this? It would be ideal to just have a button with an arrow that when clicked switches the id of the row above it.

    Thanks!

  2. #2
    (You realize the rows in a database don't mean anything right? If you don't, pick up Database Management Systems third edition and read through the first few chapters to understand how a database works and how SQL fully works.)

    Do you mean perform a query that returns ordered rows based on the title? If you add a date column you can return them in the order of creation and such.

    SELECT title, maintext
    FROM pages
    ORDER BY title ASC
    LIMIT 10
    OFFSET <pageNumber> * 10

    Hmm. Normally you don't use the id like that, but that would work. Swapping the ids seems easy if you want to order things based on id. You could also split things into depths and give each page a parent. (Requires a fun query for select).
    -Main Page
    -Projects
    --Project 1
    Sounds like wordpress.

    I'd probably go with your original idea and just swap IDs.

    // edit wordpress doesn't even allow the user to swap pages
    Last edited by Sirisian; July 22nd, 2009 at 01:47 AM. Reason: might as well add an example

  3. #3
    add a column in your database table... called 'order' as an integer... and let people adjust that instead...

    this will allow you to change the queries, and sort/order it using the ORDER BY in your query like this:

    Code:
    SELECT title, maintext FROM pages ORDER BY `order` ASC
    note that order is encapsulated with ` (this is not a quote) so that it will recognize as a columnname
    Last edited by RvGaTe; July 22nd, 2009 at 07:15 AM. Reason: typo

    You can never underestimate the stupidity of the general public.

  4. #4

    Fla Box

    okay. I realize I didn't explain it right now. I have been researching this problem, and I know that I can order by ASC and DESC, but what I really want to do is have an 'order' field with the values like this:

    1
    2
    3

    and if the user hits the up arrow on 3 it changes 3 to a 2, and 2 to a 3 likewise. In my research I have obtained information that I should use 2 queries, but no one explained how to do this with some code.

  5. #5
    ok... you want code... here we go...

    lets say you have a button that can swap order, it would look something like this:

    HTML Code:
    <a href='?id=<?php echo $row_id;?>&order=up'><img src='up.jpg'/></a>
    when submitted:

    PHP Code:
    <?php
        
    if(isset($_GET['id']) && isset($_GET['order'])){
            
    $id intval($_GET['id']);
            
    $order intval($_GET['order']);
            
    $neworder $order == "up" ? -1;
            
            
    // get the current order from the item
            
    $result mysql_query("SELECT `order` FROM pages WHERE id='".$id."'");
            while(
    $row mysql_fetch_array($result)){
                
    $currentOrder $row['order'];
            }
            
            
    // get the row id and order to swap with
            
    $neworder $currentOrder $neworder;
            
    $result mysql_query("SELECT `id`, `order` FROM pages WHERE `order`='".$neworder."'");
            while(
    $row mysql_fetch_array($result)){
                
    $swapRowId $row['id'];
                
    $swapRowOrder $row['order'];
            }
            
            
    // swap them if possible!
            
    if($swapRowId && $swapRowOrder && $id && $currentOrder){
                
    mysql_query("UPDATE pages SET `order`='".$swapRowOrder."' WHERE id='".$id."'");
                
    mysql_query("UPDATE pages SET `order`='".$currentOrder."' WHERE id='".$swapRowId."'");
            }
        }
    haven't tested, but should work something like that

    You can never underestimate the stupidity of the general public.

  6. #6
    okay. Thanks for the code. I have fixed it for my needs, but I am getting "Resource id #10" when I echo the query. How do I fix this?

  7. #7
    Do not echo the resource... ? (would be easyer to answer if we can see the actual code you're trying to echo)

    You can never underestimate the stupidity of the general public.

  8. #8
    Well I only echoed it to see what was coming out cause the code wasn't doing anything. Here is the code. I modified it to grab the title and id of the page, and then changed the id.

    PHP Code:
        if(isset($_GET['title']) && isset($_GET['id'])){ 
            
    $id_order intval($_GET['title']); 
            
    $order intval($_GET['id']); 
            
    $neworder $order == "up" ? -1
             
            
    // get the current order from the item 
           
    $query_order  "SELECT `id` FROM pages WHERE title='".$id_order."'";
            
    $result mysql_query($query_order); 
            while(
    $row mysql_fetch_array($result)){ 
                
    $currentOrder $row['id']; 
                
            } 
             
            
    // get the row id and order to swap with 
            
    $neworder $currentOrder $neworder
            
    $result mysql_query("SELECT `title`, `id` FROM pages WHERE `id`='".$neworder."'"); 
            while(
    $row mysql_fetch_array($result)){ 
                
    $swapRowId $row['title']; 
                
    $swapRowOrder $row['id']; 
            } 
             echo 
    $result;
            
    // swap them if possible! 
            
    if($swapRowId && $swapRowOrder && $id_order && $currentOrder){ 
                
    mysql_query("UPDATE pages SET `id`='".$swapRowOrder."' WHERE title='".$id_order."'"); 
                
    mysql_query("UPDATE pages SET `id`='".$currentOrder."' WHERE title='".$swapRowId."'"); 
            } 
        } 

  9. #9
    Like we suggested before, its a bad idea to modify the id column (because its unique for every row)... add an order column instead and play around with that

    You can never underestimate the stupidity of the general public.

  10. #10
    well I guess the problem I was running into was how to put order into the database when I create a new page, but I guess I can use time() or a rand() or something?

  11. #11
    Quote Originally Posted by drummer392 View Post
    well I guess the problem I was running into was how to put order into the database when I create a new page, but I guess I can use time() or a rand() or something?
    The problem is, that you do not have the order field in your table... add it! and use that to change the order of your pages... using time/random/id is just... meh... not the way to go

    ps. did you even read/try the suggestions?
    Last edited by RvGaTe; July 23rd, 2009 at 06:15 PM. Reason: added ps

    You can never underestimate the stupidity of the general public.

  12. #12

    Fla Box

    okay, so I've got the order, and the information is in that field, but now when I click 'up' the values don't switch. I used your code and didn't change anything cause it matched up.

    What am I doing wrong?

  13. #13
    You cant just assume the code provided by someone else works 1on1 with your application, the code given is merely to guide you and to understand how it works... i also stated that i didn't test the code...

    Try to understand whats happening, tweak around with it, try coding your own version, test it... you're here to learn

    You can never underestimate the stupidity of the general public.

  14. #14
    okay. So I may have some questions so that I may learn a bit more. I understand most of what the code is doing.

    So here they are:

    I think this is telling it to subtract a number from the current order or something, but if that is true, then the function would "down" wouldn't it?
    PHP Code:
            $neworder $order == "up" ? -1

    $currentOrder $row['order']; 

            
    $neworder $currentOrder $neworder
    I think if I knew how that all worked it would solve the mystery in my head, because I have looked at the code and it appears to look like it should function.


    Last edited by drummer392; July 25th, 2009 at 03:25 PM.

  15. #15
    PHP Code:
    $neworder $order == "up" ? -1
    If the $order equals "up", the value of $neworder is set to -1, if its not "up", it will be set to 1... this one liner is the same as doing:

    PHP Code:
    if($order == "up"){
        
    $neworder = -1;
    } else {
        
    $neworder 1;

    the reason why this is not working for you is, assuming this thread, that you are using the time();... when using time, there will be gaps in between the order numbers of your pages...

    to explain that:
    PHP Code:
    $neworder $currentOrder $neworder;
    $result mysql_query("SELECT `title`, `id` FROM pages WHERE `id`='".$neworder."'"); 
    will set the new order with $currentOrder + 1, or $currentOrder -1... and will do a search query to find the record with that order number. but because you used time();. the orders will have gaps (because most likely, the new items will not be created with 1 second apart)...

    to fix this, set the order manually to 1, 2, 3, 4, 5 (using phpmyadmin?) then test the code if it still works...

    You can never underestimate the stupidity of the general public.

Page 1 of 2 12 LastLast

Thread Information

Users Browsing this Thread

There are currently 3 users browsing this thread. (0 members and 3 guests)

Posting Permissions

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

Home About kirupa.com Meet the Moderators Advertise

 Link to Us

 Credits

Copyright 1999 - 2012