HTML table: modify order using mysql

Question

I have a mysql table:

(Questions) => question_id(PK), question(varchar), order(int)

When displaying the data in a html table: the order of the data is sorted by 'order'. Is there a way to make changes in the 'order' using buttons(move-up/down) then update the order in the database?

Thanks in advance!

Answer 1

Add this to the end of your MySQL query "(REST OF OLD QUERY HERE) ORDER BY order " . $sort;

You will need to make links labeled up/down (or something similiar probably) that pass an argument to your script in which way to sort your results.

E.g.: <a href="index.php?sort=>Ascending</a> <a href="index.php?sort=desc>Descending</a>

Then, in your PHP script, before the query:

$sort = ''; // Default order ascending (auto)
if ( isset ( $_GET [ 'sort' ] ) )
{
   if ( $_GET [ 'sort' ] == 'desc' )
   {
      $sort = 'DESC';
   }
}

Explanation:

MySQL orders your results ascending by default, so we have to add nothing to the query normally ($sort='';). However, if the user clicks the Descending link, PHP sets $sort to DESC and MySQL will order your results descending!

Answer 2

If you want a manual order, then you will need to add a column to the database to describe that order. Possibly an integer column with a uniqueness constraint (you appear to have that already, sans the constraint).

You can then ORDER BY that column in the SELECT query you issue.

When you want to change the order, you need to swap the values of the rows you want to swap. (You will likely need to set one to a placeholder value, then the other to the old value of the first, then the first to the old value of the second).

This can be achieved with a form for each row containing a hidden input with a row id, and up/down submit buttons. Then in the server side code you can swap row id with row id + 1 or -1 depending on which button was clicked.

You could add JavaScript (and Ajax techniques) to improve the user experience.

Answer 3

This snippet should to the trick. Any question, throw it to comment.

<?php

mysql_connect('localhost', 'xxxxxx', 'xxxxxxxxxxxx');

if(isset($_GET['q'])){
  $qid = $_GET['q'];
  $act = $_GET['act'];
  $sql = "SELECT `order` FROM test.questions WHERE question_id = '$qid'";
  $rs = mysql_query($sql);
  $r = mysql_fetch_array($rs);
  $order = $r['order'];
  if($act == 'up'){
    if($order == '1') break;
    $up_order = $order - 1;
    $sql = "SELECT question_id FROM test.questions WHERE `order` = '$up_order'";
    $rs = mysql_query($sql);
    $r = mysql_fetch_array($rs);
    $up_order_id = $r['question_id'];
    $sql = "UPDATE test.questions SET `order` = '$up_order' WHERE question_id = '$qid'";
    mysql_query($sql);
    $sql = "UPDATE test.questions SET `order` = '$order' WHERE question_id = '$up_order_id'";
    mysql_query($sql);
  } else if($act == 'down'){
    $sql = "SELECT MAX(`order`) as `order` FROM test.questions";
    $rs = mysql_query($sql);
    $r = mysql_fetch_array($rs);
    $max_order = $r['order'];
    if($order == $max_order) break;
    $down_order = $order + 1;
    $sql = "SELECT question_id FROM test.questions WHERE `order` = '$down_order'";
    $rs = mysql_query($sql);
    $r = mysql_fetch_array($rs);
    $down_order_id = $r['question_id'];
    $sql = "UPDATE test.questions SET `order` = '$down_order' WHERE question_id = '$qid'";
    mysql_query($sql);
    $sql = "UPDATE test.questions SET `order` = '$order' WHERE question_id = '$down_order_id'";
    mysql_query($sql);
  }
  $sql = "";
}

$sql = "SELECT * FROM test.questions ORDER BY `order`;";
$rs = mysql_query($sql);
echo "<table>";
while(false !== ($r = mysql_fetch_array($rs))){
   echo "<tr>";
      echo "<td>$r[question_id]</td>";
      echo "<td>$r[question]</td>";
      echo "<td>$r[order]</td>";
      echo "<td><a href='?q=$r[question_id]&act=up'>UP</a> - <a href='?q=$r[question_id]&act=down'>DOWN</a></td>";
   echo "</tr>";
}
echo "</table>";
?>