Simple Table Search

dannylewin

Hey,

Im wanting to allow people to search a table using some or all options available. But using to following method isnt very reliable and i was wondering if anyone had a better solution:

SELECT classified_dress.i_id, classified_dress.i_name ,  classified_dress.i_price, classified_dress_img.img_file, classified_dress.i_desc, classified_dress.i_madeby, classified_age.a_ages FROM classified_dress LEFT JOIN classified_dress_img ON classified_dress.i_id = classified_dress_img.i_id LEFT JOIN classified_age ON classified_dress.i_agerange = classified_age.a_id WHERE classified_dress.i_paid = '1' AND classified_dress.i_id != '1' AND classified_dress.i_desc LIKE '%$q%' AND classified_age.a_ages = '%$age%' GROUP BY classified_dress.i_id ORDER BY ".$order."

Im wanting to allow people to search dress listings using options like keywords, age range, designer, condition etc... but using LIKE '%%' isnt the best option im guessing.

And also. When i try to order the results by price (low to high) and (high to low) it doesnt work. I have one at £600 and one at £90 but they dont appear in the right order.

Thanks,
Danny

Roger_Ramjet

The wrong order in the price is because you are not using a numeric column type so the db cannot sort properly, instead it sorts alphabetically and 9 is higher than 6. Change the price to a decimal type column, and add a column for currency if you need to deal with multi-currency listings.

You probably need to be using FULL TEXT SEARCH to acheive what you want.

dannylewin

Thats really cool, thanks!

what would i do if i was using 4 search options where they can use as many or few as they would like.

So if i were to allow them to search for:

Age range
Dress Type
Condition
Designer

how would i include these in the search without LIKE or = taking in to account any of these search options could be empty?

My query string looks like this:

dress_search.php?q=&age=7&type=&condition=1&designer=&orderby=1

$q will search the items description and can be blank as can the following:

$age, $type, $condition and $designer : info is held in another table and is referenced by the primary key.

any further help would be GREAT!
Danny Lewin