Custom list order

[Guest]

Hi there,

 

I have checked 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?

[Guest]

No one can help me on this?... I managed to do brilliant things when I did not have the "display by category" issue but now, I'm really struggling with the code...

[Guest]

I did some changes to the code but it does not seem to work. Here is my code:

 

<?php
if (isset($_GET['dir'])) {
if ($_GET['dir'] && $_GET['position']) {
   $dir = $_GET['dir'];
   $position = (int) $_GET['position'];
   switch ($dir) {
      case 'up': 
		 $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;
      case 'down':
         $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);
         $swap = ($position < $max)? $result2 : $max;
         break;
      default:
         $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());
} 
}				

if (isset($_GET['del'])) {
if ($_GET['del'] == 'true') {
   $position = (int) $_GET['position'];
   $sql = "DELETE FROM subjects WHERE position = '$position'";
   $result = mysql_query($sql) or die(mysql_error());
   $sql = "SELECT menu_name FROM subjects ORDER BY position";
   $result = mysql_query($sql) or die(mysql_error());
   $usort = 1;
   while ($r = mysql_fetch_assoc($result)) {
      $name = $r['menu_name'];
      $sql = "UPDATE subjects SET position = '$usort' WHERE menu_name = '$name'";
      $update = mysql_query($sql) or die(mysql_error());
      $usort++;
   }
} 
}

$sql = "SELECT menu_name, position, id, position FROM subjects WHERE category = 1 ORDER BY position DESC";
$result = mysql_query($sql) or die(mysql_error());

echo "<table>";
while ($r = mysql_fetch_assoc($result)) {
   echo "<tr>";
   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> <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>";
} 
echo "</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 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? 

[Guest]

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:

 

<?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> <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...