allowing users to do an advanced search on my database

[morpheusoptic]

I have successfully finished my registration, login and ad placement page for a free classifieds section of my website that I have been working on. Now that I have a way of my users entering information into the database, I am trying to find a way for them to be able to do an advanced search of everything instead of having to display only a few pre-defined columns from the tables in the database that I have set up, "for now".

The advanced search page is going to be made up of text boxes, radio buttons and check boxes.... I am going to have it set up to have a default setting for everything, so if the user decides not to click a radio button or check a box, or fill out a field, the script will resort to my preset defaults for those categories, to simplify the script.

The biggest feature I need is to allow a user to select a price range of products, say I have 3 set price ranges.... If I set that up in 2 or 3 radio buttons the user can choose from, the value of them will be set to a single variable.... take a look at my current code and I will explain more below it.



$sql = "SELECT * FROM ad_details WHERE ptype LIKE '%$_POST[ptype]%' AND asking < '%$_POST[price]%' AND state LIKE '%$_POST[state]%' ORDER BY price DESC LIMIT 25";
$query = mysql_query($sql);
$total_results = mysql_num_rows($query);




If you look at the asking < $_POST[price], it basically is saying show columns that only have a price less than whatever the variable $_POST['price'] is set to. Now if my form is currently set up to allow the user to search a price at or below $50 or above $50... how do I make the code work. I know with the current way, if they picked below $50, it would only show ads below $50... if they picked above $50, I would have to set the variable really high, and than it would show not only above but also below $50. Hopefully there is a way of doing this easily... I am sure there is because you see it all the time, however I am lost.

Last thing.... I was reading somewhere about database queries and they said the word LIKE in the SQL command means "="... is this true? If not, should I change LIKE to = in the statement above... I need it to equal that variable, that why I placed LIKE in there. Please check they syntax of my statement as I am new to adding all these LIKE & AND words in it, and also parsing variables into the SQL command... I am sure I have errors!

Thanks in advance everyone, I look forward to getting this running.

Paul

[chump2877]

I'm new to SQL, too, and I didn't follow some of the stuff you said there (And there was a lot of it!), but I can tell you that LIKE is generally used in SQL to match a variable string with a similar string in your database....

For example:

SELECT price FROM parts WHERE product_code LIKE '%$product_code[0]%'

In this line of SQL you are telling the server to find the element of the column "price" in the table "parts" where that element's corresponding "product_code" (an element of another column) matches the pattern of the string contained in the variable "$product_code".....So LIKE has more to do with matching a database element with a variable that has the same pattern, as opposed to declaring a variable and database element are equal...If you want to set a variable equal to a database element, or vice versa, there is probably another SQL operator that you could use...

Hope this helps a little... :)