CODEWITHSUNDEEP
phpmysqli
M. Post
M. Post

Reputation: 11

PHP MYSQLi order/sort by multiple fields

I'm trying to get ordering/sorting working by tableheader in asc/desc order however mine keeps stuck in an asc order.

I know the code probably has some security issues but I'd like to get something working now and harden it later on.

<?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);

Upvotes: 1

Views: 415

Answers (1)

ChrisD
ChrisD

Reputation: 147

It looks like you've setup so that in your URL sort is either a 0 or 1. Then you're setting sort to equal either that 0 or 1, not ASC or DESC so MySQL doesn't understand. Try this instead

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

EDIT:

Your table headers will always sort by ASC no matter how many times you click it because it's not set to change:

$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

Upvotes: 1

Related Questions