[Resolved] Change Order of Database Records

[partypete]

Hello,

(This question is a little long in hopes of making it clear what I'm trying to achive... please still read it!! 😉 )

I need to be able to manually change the order in which database records are displayed. Unfortunately I'm not talking about simply ordering them by column type.

Let's say I have a client database with 2 columns:

client_id - integer (auto_increment)
client_name - varchar

With this table I could sort the records by the client_id or by the client_name... BUT I would like to be able to manually change the order. To do this I would like to add another column such as:

client_sortorder - integer

This column's purpose would be to hold a sequential number which would be the order I want the records displayed on a web page.

Without the client_sortorder column the records would be displayed something like:

client_id     client_name
1             Amy
2             Bob
3             Chris

Let's say I would like "Chris" displayed before "Bob"... there no simple way to do this. So I would like to use the client_sortorder column to control the order the records will be displayed:

client_id     client_name     client_sortorder
1             Amy             1
2             Bob             3
3             Chris           2

Now I can control it so "Chris" will display before "Bob".

The PROBLEM... I don't know how to edit client_sortorder column so that I can get it filled with sequential numbers.. and to change each of these sequential numbers the next time I want a record displayed in a different position.

I'd like to have all the records displayed in a list, and beside each record to have an UP/DOWN arrow which would move the record above or below the records over and under it and then update the client_sortorder column so they will continue to be displayed in that order.

Any ideas on how to edit each of the record's sequential numbers in the client_sortorder column to achieve this????

THANK YOU!

Peter

[edcellgavin]

granting u have the client_sortorder field added to the table, and using mysql as ur db, try this

-query to list sorted records
$query = "SELECT client_id, client_name FROM Client ORDER BY client_sortorder, client_id"

• populate a listbox from query result
  $string = "<select name=\"client\">";
  while(list($id, $name) = mysql_fetch_row($result)) {
  $string = "<option value=\"$id\">$name</option>";
  }
  $string = "</select>";

• add a script (perhaps javascript) to sort out the position of the items in the list, with altered item retaining its value.

• add an 'save sortorder' command button onclick() function which will loop thru the listbox content, store a pipe-delimited string of values(client_id) into a hidden text field and submit the form to the next script

<?php
// update_records.php
$records = explode("|", $_POST['hidden_field']);
foreach($records AS $key=>$id) {
// first key is zero, so add 1
$query = "UPDATE Client SET client_sortorder = $key + 1 WHERE client_id = '$id'";
mysql_query($query);
}
?>

[partypete]

Hello edcellgavin,

Thank you for your reply!

While waiting, my mind kept turning and this is what I came up with, should it work?(Although yours is prettier... I'm not great at arrays):

Lowering sortorder
Set Variables...
client_id = 5 ($rid)
client_sortorder = 9 ($old_order)
Lower client_sortorder to 3 ($new_order)

if ($new_order < $old_order){
UPDATE table SET client_sortorder = client_sortorder + 1 WHERE client_sortorder >= $new_order AND $old_order >= client_sortorder
UPDATE table SET client_sortorder = $new_order WHERE client_id = $rid
}

Raising sortorder
Set Variables...
client_id = 5 ($rid)
client_sortorder = 3 ($old_order)
Raise client_sortorder to 9 ($new_order)

if ($new_order > $old_order){
UPDATE table SET client_sortorder = client_sortorder - 1 WHERE client_sortorder <= $new_order AND $old_order <= client_sortorder 
UPDATE table SET client_sortorder = $new_order WHERE client_id = $rid
}

(The above code is not complete of course...)

To set the variables I too would use a drop menu beside each record. The client_id and current client_sortorder number would be in hidden fields. The drop list would be populated from the database with the numbers from the client_sortorder column as well as two other options:

Up One
Down One
1
2
3
4
5
6
7
...etc...

Thanks again!!!!
Peter

[leatherback]

Hi Peter,

Yes. That way would work. Actually.. I think the idea suggested by edcell might not work, since you can only increase the record identifiers. So if you have a lot of edits, your sortorder runs out of bounds. But I have not tested it.

I have not gone through your whole code to see wether the specific code works. But the idea works. I use it in my menu-edit scipt, where I pass the record number I want to place the current entry below, or negative if it has to go on to of the list:

if(***)  // form was submitted
  {

  $below = getvar('belowwhich', -1, 1);

  if($below == -1) // place at top of menu
    {
    $query = "select * from $menu_table where M_page_nr = $menu_parent order by 'sortorder' desc limit 1";
    echo $query;
    $result = mysql_query($query) or die('Something went wrong when resorting in resort.q1: '.mysql_error());
      while($row = mysql_fetch_array($result))
        {
        $sort_location_nw = $row['M_sortorder'];
        }

$move_rest_query = "update $menu_table 
                    set M_sortorder = M_sortorder+1 
                    where (M_sortorder >= $sort_location_nw)
                    and (M_p_nr = $menu_parent)";    
}
  else
    {
    $sort_location_nw = $below;


  if($sort_location_nw >= $sortorder)  // If the new location is below the old location, shift up
    {
    $move_rest_query = "update $menu_table 
                        set M_sortorder = M_ortorder-1 
                        where (M_sortorder > $sortorder) 
                        and (M_sortorder <= $sort_location_nw)
                        and (M_p_nr = $menu_parent) ";
    }
  else                                 // If the new location is below the new location, shift down
    {
    $move_rest_query = "update $menu_table 
                        setM_sortorder = M_sortorder+1 
                        where (M_sortorder) > ($sort_location_nw) 
                        and (M_sortorder < $sortorder) 
                        and (M_p_nr = $menu_parent)";
    }
    }
    echo $move_rest_query;
    $move_rest = mysql_query($move_rest_query) or die('Could not resort the table in resort.q2'.mysql_error());

$movepage = "update $menu_table 
                    set M_sortorder = $sort_location_nw 
                    where M_page_nr = $pageid";
                    echo $movepage;
$moved_page = mysql_query($movepage);                    


  }

[edcellgavin]

Thanks for the feedback, i really appreciate it. Though, i thought pete would be looking for a practical and simple solution at the time being.
I also made a similar code but my 'users' wanted batch updates. The way i figured, loading the whole table in the list is not the way to go What i did was to limit the number of records to be loaded in the list and have an offset value to move from a set of records to another. With that, you can load the first nth records in the table, conveniently maneuver the records in the list(thru js), update the records and navigate to the next/previous batch of records by adjusting the offset. It came very handy in my case.
I also thought that pete could figure it out.
cheers

[leatherback]

Hi Edd,

no worries. It is sometimes a bit hard tyo judge what people want. SO I try to give my idea when I feel it may add, and show where it is different from other peoples code.

Hope you're OK..

J.

[partypete]

edcellgavin, leatherback,

Just wanted to thank you both for all your help. Although the route which I mentioned and leatherback expanded upon will solve my immediate situation, I will be looking into the ideas you suggested edcellgavin. It would be convenient and faster to sort all the records via javascript rather than sorting each record one at a time. But this approach will take me some time to learn all the details. I'm not a great coder, but I'm tryin' !

Thanks guys!

Peter