manual sorting using PHP MySQL (Up/Down buttons)?

Question

i m trying to do a manual ordering (sorting) of items in my table not quite good at it and can't seem to figure it out so would like some help. basically there's a column called 'sort' that stores integers,

    pid       |    pid       |   sort
    ----------+--------------+-------
    Alvin     |     1001     |    1
    Tom       |     1002     |    2
    Jerry     |     1003     |    3

i need help with the codes to repositioning the rows... i.e. I m using php to parse variables via URL sort.php?do=up&pid=1002&sort=2

Clicking "UP" on top would result in Tom becoming 1, and Alvin drops to 2

Then the desired outcome would be the sorting will now change to

    pid       |    pid       |   sort
    ----------+--------------+-------
    Alvin     |     1001     |    2
    Tom       |     1002     |    1
    Jerry     |     1003     |    3

Would also like to know the opposite of this which is a "DOWN" reorder

Much appreciated!

Use ORDER BY column ASC or ORDER BY column DESC that will give you an UP-DOWN sorting order. Then just use the query inside a function using an isset conditional statement. — Funk Forty Niner
– Funk Forty Niner, Commented Apr 8, 2014 at 16:21

Plenka · Accepted Answer · 2014-04-08 17:18:13Z

2

Haven't tested this, but you'll catch my drift:

$pid = (int) $_GET['pid'];
$sort = (int) $_GET['sort'];

$update_pid = false;
if ($_GET['do'] == 'UP') {
    $update_pid = '-';
    $update_other = '+';
    $sort_other = $sort - 1;
} elseif ($_GET['do'] == 'DOWN') {
    $update_pid = '+';
    $update_other = '-';
    $sort_other = $sort + 1;
}

if ($update_pid) {
    $query_one = "
        UPDATE table 
        SET sort = sort " . $update_other ." 1 
        WHERE sort = " . $sort_other;
    $query_two = "
        UPDATE table 
        SET sort = sort " . $update_pid ." 1 
        WHERE pid = " . $pid;
}

If the order should be the other way round you can change the if-statements accordingly.

answered Apr 8, 2014 at 17:18

Plenka

1,1397 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user3448267 Over a year ago

thanks! yup I think I can figure this out from this!

Rafał Walczak · Accepted Answer · 2014-04-08 16:13:39Z

0

Consider using jQuery Sortable for this: https://jqueryui.com/sortable/

Sorting elements on server side can be a nightmare, especially if you have many elements and want to move one from the top to the bottom or from the bottom to the top.

answered Apr 8, 2014 at 16:13

Rafał Walczak

5435 silver badges11 bronze badges

2 Comments

user3448267 Over a year ago

Thanks, have seen this option, but would like to know the good old method via SQL coding if possible

Rafał Walczak Over a year ago

It's actually the BAD old method :) Plus, using jQuery Sortable you will need to update database as well. It would be just more user friendly, as the ordering process would be performed on client side and only one request would be processed on saving.

Collectives™ on Stack Overflow

manual sorting using PHP MySQL (Up/Down buttons)?

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related