URL parameter and SQL query

[petenaylor]

Hi all

 

I have an SQL database with products in and I need to write the PHP script to select from the SQl based on values in the URL.

 

The URL looks like this:

 

product.php?category=32&type=90&colour=10

 

So I can do the script:

 

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

$sqlCommand = "SELECT * FROM products WHERE category = '$category' AND type = '$type' AND colour = '$colour'"; }

 

Which works great, I have also got the other versions if the type and colour are empty.

 

What I can't do is get it to select all from the database where the category = 32 and the type = 90 if the URL looks like this:

 

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

 

In other words where the colour is set but is empty. I want the script to show all products that have the category of 32 and the type of 90 if this is the case

 

Please help!

 

Many thanks

 

Pete

[requinix]

Don't do the query all at once. Piece it together, like

query = "SELECT * FROM products"

conditions = empty list
if (there is a category to search for) conditions += the category matches whatever
if (there is a type to search for) conditions += the type matches whatever
if (there is a color to search for) conditions += the color matches whatever

if (there are conditions for the search) query += "WHERE " + conditions

 

You've got another problem though. What if the category isn't a number? What if I go to the URL and put stuff in there that you don't expect? I could ruin your database. Seriously.

If you know that something should be a number, use type casting or an appropriate function.

$x = (int)"not a number"; // type casting. $x=0
$x = intval("not a number"); // an appropriate function. $x=0

[jcbones]

Here is an example of piecing the sql.

if(isset($_GET['category']) {
  $where[] = 'category = \'' . mysql_real_escape_string($_GET['category']) . '\'';
}
if(isset($_GET['type'])) {
  $where[] = 'type = \'' . mysql_real_escape_string($_GET['type']) . '\'';
}
if(isset($_GET['colour'])){
  $where[] = 'colour = \'' . mysql_real_escape_string($_GET['colour']) . '\'';
}

if(is_array($where)) {
$sqlCommand = "SELECT * FROM products WHERE " . implode(' AND ',$where);
}

[petenaylor]

hi there

 

Thanks for your replies, I have tried it but it still doesn't show all the products in the category and type where the colour is empty?

 

If the URL is: product.php?category=31&type=74&colour=

 

It shows no products whereas if it's:

 

product.php?category=31&type=74

 

It show the correct products?

 

Please help!

 

Many thanks

Pete

[Pikachu2000]

You'll also need to add a check to each conditional to make sure the $_GET var is not empty.

[petenaylor]

Hi there

 

I see! How is that done? is it:  !empty($_GET)  ?

[Pikachu2000]

Actually, on second glance you can simply replace isset with !empty in the conditionals, and that should resolve it.

[petenaylor]

Perfect! It works! Thank you very much!