PHP Developers Network

A community of PHP developers offering assistance, advice, discussion, and friendship.
 
Loading
It is currently Fri Oct 20, 2017 8:15 pm

All times are UTC - 5 hours




Post new topic Reply to topic  [ 16 posts ]  Go to page 1, 2  Next
Author Message
PostPosted: Wed Oct 26, 2016 4:19 am 
Offline
DevNet Master

Joined: Wed Oct 08, 2008 3:39 pm
Posts: 4349
Location: United Kingdom
Syntax: [ Download ] [ Hide ]
  $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:

Syntax: [ Download ] [ Hide ]
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.


Top
 Profile  
 
PostPosted: Wed Oct 26, 2016 4:32 am 
Offline
DevNet Master

Joined: Wed Oct 08, 2008 3:39 pm
Posts: 4349
Location: United Kingdom
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.

Syntax: [ Download ] [ Hide ]
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:
Syntax: [ Download ] [ Hide ]
$result->execute(array(':cname' => $cname, ':powered' => $powered));

With this at the top of the page:
Syntax: [ Download ] [ Hide ]
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.


Top
 Profile  
 
PostPosted: Wed Oct 26, 2016 7:28 am 
Offline
Moderator
User avatar

Joined: Tue Nov 09, 2010 3:39 pm
Posts: 6390
Location: Montreal, Canada
What have you tried here? Seems like it would be pretty easy to check if $powered is set before adding it to the query.

_________________
Supported PHP versions No longer supported versions


Top
 Profile  
 
PostPosted: Wed Oct 26, 2016 7:35 am 
Offline
DevNet Master

Joined: Wed Oct 08, 2008 3:39 pm
Posts: 4349
Location: United Kingdom
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.


Top
 Profile  
 
PostPosted: Wed Oct 26, 2016 7:50 am 
Offline
Moderator
User avatar

Joined: Tue Nov 09, 2010 3:39 pm
Posts: 6390
Location: Montreal, Canada
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.

_________________
Supported PHP versions No longer supported versions


Top
 Profile  
 
PostPosted: Wed Oct 26, 2016 7:53 am 
Offline
Spammer :|
User avatar

Joined: Wed Oct 15, 2008 2:35 am
Posts: 6573
Location: WA, USA
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.


Top
 Profile  
 
PostPosted: Wed Oct 26, 2016 8:03 am 
Offline
DevNet Master

Joined: Wed Oct 08, 2008 3:39 pm
Posts: 4349
Location: United Kingdom
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.


Top
 Profile  
 
PostPosted: Wed Oct 26, 2016 8:29 am 
Offline
Moderator
User avatar

Joined: Tue Nov 09, 2010 3:39 pm
Posts: 6390
Location: Montreal, Canada
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.

Syntax: [ Download ] [ Hide ]
$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.

_________________
Supported PHP versions No longer supported versions


Top
 Profile  
 
PostPosted: Wed Oct 26, 2016 11:01 am 
Offline
DevNet Master

Joined: Wed Oct 08, 2008 3:39 pm
Posts: 4349
Location: United Kingdom
So how do I run this part, to ensure there are not more variables in the execute than in the query?

Syntax: [ Download ] [ Hide ]
  $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.


Top
 Profile  
 
PostPosted: Wed Oct 26, 2016 1:09 pm 
Offline
Moderator
User avatar

Joined: Tue Nov 09, 2010 3:39 pm
Posts: 6390
Location: Montreal, Canada
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.

_________________
Supported PHP versions No longer supported versions


Top
 Profile  
 
PostPosted: Fri Oct 28, 2016 5:18 am 
Offline
DevNet Master

Joined: Wed Oct 08, 2008 3:39 pm
Posts: 4349
Location: United Kingdom
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.


Top
 Profile  
 
PostPosted: Fri Oct 28, 2016 7:06 am 
Offline
Moderator
User avatar

Joined: Tue Nov 09, 2010 3:39 pm
Posts: 6390
Location: Montreal, Canada
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.

Syntax: [ Download ] [ Hide ]
<?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?

_________________
Supported PHP versions No longer supported versions


Top
 Profile  
 
PostPosted: Fri Oct 28, 2016 7:19 am 
Offline
DevNet Master

Joined: Wed Oct 08, 2008 3:39 pm
Posts: 4349
Location: United Kingdom
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.


Top
 Profile  
 
PostPosted: Fri Oct 28, 2016 7:22 am 
Offline
Moderator
User avatar

Joined: Tue Nov 09, 2010 3:39 pm
Posts: 6390
Location: Montreal, Canada
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.

Syntax: [ Download ] [ Hide ]
// 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.

_________________
Supported PHP versions No longer supported versions


Top
 Profile  
 
PostPosted: Fri Oct 28, 2016 9:39 am 
Offline
DevNet Master

Joined: Wed Oct 08, 2008 3:39 pm
Posts: 4349
Location: United Kingdom
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?

Syntax: [ Download ] [ Hide ]
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.


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 16 posts ]  Go to page 1, 2  Next

All times are UTC - 5 hours


Who is online

Users browsing this forum: No registered users and 7 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Jump to:  
Powered by phpBB® Forum Software © phpBB Group