move row up and down in database

Question

I had develop a ranking system for on of my websites where a rank is assign to every item to move it along the choice (up & down). It works fine.

but the problem is if one record is deleted the record next to it will not be ranked again. but others works fine.

Example.

id | cat name | Rank
 1 | Jackets  | 1
 2 | T-shirts | 2
 3 | Gloves   | 3*
 4 | Scarf    | 4

if i delete the third row (Which is gloves ) the upper rows (Jackets & T-shirts work fine) Similarly the down words. But The Ranking function of scarf will stop now...

Can any one provide simple solution to solve this prob.

Here are the codes........

//============================  UP    ================================//
if(isset($_REQUEST["up"])) {

   $a=$_REQUEST["up"];
   //echo $a; die();
   if($a>1){
        $b=$a - 1 ;

        $id=$_REQUEST["id"];
        //echo $id; die();

        $sql =mysql_query("SELECT * FROM scat WHERE rank ='".$b."'");

        while ($rc= mysql_fetch_array($sql)){
            $c=$rc["sid"];
            $count=mysql_num_rows($sql);

            if($count == 1){

                // update item with already placed id
                $d=mysql_query("update scat set rank='".$a."' where sid='".$c."'");

                // Update up Item 
                $qry=mysql_query("update scat set rank='".$b."' where sid='".$id."' ");

                //header('location:main.php'); break;
            }
        }
    }
}

//============================  Down    ================================//
if(isset($_REQUEST["dwn"])) {

   $a=$_REQUEST["dwn"];
   //echo $a;
   if($a>0) {
        $b=$a + 1 ;

        $id=$_REQUEST["id"];
        //echo $id;die();

        $sql =mysql_query("SELECT * FROM scat WHERE rank='".$b."'");

        while ($rc= mysql_fetch_array($sql)){
            $c=$rc["sid"];

            $count=mysql_num_rows($sql);

            if($count == 1){

                // update item with already placed id
                $d=mysql_query("update scat set rank='".$b."' where sid='".$id."'");

                // Update up Item 
                $qry=mysql_query("update scat set rank='".$a."' where sid='".$c."' ");

                //header('location:main.php'); break;
            }
        }
    }
}

Tables Goes Here....

<table width="600" border="0" cellspacing="1" cellpadding="0" bgcolor="#007ba4">
    <tr>
        <td width="50" align="center" valign="top" bgcolor="#FFF" class="ver_nav">Id</td>
        <td width="350" align="center" valign="top" bgcolor="#FFF" class="ver_nav">Sub Cataagery name</td>
        <td colspan="2" align="center" valign="top" bgcolor="#FFF" class="ver_nav">Rank</td>
        <td width="75" align="center" valign="top" bgcolor="#FFF" class="ver_nav">Feature</td>
        <td width="75" align="center" valign="top" bgcolor="#FFF" class="ver_nav">Edit</td>
        <td width="75" align="center" valign="top" bgcolor="#FFF" class="ver_nav">Del</td>
    </tr>
    <?php $qry=mysql_query( "SELECT * FROM scat WHERE fid='".$_REQUEST[ "fid"]. "' order by rank "); while ($rc=mysql_fetch_array($qry)){ ?>
    <tr>
        <td width="50" align="center" valign="top" bgcolor="#FFF" class="cat">
            <?php echo $rc[ "rank"];?>
        </td>
        <td width="350" align="center" valign="top" bgcolor="#FFF"><a href="add_pimg.php?fid=<?php echo $rc[" fid "];?>&sid=<?php echo $rc["sid "];?>" class="cat"><?php echo $rc["scat"];?></a></td>
        <td width="75" align="center" valign="top" bgcolor="#FFF"> <a href="add_second_cat.php?fid=<?php echo $rc[" fid "];?>&up=<?php echo $rc["rank "];?>&id=<?php echo $rc["sid "];?>" class="cat">Up</a></td>
        <td width="75" align="center" valign="top" bgcolor="#FFF"> <a href="add_second_cat.php?fid=<?php echo $rc[" fid "];?>&dwn=<?php echo $rc["rank "];?>&id=<?php echo $rc["sid "];?>"class="cat">Down</a></td>
        <td width="75" align="center" valign="top" bgcolor="#FFF"> <a href="add_second_cat.php?fid=<?php echo $rc[" fid "];?>&feature=<?php echo $rc["rank "];?>&id=<?php echo $rc["sid "];?>" class="cat"><?php echo $rc["feature"];?></a></td>
        <td width="75" align="center" valign="top" bgcolor="#FFF"><a href="edit_scat.php?fid=<?php echo $rc[" fid "];?>&amp;sid=<?php echo $rc["sid "];?>" class="cat">Edit</a><a href="dell_scat.php?fid=<?php echo $rc[" fid "];?>&amp;sid=<?php echo $rc["sid "];?>" class="cat"></a></td>
        <td width="75" align="center" valign="top" bgcolor="#FFF"><a href="dell_scat.php?fid=<?php echo $rc[" fid "];?>&amp;sid=<?php echo $rc["sid "];?>" class="cat">Dell</a></td>
    </tr>
<?php }?>
</table>

[Please, don't use mysql_* functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) in new code. They are no longer maintained [and are officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). See the [red box](http://uk.php.net/manual/en/function.mysql-connect.php)? Learn about [_prepared statements_](http://en.wikipedia.org/wiki/Prepared_statement) instead, and use [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli) - [this article](http://php.net/manual/en/mysqlinfo.api.choosing.php) will help you decide which. — TNK, Apr 27 '13 at 16:34

score 0 · Answer 1 · answered Apr 27 '13 at 16:35

You need to select a an id to swap with, not calculate it

Here is the code from an awful old project of mine. Looks funny now

  if (isset($_POST['move'])) {
    $id=intval($_POST['move']);
    $place=db("SELECT place FROM $table WHERE id=$id");
    if (!$id OR !$place) die("id or place is not set");

    if (isset($_POST['up'])) {
      $sort=db("SELECT sort FROM $table WHERE id=$id");
      $sort2=db("SELECT max(sort) as msort FROM $table WHERE del=0 AND place=$place AND sort < $sort");
      if ($sort2) $id2=db("SELECT id FROM $table WHERE del=0 AND place=$place AND sort = $sort2");
    }
    if (isset($_POST['down'])) {
      $sort=db("SELECT sort FROM $table WHERE id=$id");
      $sort2=db("SELECT min(sort) as msort FROM $table WHERE del=0 AND place=$place AND sort > $sort");
      if ($sort2) $id2=db("SELECT id FROM $table WHERE del=0 AND place=$place AND sort = $sort2");
    }
    if ($sort2) {
     $q1="UPDATE $table SET sort=$sort2 WHERE id=$id";
      $q2="UPDATE $table SET sort=$sort WHERE id=$id2";
      mysql_query($q1) or trigger_error(mysql_error());
      mysql_query($q2) or trigger_error(mysql_error());
    }
    header("Location: http://".$_SERVER['HTTP_HOST'].$_SERVER['PHP_SELF']."?place=$place"); 
    exit; 
  }

move row up and down in database

1 Answers1