How to order a query result using a drop down list_问答_开发者

I want to use a dropdown list to order the query result. Whatever option is chosen in the dropdown list, the query will be ordered in ascending order by the option selected. My query works fine, I just need to include the ORDER BY clause. Please look at the form carefully and please help me on this problem.

Below is the code:

    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>

<title>Exam Interface</title>
<meta ht开发者_如何学编程tp-equiv="Content-Type" content="text/html; charset=utf-8" />
</head>
<body>

<form action="exam_interface.php" method="post" name="sessionform">        <!-- This will post the form to its own page"-->
<p>Session ID: <input type="text" name="sessionid" /></p>      <!-- Enter Session Id here-->
<p>Module Number: <input type="text" name="moduleid" /></p>      <!-- Enter Module Id here-->
<p>Teacher Username: <input type="text" name="teacherid" /></p>      <!-- Enter Teacher here-->
<p>Student Username: <input type="text" name="studentid" /></p>      <!-- Enter User Id here-->
<p>Grade: <input type="text" name="grade" /></p>      <!-- Enter Grade here-->
<p>Order Results By: <select name="order">
<option name="noorder">Don't Order Results</option>
<option name="ordersessionid">Session ID</option>
<option name="ordermoduleid">Module Number</option>
<option name="orderteacherid">Teacher Username</option>
<option name="orderstudentid">Student Username</option>
<option name="ordergrade">Grade</option>
</select>
<p><input type="submit" value="Submit" /></p>
</form>

<?php

$username="xxx";
$password="xxx";
$database="mobile_app";

mysql_connect('localhost',$username,$password);

@mysql_select_db($database) or die("Unable to select database");

$sessionid = $_POST['sessionid'];
$moduleid = $_POST['moduleid'];
$teacherid = $_POST['teacherid'];
$studentid = $_POST['studentid'];
$grade = $_POST['grade'];

$result = mysql_query("SELECT * FROM Module m INNER JOIN Session s ON m.ModuleId = s.ModuleId JOIN Grade_Report gr ON s.SessionId = gr.SessionId JOIN Student st ON gr.StudentId = st.StudentId WHERE ('$sessionid' = '' OR gr.SessionId = '$sessionid') AND ('$moduleid' = '' OR m.ModuleId = '$moduleid') AND ('$teacherid' = '' OR s.TeacherId = '$teacherid') AND ('$studentid' = '' OR gr.StudentId = '$studentid') AND ('$grade' = '' OR gr.Grade = '$grade')");

$num=mysql_numrows($result);    

echo "<table border='1'>
<tr>
<th>Student Id</th>
<th>Forename</th>
<th>Session Id</th>
<th>Grade</th>
<th>Mark</th>
<th>Module</th>
<th>Teacher</th>
</tr>";

while ($row = mysql_fetch_array($result)){

 echo "<tr>";
  echo "<td>" . $row['StudentId'] . "</td>";
  echo "<td>" . $row['Forename'] . "</td>";
  echo "<td>" . $row['SessionId'] . "</td>";
  echo "<td>" . $row['Grade'] . "</td>";
  echo "<td>" . $row['Mark'] . "</td>";
  echo "<td>" . $row['ModuleName'] . "</td>";
  echo "<td>" . $row['TeacherId'] . "</td>";
  echo "</tr>";
}

echo "</table>";

mysql_close();


 ?>

</body>
</html>

Well, the ORDER BY clause goes after the WHERE clause, so you just need to add it in there, right? I suggest that the easiest way to do it given your current code is to use a switch to set the value of a variable, $orderbyclause say, and append that variable to the end of the query. (The default case of your switch should set $orderbyclause to the empty string.)

Also, please read about SQL injection and learn how to protect against it. The code you have posted has big security holes.

switch ($_POST['order') {
    case 'ordersessionid': $order_field = 'gr.SessionID'; break;
    case 'ordermoduleid': $order_field = 'whatever'; break;
    ...
    default: $order_field = 'default_field'; break;
}

$sql = "SELECT .... ORDER BY {$order_field} ASC";

You may be tempted to try and just directly insert $_POST['order'] into the query, with appropriate name="" values in the form, but... DON'T. Little Bobby Tables will stop in for a visit and never leave.