ORDER BY $sortby, .... not showing in correct order...

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

ORDER BY $sortby, .... not showing in correct order...

Post by simonmlewis »

Code: Select all

  $query = "SELECT * FROM products WHERE catname = :cname AND pause = 'off' ORDER BY $sortby, rcstock = 'in stock' DESC, comingsoon = 'yes' DESC, rcstock = 'out of stock' DESC LIMIT $offset, $rowsPerPage;";
We have added a 'sort by' function on the website, which is one of the following four options:

Code: Select all

if (isset($sort))
{
  if ($sort == "titleaz") { $sortby = "title ASC";}
  if ($sort == "titleza") { $sortby = "title DESC";}
  if ($sort == "pricelow") { $sortby = "price ASC";}
  if ($sort == "pricehigh") { $sortby = "price DESC";}
}
The problem is, it's not putting "out of stock" at the bottom of the results.
Some are "in stock", but on one page it's got an Out of Stock product first!
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: ORDER BY $sortby, .... not showing in correct order...

Post by simonmlewis »

In addition to this, I need to see how I can add filters to queries, without creating a ton of queries that accommodates lots of filters.

Code: Select all

SELECT * FROM products WHERE catname = :cname AND pause = 'off' AND powertype =:powered
Like this.
At the moment if I put that in there, and use this:

Code: Select all

$result->execute(array(':cname' => $cname, ':powered' => $powered));
With this at the top of the page:

Code: Select all

if(isset($_GET['powered']))
{
    $powered = $_GET['powered'];
    $_SESSION['powered']=$powered;
} elseif (isset($_SESSION['powered'])) {
    $powered=$_SESSION['powered'];
}

if (!isset($powered)) 
{ 
$powered = "";
}
It comes up with no results.
But if I add "spring" to that variable it works.
So how do I make it pass thru the variable with nothing in it, but still run the query?
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: ORDER BY $sortby, .... not showing in correct order...

Post by Celauran »

What have you tried here? Seems like it would be pretty easy to check if $powered is set before adding it to the query.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: ORDER BY $sortby, .... not showing in correct order...

Post by simonmlewis »

It is easy. Using isset. But if isset == "" and $powered is in the query, it's not giving any answers.

Plan B is to use a LIKE query. That works!
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: ORDER BY $sortby, .... not showing in correct order...

Post by Celauran »

simonmlewis wrote:But if isset == "" and $powered is in the query
My point was more that this shouldn't be the case. Check if it's set (and not empty!) and build the query accordingly.
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: ORDER BY $sortby, .... not showing in correct order...

Post by requinix »

simonmlewis wrote:The problem is, it's not putting "out of stock" at the bottom of the results.
Right, because it's sorting by the title/price first. Which is exactly what you're telling it to do.

If the in stock/out of stock matters more then you have to sort by that first.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: ORDER BY $sortby, .... not showing in correct order...

Post by simonmlewis »

Gotcha. I can see where I have gone wrong.
So if the Variable is SET, then "where powered =:powered" would work. But what if $powered == "" ? Or even NULL?
That query then falls down. Or at least produces no results.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: ORDER BY $sortby, .... not showing in correct order...

Post by Celauran »

simonmlewis wrote:Gotcha. I can see where I have gone wrong.
So if the Variable is SET, then "where powered =:powered" would work. But what if $powered == "" ? Or even NULL?
That query then falls down. Or at least produces no results.
It shouldn't.

Code: Select all

$query = "SELECT * FROM products WHERE catname = :cname AND pause = 'off'";
if (!empty($powered)) {
    $query .= " AND powertype = :powered";
}
Additionally, rather than using individual variables, you can use an array to hold all your parameters, build your query based on what's in the array, and then just pass the array directly to execute.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: ORDER BY $sortby, .... not showing in correct order...

Post by simonmlewis »

So how do I run this part, to ensure there are not more variables in the execute than in the query?

Code: Select all

  $result->execute(array(':cname' => $cname, ':powered' => "%{$powered}%", ':colour' => "%{$colour}%", ':manufacturer' => "%{$manufacturer}%"));
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: ORDER BY $sortby, .... not showing in correct order...

Post by Celauran »

Look at my previous response. If you store query parameters in an array and build your query based on which keys are set, it's done for you.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: ORDER BY $sortby, .... not showing in correct order...

Post by simonmlewis »

I understand the part in the main $query, but not how to accumulate the variables.
Obviously i cannot just add it to the list in the execute query, because of one variable is not being used, then it's an invalid number of "tokens"?
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: ORDER BY $sortby, .... not showing in correct order...

Post by Celauran »

Instead of using global variables, use a single array. Array keys match your prepared statement placeholders. It's probably easiest if I show you an example. I've tried to model this after the bits and bobs of code you have shared, but this certainly can't be copy/pasted.

Code: Select all

<?php

// This array will hold all the parameters with which we'll build our query
$query_params = [];

// $_GET takes precendence over $_SESSION
// If neither is set, the parameter never makes its way into our params array
if (isset($_GET['powered'])) {
    $query_params['powered'] = $_GET['powered'];
} else if (isset($_SESSION['powered'])) {
    $query_params['powered'] = $_SESSION['powered'];
}

// Take the same approach for other variables
if (isset($_GET['cname'])) {
    $query_params['cname'] = $_GET['cname'];
} else if (isset($_SESSION['cname'])) {
    $query_params['cname'] = $_SESSION['cname'];
}

if (isset($_GET['colour'])) {
    $query_params['colour'] = $_GET['colour'];
} else if (isset($_SESSION['colour'])) {
    $query_params['colour'] = $_SESSION['colour'];
}

if (isset($_GET['manufacturer'])) {
    $query_params['manufacturer'] = $_GET['manufacturer'];
} else if (isset($_SESSION['manufacturer'])) {
    $query_params['manufacturer'] = $_SESSION['manufacturer'];
}

/**
 * Now we build up our query. The important thing here is that we have no empty
 * values and we will have a query built to include only the selected variables
 */

// This is our base query; no variables, only bits we always want.
$query = "SELECT * FROM products WHERE pause = 'off'";

// Now we start checking for parameters passed in above
// Remember there are no empty values. If powered wasn't set, it's not going
// to be included in the query.
if (isset($query_params['powered'])) {
    $query .= " AND powertype = :powered";
}

if (isset($query_params['cname'])) {
    $query .= " AND catname = :cname";
}

if (isset($query_params['colour'])) {
    $query .= " AND colour = :colour";
}

if (isset($query_params['manufacturer'])) {
    $query .= " AND manufacturer = :manufacturer";
}

// Finally we add any remaining bits to the query
$query .= " ORDER BY rcstock = 'in stock' DESC LIMIT 10";

// Prepare the statement
$stmt = $db->prepare($query);

// Since the query is built specifically based on the contents of $query_params,
// we can just pass in the whole array. Keys will always match.
$exec = $stmt->execute($query_params);

if ($exec) {
    $results = $stmt->fetchAll();
}
There's a bunch of repetition here that could (and probably should) be extracted to function calls, but I purposely did it this way to make it clear what's going on. No empty values. No missing or extra keys. Every query is built up as per the requested parameters. Does it make more sense now?
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: ORDER BY $sortby, .... not showing in correct order...

Post by simonmlewis »

Ok I think I can see.
So for each $query_params, I put that "insert" section into the $query script.
So it only picks up those variables, if there is something in them.

But what about the execute code that runs below it, that I showed in my example?
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: ORDER BY $sortby, .... not showing in correct order...

Post by Celauran »

simonmlewis wrote:But what about the execute code that runs below it, that I showed in my example?
What about it? You're passing in an array that matches exactly what you've specified in the query. That's the whole point.

Code: Select all

// Since the query is built specifically based on the contents of $query_params,
// we can just pass in the whole array. Keys will always match.
$exec = $stmt->execute($query_params);
You don't pass in a hardcoded array. The query is built from the contents of $query_params, so passing $query_params to execute means they always match.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: ORDER BY $sortby, .... not showing in correct order...

Post by simonmlewis »

Ok two issues.
1) at what point does it store the content of the variables that have been passed through the URL?
2) how do I clear the variable if I want to reset, using the powered=showall in the URL?

Code: Select all

if(isset($_GET['powered']))
{
    $query_params['powered'] = $_GET['powered'];
    if ($powered == "showall") { $query_params['powered'] = NULL;}
    $_SESSION['powered']=$powered;
} elseif (isset($_SESSION['powered'])) {
    $query_params['powered'] = $_SESSION['powered'];
}
This doesn't do it.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
Post Reply