Dynamic mysql table sorting

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
fatherBoard
Forum Newbie
Posts: 6
Joined: Mon Apr 25, 2011 6:44 am

Dynamic mysql table sorting

Post by fatherBoard »

Hi. I have a problem with dynamic table sorting.
So, I have a table like this:
*******************
id | name | position
-----|---------|---------
1 | record1 | 1
24 | record | 2
45 | record | 3
122 | record | 4
*******************

I need to change the field values with places, for example
id 122 to position 3 and id 45 to position 4.

I tried to do so
-------------------------

Code: Select all

mysql_query("UPDATE `data` SET `position` = `position` - 1 WHERE `id` = ". $id);
mysql_query("UPDATE `data` SET `position` = `position` + 1 WHERE `id` < ". $id);
------------------------

but it's not working as needed. Help me, please. (and sorry for my english )
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Re: Dynamic mysql table sorting

Post by superdezign »

The issue is that you are looking at the `id` as your condition instead of the `position`. When you change the `position`, the `id` does not change.

Code: Select all

UPDATE `data` SET `position` = `position` - 1 where `id` = $id;
UPDATE `data` SET `position` = `position` + 1 where `id` != $id AND `position` >= $new_position_of_$id;
You would get $new_position_of_$id from a SELECT query.
fatherBoard
Forum Newbie
Posts: 6
Joined: Mon Apr 25, 2011 6:44 am

Re: Dynamic mysql table sorting

Post by fatherBoard »

but how can I set $new_position_of_id ?
fatherBoard
Forum Newbie
Posts: 6
Joined: Mon Apr 25, 2011 6:44 am

Re: Dynamic mysql table sorting

Post by fatherBoard »

I need a result page working like this http://move.inkz.ru/
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Re: Dynamic mysql table sorting

Post by superdezign »

You select it, like I said, using a SELECT query. Whether you do it before or after the UPDATE query is up to you. If you already have the information, then update it before putting it into the query instead of pulling from the database again.
fatherBoard
Forum Newbie
Posts: 6
Joined: Mon Apr 25, 2011 6:44 am

Re: Dynamic mysql table sorting

Post by fatherBoard »

but then some `position` elements become the same value.
for example:

*******************
id | name | position
-----|---------|---------
1 | record1 | 1
24 | record | 2
45 | record | 2
122 | record | 4
128 | record | 3
167 | record | 2
*******************
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Re: Dynamic mysql table sorting

Post by superdezign »

Doing what I told you makes that impossible unless your positions were already invalid. Show us the code that you are using.
fatherBoard
Forum Newbie
Posts: 6
Joined: Mon Apr 25, 2011 6:44 am

Re: Dynamic mysql table sorting

Post by fatherBoard »

original part of code:

Code: Select all

<?php

    error_reporting(E_ALL);

    include './config.php';
    
    $id = !empty($_GET['id']) ? (int)$_GET['id'] : null;
    $move = !empty($_GET['move']) ? $_GET['move'] : null;

?>

<table id="num">
    
<?php

    $table = '';

    $res = mysql_query("SELECT * FROM `data` ORDER BY `position` ASC");   

    while($row = mysql_fetch_assoc($res))
    {
        $table .= '<tr><td>'. $row ['name'] .'</td>';
        $table .= '<td><a href="?id='. $row['id'] .'&move=up"><img src="/images/arrowup.png" /></a></td>';
        $table .= '<td><a href="?id='. $row['id'] .'&move=down"><img src="/images/arrowdown.png" /></a></td></tr>';
    }

    if($id && is_int($id) && $move == 'up')
    {
        mysql_query("UPDATE `data` SET `position` = `position` - 1 WHERE `id` = ". $id);
        mysql_query("UPDATE `data` SET `position` = `position` + 1 WHERE `id` < ". $id);
        
        header('location:http://'. $_SERVER['HTTP_HOST']);
        exit();
    }
    elseif($id && is_int($id) && $move == 'down')
    {
        mysql_query("UPDATE `data` SET `position` = `position` + 1 WHERE `id` = ". $id);
        mysql_query("UPDATE `data` SET `position` = `position` - 1 WHERE `id` < ". $id);       
        
        header('location:http://'. $_SERVER['HTTP_HOST']);
        exit();
    }

    print $table;   
    
?>

</table>
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Re: Dynamic mysql table sorting

Post by superdezign »

You didn't implement what I said at all in this code. Show me the code that resulted in "some `position` elements becom[ing] the same value."
fatherBoard
Forum Newbie
Posts: 6
Joined: Mon Apr 25, 2011 6:44 am

Re: Dynamic mysql table sorting

Post by fatherBoard »

Code: Select all

$res = mysql_query("SELECT `position` FROM `data` WHERE `id` = ". $id);
        $row = mysql_fetch_assoc($res);
        
        mysql_query("UPDATE `data` SET `position` = `position` - 1 WHERE `id` = ". $id);
        mysql_query("UPDATE `data` SET `position` = `position` + 1 WHERE `id` != ". $id ." AND `position` >= ". $row['position']);
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Re: Dynamic mysql table sorting

Post by superdezign »

Remember how I said "$new_position_of_id"? You are using the old position because you pull it before the position is changed and you do not update it. Just do your SELECT query after the first update instead of before it.
Post Reply