PHP sorting database items with ORDER BY query MySQL

Question

I have a MySQL database with which I'm dynamically populating a web page. I'm building a MySQL query that fetches some products stored in a table called products that I can later echo out on the page with PHP. The columns in that table are id, product_name, price and description. I want the user to be able to sort products alphabetically, by price (low-high) etc by clicking the relevant links on the page. This is what I've written so far:

// Run a SELECT query to get all the products stored in the database
// By default, if no sorting URL variable is fed into the page, then the SQL query becomes order by id.
// The first time you land on the index page as plain index.php, not index.php?=variable, this is the query that's used

$sql = mysqli_query($con,"SELECT * FROM products ORDER BY id DESC");

// If the user chooses to sort the produts in a different way, then an HTML link will set a PHP variable into this page
// We will check for that variable and change the SQL query to sort the products in a different way
if (isset($_GET['sortby'])) {
  // Capture that in a variable by that name
  $sortby = $_GET['sortby'];
  // Now to change the SQL query based on the sorting the user chose (price high to low, low to high, alphabetical and latest first)
  if ($sortby = 'pricehilo') {
    $sql = mysqli_query($con,"SELECT * FROM products ORDER BY price DESC");
  }
  elseif ($sortby = 'pricelohi') {
    $sql = mysqli_query($con,"SELECT * FROM products ORDER BY price ASC");
  }
  elseif ($sortby = 'name') {
    $sql = mysqli_query($con,"SELECT * FROM products ORDER BY product_name");
  }
}

The page is index.php, and these are the links in the HTML:

<p><a href="index.php?sortby=pricehilo">Price (Highest-Lowest)</a></p>
<p><a href="index.php?sortby=pricelohi">Price (Lowest-Highest)</a></p>
<p><a href="index.php?sortby=name">Alphabetical</a></p>

When I load the page for the first time, all my products are displayed sorted by ID. However, when I click on any link, the products get sorted by price highest to lowest. If I refresh the page as just index.php the products remain sorted by price highest to lowest - the SQL query it is taking is that.

How can I fix this?

There's no LIMIT clause in your query, so there's no pagination. So you can just return an array and handle the ordering at the application level - either with PHP or, even, with a bit of javascript. See data grids for some examples. — Strawberry
– Strawberry, Commented Dec 4, 2013 at 13:17
Right now the table is very simple with just a few products so I'm echoing out everything in one page, but thanks for the tip, I'll look into that. — user2957365
– user2957365, Commented Dec 4, 2013 at 13:22
The 'Sort' option on the following website is one of my favourite examples - you use a database to build a list (li) of items, and then javascript handles the rest. isotope.metafizzy.co . None of the sorting/filtering operations require any further interaction with the database. — Strawberry
– Strawberry, Commented Dec 4, 2013 at 13:25

aykut · Accepted Answer · 2013-12-04 13:16:45Z

7

Change if statement's content = to ==

answered Dec 4, 2013 at 13:16

aykut

3,1021 gold badge28 silver badges27 bronze badges

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

2 Comments

user2957365 Over a year ago

Ha! So simple. Thank you very much for answering, it worked. I feel silly now :)

aykut Over a year ago

Never mind, most people do this mistake first years of PHP :)

Collectives™ on Stack Overflow

PHP sorting database items with ORDER BY query MySQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related