PHP MYSQLi order/sort by multiple fields

Question

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

On first glance, it seems to be due to the fact you have hard coded it that way in the URL's "sort=0" — Peter
– Peter, Commented May 21, 2017 at 11:35

ChrisD · Accepted Answer · 2017-05-21 14:54:39Z

1

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

edited May 21, 2017 at 14:54

answered May 21, 2017 at 11:42

ChrisD

1475 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

ChrisD Over a year ago

What do you get if you var_dump($sort)?

M. Post Over a year ago

string(1) "0"

ChrisD Over a year ago

If you made the changes exactly as I have them above, there is absolutely no reason why sort would be 0

M. Post Over a year ago

god stupid me I forgot the last line string(3) "asc" it is then

ChrisD Over a year ago

So it's functioning as it should now?

|

Collectives™ on Stack Overflow

PHP MYSQLi order/sort by multiple fields

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related