Swap Sort Number With Relevant Items Only

[son]

I have the following working code:

$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;

As all products (in any category) are stored in same table I would like to decrease to next usort of product in same category. Just going one down will go through quite a lot of unrelevant items. How can this be done?Son

[justsomeguy]

What do you mean, you're already getting the lowest value for usort. I don't understand what your question is.

[son]

What do you mean, you're already getting the lowest value for usort. I don't understand what your question is.

I re-phrase. Line '$swap = ($id > $min)? $id-- : $min;' refers to correct minimum. That is right. Still, it will decrease $id by one if it has not reached the minimum ($min). I would like to skip items in different categories. Lets say we have item 6 and 4 in photo category and item 5, 3,2 ,1 in prints. When going down for item 6 I want to skip item 5 as it is in different category. If I do not skip unrelated items I might end up going through 50 items just to change order for one item.Hope my explanation is more precise.Son

[justsomeguy]

Maybe I'm dense, but I'm still not understanding. Your query is getting the single lowest value of usort for all products in a single category. So there's going to be 1 value of usort, and it's going to be the lowest value for usort in 1 category. I'm not sure where you're looping over several products in several categories. Are you leaving out important code?

[son]

Maybe I'm dense, but I'm still not understanding. Your query is getting the single lowest value of usort for all products in a single category. So there's going to be 1 value of usort, and it's going to be the lowest value for usort in 1 category. I'm not sure where you're looping over several products in several categories. Are you leaving out important code?

With my code it still goes through all products when it increases or decreases (as long as it is not eqaul to min or max). The complete relevant 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		 $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;      // 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);

Any ideas where I need to amend?Son

[justsomeguy]

With my code it still goes through all products

I don't see how it's doing that, I see several if statements and a switch statement, there aren't any loop statements.I'm confused about the use of the ID. If the ID is the row that you're trying to reorder, I don't see how it has to do with the sort order, the sort order and ID should be unrelated. This is one algorithm I use to reorder fields, there are separate sections for moving it up one, down one, to the top, or to the bottom. The tid is the ID of the item to move. It looks up the sort order for that ID in the database and reorders things. The pid is sort of a category ID, it's for the project. This is to reorder a task for a particular project, so the pid is the project ID and the tid is the task ID. This is ASP/Jscript, but it's the same thing you're trying to do:

  case 'move_task_up':	pid = get_form_var("pid");	tid = get_form_var("task");	pid = parseInt(pid, 10);	tid = parseInt(tid, 10);	dbcon.open("SELECT disp_order FROM proj_schedule WHERE id=" + tid);	disp_order = dbcon.fields.item("disp_order").value;	dbcon.close();		dbcon.open("UPDATE proj_schedule SET disp_order=disp_order + 1 WHERE project=" + pid + " AND disp_order=" + (disp_order - 1));	dbcon.open("UPDATE proj_schedule SET disp_order=disp_order - 1 WHERE id=" + tid);	if (disp_order <= 1)	{	  dbcon.open("UPDATE proj_schedule SET disp_order=disp_order + 1 WHERE project=" + pid);	}  break;  case 'move_task_top':	pid = get_form_var("pid");	tid = get_form_var("task");	pid = parseInt(pid, 10);	tid = parseInt(tid, 10);	dbcon.open("SELECT disp_order FROM proj_schedule WHERE id=" + tid);	disp_order = dbcon.fields.item("disp_order").value;	dbcon.close();		dbcon.open("UPDATE proj_schedule SET disp_order=disp_order + 1 WHERE project=" + pid + " AND disp_order<" + (disp_order));	dbcon.open("UPDATE proj_schedule SET disp_order=1 WHERE id=" + tid);  break;  case 'move_task_down':	pid = get_form_var("pid");	tid = get_form_var("task");	pid = parseInt(pid, 10);	tid = parseInt(tid, 10);	dbcon.open("SELECT disp_order FROM proj_schedule WHERE id=" + tid);	disp_order = dbcon.fields.item("disp_order").value;	dbcon.close();		dbcon.open("UPDATE proj_schedule SET disp_order=disp_order - 1 WHERE project=" + pid + " AND disp_order=" + (disp_order + 1));	dbcon.open("UPDATE proj_schedule SET disp_order=disp_order + 1 WHERE id=" + tid);  break;  case 'move_task_bottom':	pid = get_form_var("pid");	tid = get_form_var("task");	pid = parseInt(pid, 10);	tid = parseInt(tid, 10);	dbcon.open("SELECT disp_order FROM proj_schedule WHERE id=" + tid);	disp_order = dbcon.fields.item("disp_order").value;	dbcon.close();	dbcon.open("SELECT MAX(disp_order) AS num FROM proj_schedule WHERE project=" + pid);	max_disp = dbcon.fields.item("num").value;	dbcon.close();	dbcon.open("UPDATE proj_schedule SET disp_order=disp_order - 1 WHERE project=" + pid + " AND disp_order>" + (disp_order));	dbcon.open("UPDATE proj_schedule SET disp_order=" + max_disp + " WHERE id=" + tid);  break;

Here's some other code written in PHP. This also reorders items, but this algorithm is for reordering an item by any number of places, either up or down. The delta variable tells it which direction and how many places (-2 = move item up 2 places, +3 = move item down 3 places, etc), and the fid variable is the ID of the item to move:

<?phpif (!$sess->main_admin){  $response['errors']['reason'] = 'You do not have permission.  Your log-in session might have expired.';  return;}$fid = form_var('fid');$delta = intval(form_var('delta'));$db->sql('SELECT disp_order FROM user_fields WHERE id=%s');$db->add_param($fid);$field = $db->select();// current sort order$old = $field[0]['disp_order'];// new sort order$new = $old + $delta;if ($delta < 0) // move up in list{  // update fields that were above this one and are now below  $db->sql('UPDATE user_fields SET disp_order = disp_order + 1 WHERE disp_order < %d AND disp_order >= %d');  $db->add_param($old, false);  $db->add_param($new, false);  $db->exec();  $db->update('user_fields', array('disp_order' => $new), "id='" . $db->escape($fid) . "'");}if ($delta > 0) // move down in list{  // update fields that were below this one and are now above  $db->sql('UPDATE user_fields SET disp_order = disp_order - 1 WHERE disp_order > %d AND disp_order <= %d');  $db->add_param($old, false);  $db->add_param($new, false);  $db->exec();  $db->update('user_fields', array('disp_order' => $new), "id='" . $db->escape($fid) . "'");}$response['success'] = true;?>

[son]

Many thanks for your code. Unfortunately, it does not solve my problem. If I press the up arrow I want the item to swap the usort number (db) with item that is in same category, not the one direcly above (unless it happens to be in same category). The same applies to going down: it should only swap with items that are in same category. Could I maybe somehow get all items in selected category, store them in array (in usort order) and then go through those somehow? Son

[justsomeguy]

You could use an array if you want, I guess I'm a little confused about what you're doing though. I guess your sort just conceptually doesn't make much sense to me, it seems to me that if you sort an item up you would want to move it before whatever item is before it. The first piece of code I showed does use categories, that's what the pid is.

[son]

You could use an array if you want, I guess I'm a little confused about what you're doing though. I guess your sort just conceptually doesn't make much sense to me, it seems to me that if you sort an item up you would want to move it before whatever item is before it. The first piece of code I showed does use categories, that's what the pid is.

It is a bit confusing, I am sorry that I am not good in explaining. Will try it again: the thing is that there are hundreds of products in product table and the usort number serves the purpose to order products on the category overview page (actual website). Therefore, in product admin area it would be rather cumbersome to have to move a product up/down by one when there are potentially 300 products in between. Does this make more sense to you?Son

[justsomeguy]

it would be rather cumbersome to have to move a product up/down by one when there are potentially 300 products in between.

What are you trying to do then, are you trying to pick 2 products and just swap their sort numbers?

[son]

What are you trying to do then, are you trying to pick 2 products and just swap their sort numbers?

I was trying to determine which product of same category is next up/down in product table (via usort number), so product can swap number with this one...Son

[justsomeguy]

That's what the first piece of code in post 6 is doing. That code is reordering scheduled tasks inside a project, instead of products inside a category. It's the same type of relationship.This assumes that each category has its own sort numbering. There might be several items in the database that all have the sort order "5" if they're all in different categories, that just means that each one shows up fifth in its own category. If you have it numbered where the items in one category aren't sequential, the sort orders for items in one category might be 10, 23, 54, 68, etc, not in series, then I recommend you change that so that the items in each category are numbered sequentially. Then you can use the code in post 6 as an example.

[son]

That's what the first piece of code in post 6 is doing. That code is reordering scheduled tasks inside a project, instead of products inside a category. It's the same type of relationship.This assumes that each category has its own sort numbering. There might be several items in the database that all have the sort order "5" if they're all in different categories, that just means that each one shows up fifth in its own category. If you have it numbered where the items in one category aren't sequential, the sort orders for items in one category might be 10, 23, 54, 68, etc, not in series, then I recommend you change that so that the items in each category are numbered sequentially. Then you can use the code in post 6 as an example.

Looks like I should order in table with product-category relationship instead of in table with all products. Did I get this right? If so, maybe I can just slightly modify my code. What do you think?Son

[justsomeguy]

I've got it set up where I have one projects table (your categories), and one scheduled tasks table (your products). My tasks table has an ID for each task, the ID of the project it's a part of, other data like description, dates, etc, then the display order. When I add a new task to a project I check for the largest display order for that project, and give the new task one more than that (largest + 1). So if I've got 3 projects with 3 tasks each, the tables might look like this:

projectsid	title-----------1	 proj 12	 proj 23	 proj 3tasksid	project	 title	 disp_order--------------------------------------1	 1		   task 1a   12	 1		   task 2a   23	 1		   task 3a   34	 2		   task 1b   15	 2		   task 2b   26	 2		   task 3b   37	 3		   task 1c   18	 3		   task 2c   29	 3		   task 3c   3

So there are several tasks with the same disp_order, but they're all in different projects. The tasks in each project have sequential disp_orders, which makes the reordering easy.

[son]

Understood. But my situation is slightly different as I have a n:n relationship. One product can be in several categories, one category can hold several products. For this relationship I have a table as:id, category_id, product_idThis is what makes it so difficult for me to understand.Son

[justsomeguy]

For this relationship I have a table as:id, category_id, product_id

That's the table you should have the sort order on, so that each product can have a different sort order in each category. As you have it set up now, if you have 100 products in the table do the values of usort just go from 0 to 100?For what it's worth, whenever I write a sort like this I typically just draw it out. I'll get out some paper and just write out a list of items and give them sort values and IDs, and then say that if I want to move a certain item to a certain place, which objects needs to change. I'll write out a couple things like that and then look for the pattern to write the algorithm.

[son]

That's the table you should have the sort order on, so that each product can have a different sort order in each category. As you have it set up now, if you have 100 products in the table do the values of usort just go from 0 to 100?For what it's worth, whenever I write a sort like this I typically just draw it out. I'll get out some paper and just write out a list of items and give them sort values and IDs, and then say that if I want to move a certain item to a certain place, which objects needs to change. I'll write out a couple things like that and then look for the pattern to write the algorithm.

Pen and paper, that is a good idea. Have never really done this before and will certainly try this weekend to work it out in theory (hopefully also write code that does what I am after;-)). I will let you know how I am getting on (via this post).Son

[justsomeguy]

Yeah, never underestimate pen and paper. I've diagrammed many a sorting algorithm out before implementing it. Sorting algorithms in particular are something that I have a hard time visualizing without a diagram.