Page 1 of 1

PHP Interface to Sort MySQL Data

Posted: Sun Jun 07, 2009 8:22 am
by socket1
What I am trying to do is have a database with various columns then a column called position or something and then the query is ORDER BY position;
I need to write something in PHP where I can have a visual representation of these rows and have two arrows, one pointing up and one pointing down next to each of the rows. When I click one of the arrows I need it to move the row in the appropriate direction.

I almost got this working but with a few glitches then I lost a whole bunch of work and had no backups.

EDIT: I have this so far but I'm not sure if it will break
http://ajarsource.org/projects/MySQL%20Sort/sort.php

Image

CODE:

Code: Select all

<style type="text/css">
<!--
a:link {
    color: #0099CC;
    text-decoration: none;
    cursor:default;
}
a:visited {
    text-decoration: none;
    color: #0099CC;
}
a:hover {
    text-decoration: underline;
    color: #0099CC;
}
a:active {
    text-decoration: none;
    color: #0099CC;
}
-->
</style>
<?php
mysql_connect('localhost', 'root', '*****');
if (isset($_GET['move'])) {
    switch ($_GET['move']) {
        case 'up':
            mysql_query("UPDATE sort.table SET pos='".$_GET['pos']."' WHERE pos='".($_GET['pos']-1)."'");
            mysql_query("UPDATE sort.table SET pos='".($_GET['pos']-1)."' WHERE id='".$_GET['id']."'");
            header('Location: sort.php');
        break;
        
        case 'dn':
            mysql_query("UPDATE sort.table SET pos='".($_GET['pos']-1)."' WHERE pos='".($_GET['pos']+1)."'");
            mysql_query("UPDATE sort.table SET pos='".($_GET['pos']+1)."' WHERE id='".$_GET['id']."'");
            header('Location: sort.php');
        break;
    }
}
// This code is all I could think of to make the pos column neat and clean every time
$q = mysql_query("SELECT * FROM sort.table ORDER BY pos");
$ID = 1;
while ($r = mysql_fetch_assoc($q)) {
    mysql_query("UPDATE sort.table SET pos='".$ID."' WHERE id='".$r['id']."'");
    $ID++;
}
// End fixed code
 
$q = mysql_query("SELECT * FROM sort.table ORDER BY pos");
while ($r = mysql_fetch_assoc($q)) {
    $Code = '<sup>';
    if ($r['pos'] == 1) {
        $Code.= '&nbsp; &nbsp;';
    } else {
        $Code.= '<a href="sort.php?id='.$r['id'].'&pos='.$r['pos'].'&move=up">&uarr;</a> ';
    }
    if ($r['pos'] == mysql_num_rows($q)) {
        $Code.= '&nbsp; &nbsp;';
    } else {
        $Code.= ' <a href="sort.php?id='.$r['id'].'&pos='.$r['pos'].'&move=dn">&darr;</a>';
    }
    $Code .= '</sup>'.$r['title'].'<br />'."\n";
echo $Code;
}
?>
Are there any suggestions to improve this code or if anybody sees any mistakes / things that could cause glitches I'd be glad to know so I can fix them.