Jump to content

Select statement and drop down menus.


AGazzaz
 Share

Recommended Posts

Hello,I have 5 drop down lists the user should use them to search the website database so he can get the most specific information possiblethe first two lists are populated using php and mysql the rest is htmlThe first problem is: The first entry in all these menus is "All" so if the user leaves it without changing this means the whole column will be searchedand if all the fields are left this means all the entries in the database will be displayed

$cat=$_POST['cat'];$price=$_POST['price'];mysql_connect("IP", "USER", "PASS") or die(mysql_error()); mysql_select_db("amaar") or die(mysql_error()); $data = mysql_query("SELECT * FROM search WHERE cat_id = '$cat' AND price = '$price' ") or die(mysql_error());

I know I have to change the value attribute in <option value=""> but I do not know what to put in itThe second problem is: The price provided in the menu is a price range, ie:"10000 to 20000" , I know that I will use BETWEEN in the sql query but I do not know what to put in the value attribute so the column can be searchedI hope what I wrote is clear and you can understand what I am trying to doThanks.

Link to comment
Share on other sites

This isn't a straight insertion where you can insert a value to search for all. Don't just have 1 query you can insert any value to do 5 different things, you need to use some logic and check if various things were filled out and build the query dynamically. If they set the "cat" to be "all", you don't even want the cat to show up in the WHERE clause at all.Also, if you're inserting things from $_POST directly into the SQL query you're just asking for a SQL attack. You need to use mysql_real_escape_string to sanitize strings, and for numbers like the price cast them as numbers using intval or floatval. Even if it's coming from a dropdown list, you don't want someone to create their own form that submits to your page with all text fields and have them type a bunch of SQL code in.

Link to comment
Share on other sites

Don't just have 1 query you can insert any value to do 5 different things, you need to use some logic and check if various things were filled out and build the query dynamically.
I do not think what I understood was correct but do you mean that for the 5 drop down lists I have to make 25 querys using If, elseif, else covering all the possible user input chances, or there is another smarter way to do this.
You need to use mysql_real_escape_string to sanitize strings,
my cat variable will look like this
$cat = mysql_real_escape_string($_POST['cat']);

numbers like the price cast them as numbers using intval or floatval.
So my price variable will look like this:
$price=floatval($_POST['price']);

Thank you for the tips, but my question was how to search a price range from 100 to 200 and I want the query to retrieve the values in between

Link to comment
Share on other sites

OK, that query would be something like this:SELECT * FROM table WHERE price BETWEEN 100 AND 200You don't need to make 25 different queries, you just build the one query dynamically and add whichever parts need to be added.

$sql = "SELECT * FROM table";$where = "";if ($cat != ""){  if ($where != "")	$where .= " AND ";  $where .= " cat_id='{$cat}'";}if ($price != ""){  if ($where != "")	$where .= " AND ";  $where .= " price='{$price}'";}if ($where != "")  $where = " WHERE " . $where;$sql .= $where;

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...