CODEWITHSUNDEEP
phpmysqladdition
clarnp49
clarnp49

Reputation: 53

Priority Row Increase or Decrease based on Selection

I have searched and searched but can't find what I'm after, it's probably super simple.

I have a table that has a priority field to display in order that was chosen. What I want to be able to do I have seen but don't know how to make my code do it.

Take data with priority 1,2,3,4,5,6 and change #5 to 4 and adjust all the other numbers accordingly. So now it would be 1,2,3,4 (old 5),5 (old 4),6.

I'm using PHP, Mysql and for some reason I'm not getting this concept. I can increase/decrease the number but I can't adjust the others around it. So I end up with 1,2,3,4,4,6

Table structure ID, Cat, Title, Priority I only want to increase/decrease in the cat. I'm looking at sorting multiple at a time so it could be 1 (old 5), 2 (old 3), 3 (old 6)... etc

Thanks for the help.

EDIT

Maybe my first explaination wasn't explained well enough. Let's say I have 10 pictures, each with a unique ID and the priority they are given right now is the same as the ID. I can display the images ORDER BY priority ID 1,2,3,4,5,6,7,8,,9,10 Priority 1,2,3,4,5,6,7,8,9,10

But after looking at the pictures I want to change the priority they are shown. SO... I click an arrow that has a link that tells the database to update picture ID 4 with priority 4 to be ID 4 priority 2 because it's more important.

Now my database looks like this. ID 1,2,3,4,5,6,7,8,9,10 Priority 1,2,2,3,5,6,7,8,9,10 because the rest of the priority numbers didn't get changed because I don't know how to make that work.

What I want to do is, after I change one priority to the new one, rearrange the numbers so they are in numerical order after that number because they are less important. How do I do this?

Upvotes: 2

Views: 825

Answers (1)

bestprogrammerintheworld
bestprogrammerintheworld

Reputation: 5520

I hope this following code could give you som help how to solve your problem/challenge.:

<?php
//Say that you've recieved array from db...
$arr = array(); 
$arr[0] = array('id'=>5, 'sortOrder' => 1, 'picture' => 'picture1');
$arr[1] = array('id'=>6, 'sortOrder' => 2, 'picture' => 'picture2');
$arr[2] = array('id'=>7, 'sortOrder' => 3, 'picture' => 'picture3');
$arr[3] = array('id'=>9, 'sortOrder' => 4, 'picture' => 'picture4');
$arr[4] = array('id'=>10, 'sortOrder' => 5, 'picture' => 'picture5');


//Do some sorting...

$executeSQL = down($arr,2); //Tell sortOrder to decrease for picture3 and to increase for picture2 
//Array will change to:
//$arr[1] = array('id'>=6, 'sortOrder' => 3, 'picture' => 'picture2');
//$arr[2] = array('id'>=7, 'sortOrder' => 2, 'picture' => 'picture3');

//$executeSQL returns an array of two sql-statements that you should execute


$executeSQL = up($arr, 1); //Decrease sortorder for picture2 and increase for picture3
//Array will change to:
//$arr[1] = array('id'=>6, 'sortOrder' => 2, 'picture' => 'picture2');
//$arr[2] = array('id'=>7, 'sortOrder' => 3, 'picture' => 'picture3');

//$executeSQL returns an array of two sql-statements that you should execute


echo print_r($arr,true); //Will output the original array

function down(Array &$arr, $index) {
 $origPrev = $arr[$index-1]['sortOrder'];      
 $arr[$index-1]['sortOrder'] = $arr[$index]['sortOrder'];  
 $arr[$index]['sortOrder'] = $origPrev;
 $sql = "UPDATE table SET sortOrder=" . $origPrev . " WHERE id=" . $arr[$index]['id'];
 $sql2 = "UPDATE table SET sortOrder=" . $arr[$index]['sortOrder'] . " WHERE id=" . $arr[$index-1]['id']; 
 return array($sql, $sql2);
}

function up(Array &$arr, $index) {
 $origPrev = $arr[$index+1]['sortOrder'];      
 $arr[$index+1]['sortOrder'] = $arr[$index]['sortOrder']; 
 $arr[$index]['sortOrder'] = $origPrev;
 $sql = "UPDATE table SET sortOrder=" . $origPrev . " WHERE id=" . $arr[$index]['id'];
 $sql2 = "UPDATE table SET sortOrder=" . $arr[$index]['sortOrder'] . " WHERE id=" . $arr[$index+1]['id']; 
 return array($sql, $sql2);
}

?>

Upvotes: 2

Related Questions