Select from SQL based on URL parameter

[petenaylor]

Hi all

 

I am trying to write a piece of code that selects different products from the database based on the URL parameter. There are 3 fields; category, type and colour.

 

My URL will look like this if there is all three selected:

 

product.php?category=31&type=74&colour=3

 

Basically, if what ever is set in the url I need this to be selected from the database. Here's my code:

 

if (!isset($_GET['category']) && !isset($_GET['type']) && !isset($_GET['colour'])){

$fetchproducts = mysql_query(" SELECT * FROM `products` ");

  $lightboxcount = 0;

  while($returnedProduct = mysql_fetch_array($fetchproducts)) {

    $lightboxcount ++;

    include('product-cell.php');

}

}

 

Although if there is no colour or the colour is blank like this:

 

product.php?category=31&type=74&colour=

 

I want it to select the items from the database just where the category and type are set.

 

Please help!

 

Many thanks

[denno020]

if (!isset($_GET['category']) || !isset($_GET['type']) || !isset($_GET['colour']))

 

Change to the above and it will work. You had &&, which meant the first condition had to be true AND then second condition AND the third condition, which wouldn't work if, as you say, the colour isn't selected. Using the above it says, if condition 1 is true OR condition 2 is true OR condition 3 is true (or any combination of them). '||' means or.

 

Denno

[petenaylor]

That's great! Thanks for you help.

 

How though, would I adjust the script if there is no colour or if there is a colour?

 

For instance:

 

product.php?category=32&type=80&colour=11

 

or

 

product.php?category=32&type=80&colour=

 

I need it to return all items with category 32 type 80 and colour 11 in the first url and all items with category 32 type 80 and all colours in the second url.

 

Many thanks for you help.

[denno020]

I'm not sure which script you want to change.. Do you want the SQL query to change? Right now, I can't see how this query will pull the correct item from the database at all.. Does it infact work for when all three options are set?

 

Denno

[petenaylor]

Hi there

 

Sorry I didn't explain it very well. Basically when the URL has just a value in for the category it just needs to select all the products from the database where the category is the same. EG:

 

product.php?category=31

 

However, when there is a type as well I need it to find the products from the database where the category is a certain number and the type is too.

 

EG:

 

product.php?category=31&type=23

 

Then the same for the colour. EG:

 

product.php?category=31&type=23&colour=6

 

I need to know the php code that will check to see if each of the parameters are true and then the SQL query too.

 

Many thanks for your help!

[denno020]

Ohhhhhhkay then. You need the SQL too. Well that makes sense cuz what you had wouldn't have worked how you want.

I would first grab the posted data and put them into variables:

 

$category = $_GET['category'];
$type = $_GET['type'];
$colour = $_GET['colour'];

 

Then, the SQL query will look something like this:

SELECT * FROM products WHERE category = '$category' AND type = '$type' AND colour = '$colour'

 

Now for the cases where there isn't one of those sections set, then I'm not sure if this SQL would run correctly. I can think of a couple of ways to do it, but it's going to be far less than efficient.. You could use php to check if the posted variable is set, how you already are doing, however you check for each possible combination. Then you have different SQL for each of those combinations. It wouldn't be too hard to set up, in fact here it is:

 

//check if all variables were set
if (isset($_GET['category']) && isset($_GET['type']) && isset($_GET['colour'])){
$sqlCommand = "SELECT * FROM products WHERE category = '$category' AND type = '$type' AND colour = '$colour'";
//check if just category and type were selected
}else if(isset($_GET['category']) && isset($_GET['type'])){
$sqlCommand = "SELECT * FROM products WHERE category = '$category' AND type = '$type'";
//check if just category and colour were selected
}else if(isset($_GET['category']) &&isset($_GET['colour'])){
$sqlCommand = "SELECT * FROM products WHERE category = '$category' AND colour = '$colour'";
//check if just type and colour were selected
}else if(isset($_GET['type']) && isset($_GET['colour'])){
$sqlCommand = "SELECT * FROM products WHERE type = '$type' AND colour = '$colour'";
}
$fetchproducts = mysql_query($sqlCommand);

 

I also noticed that you had a ! before your isset.. This would be checking for if the variables aren't set. So I removed it to check if they are set. Obviously the words category, type and colour without the $ sign before them refer to the product tables cells.

 

Hopefully that helps you, even if it is a long winded way of finding a solution.

 

Denno

[petenaylor]

That's brilliant mate, thanks for you help!

[denno020]

I'm hoping you've checked that it works before you've called me brilliant lol.

 

But you're welcome

 

Denno