Jump to content

Select statement and drop down menus.


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 post
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 post
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 post
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 post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...