Page 1 of 2
ORDER BY $sortby, .... not showing in correct order...
Posted: Wed Oct 26, 2016 4:19 am
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!
Re: ORDER BY $sortby, .... not showing in correct order...
Posted: Wed Oct 26, 2016 4:32 am
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?
Re: ORDER BY $sortby, .... not showing in correct order...
Posted: Wed Oct 26, 2016 7:28 am
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.
Re: ORDER BY $sortby, .... not showing in correct order...
Posted: Wed Oct 26, 2016 7:35 am
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!
Re: ORDER BY $sortby, .... not showing in correct order...
Posted: Wed Oct 26, 2016 7:50 am
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.
Re: ORDER BY $sortby, .... not showing in correct order...
Posted: Wed Oct 26, 2016 7:53 am
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.
Re: ORDER BY $sortby, .... not showing in correct order...
Posted: Wed Oct 26, 2016 8:03 am
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.
Re: ORDER BY $sortby, .... not showing in correct order...
Posted: Wed Oct 26, 2016 8:29 am
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.
Re: ORDER BY $sortby, .... not showing in correct order...
Posted: Wed Oct 26, 2016 11:01 am
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}%"));
Re: ORDER BY $sortby, .... not showing in correct order...
Posted: Wed Oct 26, 2016 1:09 pm
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.
Re: ORDER BY $sortby, .... not showing in correct order...
Posted: Fri Oct 28, 2016 5:18 am
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"?
Re: ORDER BY $sortby, .... not showing in correct order...
Posted: Fri Oct 28, 2016 7:06 am
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?
Re: ORDER BY $sortby, .... not showing in correct order...
Posted: Fri Oct 28, 2016 7:19 am
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?
Re: ORDER BY $sortby, .... not showing in correct order...
Posted: Fri Oct 28, 2016 7:22 am
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.
Re: ORDER BY $sortby, .... not showing in correct order...
Posted: Fri Oct 28, 2016 9:39 am
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.