Filtering SQL values by URL parameter

[petenaylor]

Hi all

 

I am building a page to filter SQL values but I'm not sure how to write the code for the query.

 

My URL looks like this:

 

stone.php?category=Wood&type=4&colour=1&Submit=Submit

 

Firstly, can I remove the submit from the URL?

 

Then I have my code to create the query:

 

$fetchData=mysql_query("SELECT * FROM `products` WHERE category = "'.$_GET'category.'"");

 

Can I combine this for the type and colour?

 

Many thanks

Pete

[Pikachu2000]

If you're submitting a form with GET method, all the variables will show up in the url, so no you can't remove Submit (at least not through any method I'm aware of), and you'd likely want to make use of it anyhow to verify that the script was reached via the search form, and prevent the query from running if it wasn't.

 

You also really need to sanitize and validate any and all incoming form data before using it in a database query string.

 

if( isset($_GET['Submit']) ) {
if( (strlen(trim($_GET['category'])) > 0) && (strlen(trim($_GET['type'])) > 0 && ctype_digit($_GET['type'])) ) { // assumes category as string, and type as integer
	$category = mysql_real_escape_string(trim($_GET['category'])); // escape string data from form
	$type = (int) trim($_GET['type']); // cast numeric form data as an integer

	$query = "SELECT * FROM `products` WHERE `category` = '{$_GET['category']}' AND `type` = $type";
	//echo $query;
	$fetchData = mysql_query($query);
} else {
	// Missing one or more search terms
}
} else {
// Script was accessed without a form being submitted. Can redirect, display search form, or whatever here.
}

[petenaylor]

Hi all

 

I am creating a product filter system with a drop down menu which works well except it needs to select certain products out of the SQl depending on what is in the URL Parameter. If the URL is like this:

 

product.php?category=stone

 

I only want it to SELECT * FROM `products` WHERE category = '".$_GET['category']."'");

 

However, if the URL is:

 

product.php?category=stone&type=sandstonepatiopaving

 

I want it to SELECT * FROM `products` WHERE category = '".$_GET['category']."' AND type = "'.$_GET['type']."'");

 

And if the URL is:

 

product.php?category=stone&type=sandstonepatiopaving&colour=lightgrey

 

I want it to SELECT * FROM `products` WHERE category = '".$_GET['category']."' AND type = "'.$_GET['type']."' AND colour = "'.$_GET['colour']."'");

 

How would I write the code to do this and if there is nothing in the URL so it's just product.php to get all from the products table?

 

Thanks

Pete

[intellix]

Ok so you're saying you want to have this done without putting anything into the URL? You can either have it take those parameters and store them in a session, redirect to the page you're talking about or instead POST the data rather than GET

 

So you have a form that uses POST instead of GET it won't be in the URL and you retrieve it instead by $_POST...

 

Or do you mean that you want it to work as it is but when there are no parameters you want it to retrieve everything? For that you can just do a test to see if there are no parameters like so:

<?php
if (!isset($_GET['category']) && !isset($_GET['category']) && !isset($_GET['category'])){
       $result = mysql_query(SELECT * FROM `products`");
}else{
        // do the other stuff
}
?>

 

Also just suggesting that you make sure everything you're putting into your query is escape properly as without any form of validation you're giving users full access to do whatever they want with your system

 

I hope I answered something... I'm not quite sure what the question was ^^

[petenaylor]

Yes that second option seems to be along the right tracks.

 

I have a drop down menu that filters out the products by sending variables into the URL so when the URL in empty and is just product.php it should fetch all of the products from the mySQL table.

 

Otherwise if the category is set it should select from the table where there are products in that category. The same for the type and the colour.

 

Thanks for you help!

[intellix]

np I don't see why you lose the ability to edit your posts after so long... I have errors in the code and I think its bad when you can't correct them