Ordering a table with weights?


The problem

Right now I have to create this:

In my database; each favorite as its own 'weight' entry.
As of right now it defaults to '10'

Let's say I have an array of favorites,
the weights go like this accordingly:

  • 1
  • 2
  • 3
  • 4

If I were to press the 'down' arrow on '2' I'd have to add +1 to its weight.
Which would result in:

  • 1
  • 3
  • 3
  • 4

But I just want to switch favorite '2' with favorite '3's weight.
I need a way to ensure that when the weights for each item function as they're implied. Since just adding or subtracting 1 will lead to a lot of problem.

Hopefully I explained it properly.

The code

Writing to the database

 * Write Form data to database
function f25_favorites_form_submit($form, &$form_state){
  global $user;
  $listOfPaths = f25_favorites_listOfPaths();
  $selected = $form_state['values']['path'];

  $data = array(
    'uid' => $user->uid,
    'path' => $selected,
    'title' => $listOfPaths[$selected]['#title'],
    'weight' => 10,
    'timestamp' => time(),

  drupal_write_record(f25_favorites, $data);

The Query

 * Fetching Specific User's Favorites Data
function f25_favorites_user_favorites() {
  global $user;
  if($user->uid > 0){
    $userid = $user->uid;
    $user_paths = db_query("SELECT * FROM f25_favorites foo WHERE foo.uid = '%s' ORDER BY foo.weight DESC", $userid);
      $pathlist = array();
    while ($data = db_fetch_object($user_paths)) {
      $pathlist[] = $data;
    return $pathlist;

How should I approach this problem?

if all your favorites are weighted differently, you can simply swap the weight field of the one "getting up" with the one "getting down", or reverse, when decreasing weight. you'd have to figure out the code though