PHP MYSQLi 按多个字段排序/排序

Question

我正在尝试按 asc/desc 顺序按表头进行排序/排序，但我的一直卡在 asc 顺序中。

我知道代码可能存在一些安全问题，但我想现在让一些东西工作并在以后强化它。

<?php
$con = mysqli_connect($dbhost,$dbuser,$dbpass);
mysqli_select_db($con,$database) or die ("Unable to select database");
// menu creation
echo "<div class="menu"><ul>";
echo "<li><a href="index.php?name="> All </a></li>";
for ($i="A"; $i != "AA"; $i++) 
echo "<li><a href="index.php?name=$i"> $i </a></li>";
if(isset($_REQUEST['name'])){
 $i= strip_tags($_REQUEST['name']);
}
//table sorting
$orderBy = array('name', 'date', 'genre', 'art', 'topic', 'version');
$order = 'name';
if (isset($_GET['orderBy']) && in_array($_GET['orderBy'], $orderBy)) {
    $order = $_GET['orderBy'];
}
$sortBy = array('asc', 'desc');
$sort = 0;
if (isset($_GET['sort']) && in_array($_GET['sort'], array_keys($sortBy))) {
    $sort = $_GET['sort'];
}
$data = mysqli_query($con, 'SELECT * FROM games ORDER BY ' . $order . ' ' . $sort) or die (mysqli_error($con));
echo "</ul></div>";
// table result
echo"<div class='table'><table><thead><tr>
<th><a href='?orderBy=name&sort=0'>Name</a></th>
<th><a href='?orderBy=date&sort=0'>Date</a></th>
<th><a href='?orderBy=genre&sort=0'>Genre</a></th>
<th><a href='?orderBy=art&sort=0'>Art</a></th>
<th><a href='?orderBy=version&sort=0'>Version</a></th>
</thead></tr><tbody>";
while($row = mysqli_fetch_array($data)){
  echo "<tr>";
  echo "<td>" . $row['name'] . "</td>";
  echo "<td>" . $row['date'] . "</td>";
  echo "<td>" . $row['genre'] . "</td>";
  echo "<td>" . $row['art'] . "</td>";
  echo "<td>" . $row['version'] . "</td>";
  echo "</tr>";
}
echo "</tbody></table></div>";
mysqli_close($con);

Answer 1

看起来您已经设置好了，因此在 URL 中排序为 0 或 1。然后，您将排序设置为等于0或1，而不是ASC或DESC，因此MySQL无法理解。试试这个

$sortBy = array('asc', 'desc');
$sort = 'asc';
if (isset($_GET['sort']) && in_array($_GET['sort'], array_keys($sortBy))) {
    $sort = $sortBy[$_GET['sort']];
}

编辑：

无论您单击多少次，您的表格标题都将始终按 ASC 排序，因为它未设置为更改：

$data = mysqli_query($con, 'SELECT * FROM games ORDER BY ' . $order . ' ' . $sort) or die (mysqli_error($con));
echo "</ul></div>";
// table result
$sort = ($sort == 'desc' ? 1 : 0);
?>
  <div class='table'><table><thead><tr>
  <th><a href='?orderBy=name&sort=<?= ($order == 'name' ? ($sort == 0 ? 1 : 0) : 0); ?>'>Name</a></th>
  <th><a href='?orderBy=date&sort=<?= ($order == 'date' ? ($sort == 0 ? 1 : 0) : 0); ?>'>Date</a></th>
  <th><a href='?orderBy=genre&sort=<?= ($order == 'genre' ? ($sort == 0 ? 1 : 0) : 0); ?>'>Genre</a></th>
  <th><a href='?orderBy=art&sort=<?= ($order == 'art' ? ($sort == 0 ? 1 : 0) : 0); ?>'>Art</a></th>
  <th><a href='?orderBy=version&sort=<?= ($order == 'version' ? ($sort == 0 ? 1 : 0) : 0); ?>'>Version</a></th>
</thead></tr><tbody>
<?php