我有一个表,它将数据库中的所有信息都放在一个页面上。我想添加一个下拉列表,上面写着按名字、姓氏、组等排序…
我该如何做到这一点?
<?php $query = "SELECT * FROM contacts WHERE type = 'lead'";
$result = mysql_query($query);
if (!$result) {
die('Invalid query: ' . mysql_error()); } ?>
<table>
<tr><td>ID</td> <td>Name</td> <td>Email</td></tr>
<?php
while ($row = mysql_fetch_assoc($result))
{
echo '<tr>';
echo '<td>' . $row['id'] . '</td>';
echo '<td>' . $row['name'] . '</td>';
echo '<td>' . $row['email'] . '</td>';
echo '<td>' . '<a href = "/admin/leads/convert-contact.php?id=' . $row[id] . '">Make Contact</a>';
echo '</tr>';
}
echo '</table>';
?>
SELECT *
FROM Whatever
ORDER BY CASE
WHEN @order = 'first' THEN first_name
WHEN @order = 'last' THEN last_name
WHEN @order = 'group' THEN group_name
ELSE CAST(id AS Varchar)
END
请注意,所有排序列必须是类型兼容的(例如,所有字符/varchars或所有数字)
<form action='database.php' type='post'>
<select name='orderby'>
<option value='firstname'>First Name</option>
<option value='secondname'>Second Name</option>
<option value='group'> group</option>
</select>
</form>
在PHP文件中
if($_POST['orderby']=='firstname') $lastpart= 'ORDER BY firstname';
if($_POST['orderby']=='lastname') $lastpart= 'ORDER BY lastname';
if($_POST['orderby']=='group') $lastpart= 'GROUP BY field';
<?php $query = "SELECT * FROM contacts WHERE type = 'lead' $lastpart"; ----
可能:
<form action='this page url' type='post'>
<select name='orderby'>
<option value='0'>First Name</option>
<option value='1'>Second Name</option>
<option value='2'> Name</option>
<option value='3'> Group</option>
</select>
<input type='submit' name='querygroup' value='Order'/>
</form>
<table>
<tr><td>ID</td> <td>Name</td> <td>Email</td></tr>
<?php
if(isset($_POST['querygroup'])){
if(is_numeric($_POST['orderby'])){
$query = "SELECT * FROM contacts WHERE type = 'lead' ORDER BY CASE '".$_POST['orderby']."' WHEN '0' THEN field_name WHEN '1' THEN field_name [other options] ELSE field_name END";
$result = mysql_query($query);
if (!$result) {
die('Invalid query: ' . mysql_error());
}
while ($row = mysql_fetch_assoc($result)){
echo '<tr>';
echo '<td>' . $row['id'] . '</td>';
echo '<td>' . $row['name'] . '</td>';
echo '<td>' . $row['email'] . '</td>';
echo '<td>' . '<a href = "/admin/leads/convert-contact.php?id=' . $row[id] . '">Make Contact</a>';
echo '</tr>';
}
}
}
?>
</table>
一点点AJAX可以使其成为可重新排序的输出。从下面的例子中可以看出,这并没有那么难。
下面的代码将在页面上放置一个下拉列表,并允许用户选择排序顺序。选择后,排序后的结果将显示在div中。然后,用户可以选择不同的排序顺序,这些结果将出现在div.中
HTML:
<html>
<head>
<script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>
<script type="text/javascript">
$(document).ready(function() {
$('#mysel').change(function() {
var sby = $(this).val();
//alert(sby);
$.ajax({
type: "POST",
url: "another_php_file.php",
data: 'sortby=' + sby,
success: function(thedata) {
$("#results").html(thedata) ;
}
});
});
}); //END $(document).ready()
</script>
</head>
<body>
<div id="results"></div>
<select id="mysel">
<option value = '0'>Choose One</option>
<option value = 'last'>Sort by Last Name</option>
<option value = 'first'>Sort by First Name</option>
</select>
</body>
</html>
另一个_php_file.php
<?php
$order = $_POST['sortby'];
// Do your database login here
$output = '<table><tr><td>ID</td><td>Name</td><td>Email</td></tr>';
$result = mysql_query("SELECT * FROM contacts WHERE type = 'lead' ORDER BY '$order'");
$num_rows = mysql_num_rows($result);
while ($row = mysql_fetch_assoc($result)) {
$output .= '<tr>';
$output .= '<td>' . $row['id'] . '</td>';
$output .= '<td>' . $row['name'] . '</td>';
$output .= '<td>' . $row['email'] . '</td>';
$output .= '<td>' . '<a href = "/admin/leads/convert-contact.php?id=' . $row[id] . '">Make Contact</a>';
$output .= '</tr>';
}
$output .= '</table>';
echo $output;
如果你是AJAX的新手,这里有一些非常简单的例子可以让你掌握它的窍门。它们非常值得你花15或20分钟来玩。
的一个简单例子
更复杂的示例
根据下拉列表1中的选择填充下拉列表2