...

View Full Version : using LIKE with mulitple search options?



cooper3000
01-11-2006, 06:51 PM
Search form made up of 5 options.

1.Category
2.Business Name
3.Town
4.County
5.Postcode

I indicate to the user that they must enter data into box1 (category) or box2 (businessname), they are then asked to enter data into box3, or box4, or box5. Then press Search!

eg. user1 enters 'internet' into box1 and 'london' into box3, then presses search.

however

user2 could enter 'website world' into box2 and 'london' into box3, then presses search.

I need to find out how to script the Query so that mysql knows to return only data holding the info requested from the completed search form and ignores the non completed search boxes.

I would like to further complicate things by saying that i wish for boxes 1 & 2 to have the option of returning records that include part of the text entered..

eg. if 'internet web design' was entered into box1, all records in the category column containing the first word (in this case 'internet') were shown in the results.

If any one can help me, then please get in touch

cooper

arnyinc
01-11-2006, 09:53 PM
Use if-statements to see if the user typed something into the box. If they did, then you can append an AND clause to your SQL statement telling it to search for that.

$sql="select from my table where 1=1 "
if ($category!="")
$sql.=" and category='".$category."'"
if ($businessname!="")
$sql.=" and businessname='".$businessname."'"

For your second question, are you looking for a partial search? If the user enters "internet" you would return "internet web design" and "internet something or other". That's easy to do with the LIKE clause:

SELECT * FROM mytable WHERE field1 LIKE '%internet%'

You might want to force field1 and your search term into lowercase (or uppercase) so they aren't case-sensitive. You explained it differently, but I think that is what you want.

cooper3000
01-12-2006, 09:41 AM
Thats spot on, thanks very much.

Something else i was also wandering....

when a user searches I currently return the following details:

Business Name
Address
Town
County
Post Code
Telephone

I have introduced a payment scheme where the idea is that if the business has paid a subscription the following details then become viewable to the user (also priority is given to these member).

Business Name
Address
Town
County
Post Code
Telephone
Fax
Email
Website
Category
Feature 1
Feature 2

I have been able to display one or the other, is it possible to have them viewed together, so that paid subscriptions get their full details viewable and ahead of the unpaid, who have the minimum details viewable to users?

(to find out if someone has paid I created a field in my table which details either paid or unpaid)

Many Thanks again

Cooper

:thumbsup:

degsy
01-12-2006, 01:52 PM
use ORDER BY on the subscription field



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum