Custom list order

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
krystof78
Forum Newbie
Posts: 10
Joined: Fri Oct 16, 2009 4:08 am

Custom list order

Post by krystof78 »

Hi there,

I have found this tutorial and it's great till the point where I want to display my data by a variable.

Let's say that I have in my table these fields:
- id
- name
- usort
- category

I want to display the information by category. But then, when I use the script described in the tutorial, it does not work properly as it changes the position in the table by +/-1, not with the row I want it to be changed with. Ultimately it will work when it goes up/down the appropriate row.

I guess there should be a way of fixing that by swapping the usort number, but I have to admit that my skills do not allow me to do that.

Does anyone have an idea how I could do what I would like to do?

Thanks in advance for your help.
krystof78
Forum Newbie
Posts: 10
Joined: Fri Oct 16, 2009 4:08 am

Re: Custom list order

Post by krystof78 »

No one can help me on that? :(
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Custom list order

Post by califdon »

krystof78 wrote:No one can help me on that? :(
You are rather impatient. Less than 3 hours and you're complaining? We ask in our forum rules that you should not "bump" your own posts for at least 24 hours. But the real reason you aren't getting any response is that your question is vague and confused. I honestly can't understand what you are saying, in large part.
krystof78
Forum Newbie
Posts: 10
Joined: Fri Oct 16, 2009 4:08 am

Re: Custom list order

Post by krystof78 »

Sorry Califdon, I did not know about that rule. I guess I am so into my project and can't so far find a solution that I got a bit too impatient...

As for my problem, I am going to try to be a bit clearer.

Basically, the script described in the tutorial shows how to move up or down a row in a table by clicking on a link "up" or "Down" next to the row you want to move. In fact, it adds 1 to the position variable of the row that goes down or substract 1 to the position variable of the row that goes up, and then sort the table by this position variable. The end result is like that:
Image

I want to display this table by another variable, say category. So in the example shown above, the line 1 (Test count row 510), 3 (Test count row 78) and 5 (Test count row 9) are displayed when I select only the categories that I want, as shown below:
Image

My problem is that when I click on the "up arrow" of the 3rd line (Test count row 9) for example, it does not move. It does take a up in the entire table, but not in the portion of the table that I want to display. If I have to display all the table again, this line is no longer in 5th position but in 4th. But I would like it to go from 5th position in the table to 3rd position, and the row in 3rd position to go in 5th.

I don't really feel that I have been clearer. Let me know if I made myself a little bit clearer.

Thanks in advance for your help.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Custom list order

Post by califdon »

I still don't understand your description. You could show us your code (please use the Code button in the Post form, and change

Code: Select all

to

Code: Select all

to properly highlight the code) and describe (1) what you want it to do, and (2) what it is doing that you don't like. I'm not getting any clear idea of what your categories have to do with the sorting order.
krystof78
Forum Newbie
Posts: 10
Joined: Fri Oct 16, 2009 4:08 am

Re: Custom list order

Post by krystof78 »

Ok. So here is how my table "subjects" is built:

Image

And here is the code:

Code: Select all

 
<?php
// find out how many rows are in the table 
$sql = "SELECT COUNT(*) FROM subjects";
$result = mysql_query($sql) or trigger_error("SQL", E_USER_ERROR);
$r = mysql_fetch_row($result);
$numrows = $r[0];
 
// number of rows to show per page
$rowsperpage = 20;
// find out total pages
$totalpages = ceil($numrows / $rowsperpage);
 
// get the current page or set a default
if (isset($_GET['currentpage']) && is_numeric($_GET['currentpage'])) {
   // cast var as int
   $currentpage = (int) $_GET['currentpage'];
} else {
   // default page num
   $currentpage = 1;
} // end if
 
// if current page is greater than total pages...
if ($currentpage > $totalpages) {
   // set current page to last page
   $currentpage = $totalpages;
} // end if
// if current page is less than first page...
if ($currentpage < 1) {
   // set current page to first page
   $currentpage = 1;
} // end if
 
// the offset of the list, based on current page 
$offset = ($currentpage - 1) * $rowsperpage;
 
// get the info from the db 
$sql = "SELECT id, menu_name FROM subjects ORDER BY id DESC LIMIT $offset, $rowsperpage";
$result = mysql_query($sql) or trigger_error("SQL", E_USER_ERROR);
 
// if an arrow link was clicked...
if (isset($_GET['dir'])) {
    if ($_GET['dir'] && $_GET['id']) {
       // make GET vars easier to handle
       $dir = $_GET['dir'];
       // cast as int and couple with switch for sql injection prevention for $id
       $id = (int) $_GET['id'];
       // decide what row we're swapping based on $dir
       switch ($dir) {
          // if we're going up, swap is 1 less than id
          case 'up': 
             // make sure that there's a row above to swap
             $swap = ($id > 1)? $id-- : 1;
             break;
          // if we're going down, swap is 1 more than id
          case 'down':
             // find out what the highest row is
             $sql = "SELECT count(*) FROM subjects";
             $result = mysql_query($sql) or die(mysql_error());
             $r = mysql_fetch_row($result);
             $max = $r[0];
             // make sure that there's a row below to swap with
             $swap = ($id < $max)? $id++ : $max;
             break;
          // default value (sql injection prevention for $dir)
          default:
             $swap = $id;
       } // end switch $dir
       // swap the rows. Basic idea is to make $id=$swap and $swap=$id 
       $sql = "UPDATE subjects SET position = CASE position WHEN $id THEN $swap WHEN $swap THEN $id END WHERE position IN ($id, $swap)";
       $result = mysql_query($sql) or die(mysql_error());
    } // end if GET  
}               
// delete from table
if (isset($_GET['del'])) {
    if ($_GET['del'] == 'true') {
       // cast id as int for security
       $id = (int) $_GET['id'];
       // delete row from table
       $sql = "DELETE FROM subjects WHERE position = '$id'";
       $result = mysql_query($sql) or die(mysql_error());
       // select the info, ordering by usort
       $sql = "SELECT menu_name FROM subjects ORDER BY position";
       $result = mysql_query($sql) or die(mysql_error());
       // initialize a counter for rewriting usort
       $usort = 1;
       // while there is info to be fetched...
       while ($r = mysql_fetch_assoc($result)) {
          $name = $r['menu_name'];
          // update the usort number to the one in the next number
          $sql = "UPDATE subjects SET position = '$usort' WHERE menu_name = '$name'";
          $update = mysql_query($sql) or die(mysql_error());
          // inc to next avail number
          $usort++;
       } // end while
    } // end if del
}
 
// pull the info from the table
$sql = "SELECT id, menu_name, position FROM subjects WHERE category = 1 ORDER BY position DESC LIMIT $offset, $rowsperpage";
$result = mysql_query($sql) or trigger_error("SQL", E_USER_ERROR);
 
// display table
echo "<table>";
// display data 1 row at a time
while ($r = mysql_fetch_assoc($result)) {
   echo "<tr>";
   // make the links to change custom order, passing direction and the custom sort id
   echo "<td><a href='{$_SERVER['PHP_SELF']}?dir=down&id={$r['position']}'><img src=\"images/up_1.gif\" alt=\"Move up\" title=\"Move up\" /></a></td>";
   echo "<td><a href='{$_SERVER['PHP_SELF']}?dir=up&id={$r['position']}'><img src=\"images/down_1.gif\" alt=\"Move down\" title=\"Move down\" /></a> </td>";
   echo "<td><a href='{$_SERVER['PHP_SELF']}?del=true&id={$r['position']}&currentpage={$currentpage}' onclick=\"return confirm('Are you sure you want to DELETE this subject?');\"><img src=\"images/delete_1.png\" alt=\"Delete\" title=\"Delete\" /></a></td>";
   echo "<td><a href=\"edit_subject.php?subj=" . urlencode($r["id"]) . "&currentpage=" . $currentpage . "\">" . substr($r['menu_name'], 0, 23) . "</td>";                  
   echo "</tr>";
} // end while $r
echo "</table>";
// end display table
 
/******  build the pagination links ******/
// range of num links to show
$range = 3;
 
// if not on page 1, don't show back links
if ($currentpage > 1) {
   // show << link to go back to page 1
   echo " <a href=\"{$_SERVER['PHP_SELF']}?subj=" . urlencode($r["id"]) . "&currentpage=1" . "\">First</a> ";
   // get previous page num
   $prevpage = $currentpage - 1;
   // show < link to go back to 1 page
   echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$prevpage'>&nbsp;...&nbsp;</a> ";
} // end if 
 
// loop to show links to range of pages around current page
for ($x = ($currentpage - $range); $x < (($currentpage + $range) + 1); $x++) {
   // if it's a valid page number...
   if (($x > 0) && ($x <= $totalpages)) {
      // if we're on current page...
      if ($x == $currentpage) {
         // 'highlight' it but don't make a link
         echo " [<b>$x</b>] ";
      // if not current page...
      } else {
         // make it a link
         echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$x'>$x</a> ";
      } // end else
   } // end if 
} // end for
 
// if not on last page, show forward and last page links        
if ($currentpage != $totalpages) {
   // get next page
   $nextpage = $currentpage + 1;
    // echo forward link for next page 
   echo " <a href=\"{$_SERVER['PHP_SELF']}?currentpage=$nextpage" . "\">&nbsp;...&nbsp;</a> ";
   // echo forward link for lastpage
   echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$totalpages'>Last</a> ";
} // end if
/****** end build pagination links ******/
?>
 
I would like to be able to reorder the lines displayed by clicking on the links "Move Up" or "Move Down". Right now, if I click on a link, it swaps rows that are just next to each other in the table. But it does not work if 2 rows displayed next to each other are in fact far away from each other in the table.

Hope it's clearer like this. Sorry for not being clear in my previous posts. Let me know if you need any other info.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Custom list order

Post by califdon »

OK, now I understand what you're trying to do. The reason it's swapping based on how it's stored in the table (actually it's not, but I don't want to confuse you) is that you are using the id field when you should be using the position field. Since you are displaying them in the order of their position values, that's what you have to swap, not their id's. The id value should never change, it's how you identify a particular record. What you want to change is the position value. Try that and if you have trouble, post here again.
krystof78
Forum Newbie
Posts: 10
Joined: Fri Oct 16, 2009 4:08 am

Re: Custom list order

Post by krystof78 »

I have done some changes to the code but it does not seem to work.

Code: Select all

 
<?php
// if an arrow link was clicked...
if (isset($_GET['dir'])) {
    if ($_GET['dir'] && $_GET['position']) {
       // make GET vars easier to handle
       $dir = $_GET['dir'];
       // cast as int and couple with switch for sql injection prevention for $position
       $position = (int) $_GET['position'];
       // decide what row we're swapping based on $dir
       switch ($dir) {
          // if we're going up, swap is 1 less than position
          case 'up': 
             // make sure that there's a row above to swap
             $query1 = "SELECT position FROM subjects WHERE category = 1 AND position > '$position' ORDER BY position DESC LIMIT 1";
             $result1 = mysql_query($query1);
             $swap = ($position > 1)? $result1 : 1;
             break;
          // if we're going down, swap is 1 more than position
          case 'down':
             // find out what the highest row is
             $sql = "SELECT count(*) FROM subjects";
             $result = mysql_query($sql) or die(mysql_error());
             $r = mysql_fetch_row($result);
             $max = $r[0];
             $query2 = "SELECT position FROM subjects WHERE category = 1 AND position < '$position' ORDER BY position DESC LIMIT 1";
             $result2 = mysql_query($query2);
             // make sure that there's a row below to swap with
             $swap = ($position < $max)? $result2 : $max;
             break;
          // default value (sql injection prevention for $dir)
          default:
             $swap = $position;
       } // end switch $dir
       // swap the rows. Basic idea is to make $position=$swap and $swap=$position 
       $sql = "UPDATE subjects SET position = CASE position WHEN '$position' THEN '$swap' WHEN '$swap' THEN '$position' END WHERE position IN ('$position', '$swap')";
       $result = mysql_query($sql) or die(mysql_error());
    } // end if GET  
}               
// delete from table
if (isset($_GET['del'])) {
    if ($_GET['del'] == 'true') {
       // cast id as int for security
       $position = (int) $_GET['position'];
       // delete row from table
       $sql = "DELETE FROM subjects WHERE position = '$position'";
       $result = mysql_query($sql) or die(mysql_error());
       // select the info, ordering by usort
       $sql = "SELECT menu_name FROM subjects ORDER BY position";
       $result = mysql_query($sql) or die(mysql_error());
       // initialize a counter for rewriting usort
       $usort = 1;
       // while there is info to be fetched...
       while ($r = mysql_fetch_assoc($result)) {
          $name = $r['menu_name'];
          // update the usort number to the one in the next number
          $sql = "UPDATE subjects SET position = '$usort' WHERE menu_name = '$name'";
          $update = mysql_query($sql) or die(mysql_error());
          // inc to next avail number
          $usort++;
       } // end while
    } // end if del
}
 
// pull the info from the table
$sql = "SELECT menu_name, position, id, position FROM subjects WHERE category = 1 ORDER BY position DESC";
$result = mysql_query($sql) or die(mysql_error());
 
// display table
echo "<table>";
// display data 1 row at a time
while ($r = mysql_fetch_assoc($result)) {
   echo "<tr>";
   // make the links to change custom order, passing direction and the custom sort position
   echo "<td><a href='{$_SERVER['PHP_SELF']}?dir=down&position={$r['position']}'><img src=\"images/up_1.gif\" /></a></td>";
   echo "<td><a href='{$_SERVER['PHP_SELF']}?dir=up&position={$r['position']}'><img src=\"images/down_1.gif\" /></a> </td>";
   echo "<td>&nbsp;<a href='{$_SERVER['PHP_SELF']}?del=true&position={$r['position']}'><img src=\"images/delete_1.png\" /></a></td>";
   echo "<td><a href=\"edit_subject.php?subj=" . urlencode($r["id"]) . "\">" . substr($r['menu_name'], 0, 23) . "</td>";                   
   echo "</tr>";
} // end while $r
echo "</table>";
// end display table
?>
 
I did that because I want to display my table by category (here category 1). I thought that by getting the position and finding the position right below or above it in the position column, I could just swap them in order to reorder my list. But what it does in fact, it changes the position of the row to 0.

Any idea what I did wrong?
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Custom list order

Post by califdon »

Well, see, that's an entirely different objective. You are taking an algorithm that depends on every record having a position number within the total list, and you're trying to apply that to a totally different situation, where you want to display only a selected group of records, so it should be no surprise that the algorithm doesn't work. If I had several days to work on it, I could probably write a script that works for the situation you have finally described, but I don't have that much time to devote to it. Sorry.
krystof78
Forum Newbie
Posts: 10
Joined: Fri Oct 16, 2009 4:08 am

Re: Custom list order

Post by krystof78 »

With the help of a friend, I found the solution. I was actually pretty close to the solution. The only problem was that I was sending a sql query (find the number before/after the position) in another sql query (switch the position), which could not work.

What I had to do is set the number found by the 1st query by a mysql_fetch_assoc function and it worked! Here is the code:

Code: Select all

 
<?php
// if an arrow link was clicked...
if (isset($_GET['dir'])) {
    if ($_GET['dir'] && $_GET['position']) {
        // make GET vars easier to handle
        $dir = $_GET['dir'];
        // cast as int and couple with switch for sql injection prevention for $position
        $position = (int) $_GET['position'];
        // decide what row we're swapping based on $dir
        switch ($dir) {
          // if we're going up, swap is 1 less than position
        case 'up': 
            // make sure that there's a row above to swap
            $query1 = "SELECT position FROM subjects WHERE category = 1 AND position > '$position' ORDER BY position ASC LIMIT 1";
            $result1 = mysql_query($query1);
            $r = mysql_fetch_assoc($result1);
            $swap = ($position > 1)? $r['position'] : 1;
            break;
        // if we're going down, swap is 1 more than position
        case 'down':
            // find out what the highest row is
            $sql = "SELECT count(*) FROM subjects";
            $result = mysql_query($sql) or die(mysql_error());
            $r = mysql_fetch_row($result);
            $max = $r[0];
            $query2 = "SELECT position FROM subjects WHERE category = 1 AND position < '$position' ORDER BY position DESC LIMIT 1";
            $result2 = mysql_query($query2);
            $r = mysql_fetch_assoc($result2);
            // make sure that there's a row below to swap with
            $swap = ($position < $max)? $r['position'] : $max;
            break;
        // default value (sql injection prevention for $dir)
        default:
           $swap = $position;
       } // end switch $dir
       // swap the rows. Basic idea is to make $position=$swap and $swap=$position 
       $sql = "UPDATE subjects SET position = CASE position WHEN '$position' THEN '$swap' WHEN '$swap' THEN '$position' END WHERE position IN ('$position', '$swap')";
       $result = mysql_query($sql) or die(mysql_error());
    } // end if GET  
}   
            
// delete from table
if (isset($_GET['del'])) {
    if ($_GET['del'] == 'true') {
        // cast id as int for security
        $position = (int) $_GET['position'];
        // delete row from table
        $sql = "DELETE FROM subjects WHERE position = '$position'";
        $result = mysql_query($sql) or die(mysql_error());
        // select the info, ordering by usort
        $sql = "SELECT menu_name FROM subjects ORDER BY position";
        $result = mysql_query($sql) or die(mysql_error());
        // initialize a counter for rewriting usort
        $usort = 1;
        // while there is info to be fetched...
        while ($r = mysql_fetch_assoc($result)) {
            $name = $r['menu_name'];
            // update the usort number to the one in the next number
            $sql = "UPDATE subjects SET position = '$usort' WHERE menu_name = '$name'";
            $update = mysql_query($sql) or die(mysql_error());
            // inc to next avail number
            $usort++;
        } // end while
    } // end if del
}       
    
// pull the info from the table
$sql = "SELECT menu_name, position, id, position FROM subjects WHERE category = 1 ORDER BY position ASC";
$result = mysql_query($sql) or die(mysql_error());
 
// display table
echo "<table>";
// display data 1 row at a time
while ($r = mysql_fetch_assoc($result)) {
    echo "<tr>";
    // make the links to change custom order, passing direction and the custom sort position
    echo "<td><a href='{$_SERVER['PHP_SELF']}?dir=down&position={$r['position']}'><img src=\"images/up_1.gif\" /></a></td>";
    echo "<td><a href='{$_SERVER['PHP_SELF']}?dir=up&position={$r['position']}'><img src=\"images/down_1.gif\" /></a> </td>";
    echo "<td>&nbsp;<a href='{$_SERVER['PHP_SELF']}?del=true&position={$r['position']}'><img src=\"images/delete_1.png\" /></a></td>";
    echo "<td><a href=\"edit_subject.php?subj=" . urlencode($r["id"]) . "\">" . substr($r['menu_name'], 0, 23) . "</td>";                  
    echo "</tr>";
} // end while $r
echo "</table>";
// end display table
?>
 
Hope it can help some other people... :)
Post Reply