petenaylor Posted November 25, 2010 Share Posted November 25, 2010 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 Quote Link to comment Share on other sites More sharing options...
Pikachu2000 Posted November 25, 2010 Share Posted November 25, 2010 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. } Quote Link to comment Share on other sites More sharing options...
petenaylor Posted November 26, 2010 Author Share Posted November 26, 2010 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 Quote Link to comment Share on other sites More sharing options...
intellix Posted November 26, 2010 Share Posted November 26, 2010 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 ^^ Quote Link to comment Share on other sites More sharing options...
petenaylor Posted November 26, 2010 Author Share Posted November 26, 2010 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! Quote Link to comment Share on other sites More sharing options...
intellix Posted November 26, 2010 Share Posted November 26, 2010 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 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.