Product Order Script

[son]

I have this great script which orders products, so they can be displayed in certain order. Now, the number of products increased and I inserted a pre-selection of category before all relevant products being displayed. The working code is:

if (isset($_GET['cid'])) {$cid = $_GET['cid'];echo "<p>Please use arrows to change order of products.</p>";if (isset($_GET['dir']))		{// if an arrow link was clicked...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 products";         $result = mysqli_query($dbc, $sql);         $r = mysqli_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 products SET usort = CASE usort WHEN $id THEN $swap WHEN $swap THEN $id END WHERE usort IN ($id, $swap) AND product_id IN (SELECT product_id FROM productCat WHERE category_id = $cid)";   $result = mysqli_query($dbc, $sql);} // end if GET } // set a result order with a default (sql infection prevention for $sortby)if (isset($_GET['sortby']))		{	$sortby = $_GET['sortby'];	}		else		{	$sortby = 'usort';	}// pull the info from the table$sql = "SELECT usort, product, img1 FROM products WHERE product_id IN (SELECT product_id FROM productCat WHERE category_id = $cid) ORDER BY $sortby";$result = mysqli_query($dbc, $sql);// display tableecho "<table border = '1'>";// display data 1 row at a time$productPath = "../products/basket/";while ($r = mysqli_fetch_assoc($result)) {$img1 = $r['img1'];$size = getimagesize("../products/basket/{$img1}");   echo "<tr>";   // make the links to change custom order, passing direction and the custom sort id   echo "<td><img src=\"" . $productPath . "/" . $img1 . "\" " . $size[3] . " alt=\"{$r['product']}\" title=\"{$r['product']}\" /></td>";   echo "<td align = 'center'><a href='product_order.php?cid=$cid&dir=up&sortby=usort&id={$r['usort']}'>/\</a> ";   echo "<a href='product_order.php?cid=$cid&dir=down&sortby=usort&id={$r['usort']}'>\/</a></td>";   echo "<td>{$r['product']}</td>";   echo "</tr>";} // end while $recho "</table>";// end display tableecho "<p style=\"margin-top:20px; font-size:90%;\">Display data  by:  <a href='{$_SERVER['PHP_SELF']}?cid=$cid&sortby=usort'>order</a> | <a href='{$_SERVER['PHP_SELF']}?cid=$cid&sortby=product'>product name</a><p>";}else{$query = "SELECT category_id, parent_id, category FROM categories WHERE parent_id = 0 ORDER BY category_id";$result = mysqli_query ($dbc, $query);if (mysqli_num_rows($result) > 0)echo "<p>Please select the relevant category.</p>";{	while ($row = mysqli_fetch_array ($result, MYSQLI_ASSOC))		{	$parent = $row['category_id'];	$subQuery = "SELECT  category_id, category FROM categories WHERE parent_id = $parent AND category_id IN (SELECT category_id FROM productCat WHERE product_id IN (SELECT product_id FROM products WHERE room_scene = 0)) ORDER BY category_id";	$subResult = mysqli_query ($dbc, $subQuery);		if (mysqli_num_rows($subResult) > 0) 		{		$category = $row['category'];		echo "<h2 style=\"margin-bottom:5px;\">" . $category . "</h2>\n";			echo "<ul class=\"categories\">\n";			while ($row = mysqli_fetch_array ($subResult, MYSQLI_ASSOC))				{			$categorySub = $row['category'];			$category_id = $row['category_id'];			echo "<li><a href=\"product_order.php?cid=" . $category_id . "\" title=\"" . $categorySub . "\">" . $categorySub . "</a></li>\n";			}		echo "</ul>\n";		}		}}}

Now, my problem: the products are not entered in any particular order. That means that I could enter first three products in photos, then one in small items and then five in more. Using count it will bring the number of rows in table. This is of no use any more. I have now to get the highest and lowest usort of relevant category and then only swap with relevant items of same category. How do you do this?Son

[son]

Just after posting the solution came to my small brains:

case 'up':          // make sure that there's a row above to swap		 $sql1 = "SELECT usort FROM products WHERE product_id IN (SELECT product_id FROM productCat WHERE category_id = $cid) ORDER BY usort ASC LIMIT 1";         $result1 = mysqli_query($dbc, $sql1);         $r1 = mysqli_fetch_row($result1);         $min = $r1[0];         $swap = ($id > $min)? $id-- : $min;         break;      // if we're going down, swap is 1 more than id      case 'down':         // find out what the highest row is         $sql = "SELECT usort FROM products WHERE product_id IN (SELECT product_id FROM productCat WHERE category_id = $cid) ORDER BY usort DESC LIMIT 1";         $result = mysqli_query($dbc, $sql);         $r = mysqli_fetch_row($result);         $max = $r[0];         // make sure that there's a row below to swap with         $swap = ($id < $max)? $id++ : $max;         break;

If there is a better way, let me know... Otherwise I am quite happy now...Son:-)