Making a keyword search box

lizzyd

hello!

I hope someone can help me! I'm trying to make a search box for my hotel search site...

I have a big database with field names such as Town, City, and then also lots of fieldnames indicating each hotel's facilities, like Tennis, Creche, Restaurant.... The values in these latter fields are either Y or N.

I want to allow users to enter a town/city in one input box, and then one or more keywords (eg. creche, tennis) in a second inbox, and then click submit. My code for the search box so far looks like this:

<?php
//create search form	
	echo "<form action='searchresults.php' method='post'>

		<tr>
		<td class=\"lineheight\" height=\"4\">&nbsp;</td>

		<td align=\"right\" rowspan=\"5\" valign=\"top\" width=\"45\" class=\"searcharrow\">
		<input type=image src=\"http://www.hotelheaven.co.uk/searcharrow.gif\" border=\"0\">
		</td>
		</tr>

		<tr>
		<td valign=\"top\" class=\"welcomewhite\">
		Town/City<br>
		<input type=\"text\" name=\"search\" class=\"searchbox\" size=\"26\">
		</td>
		</tr>

		<tr>
		<td class=\"lineheight\" height=\"2\">&nbsp;</td>
		</tr>			

		<tr>
		<td valign=\"top\" class=\"welcomewhite\" width=\"142\">Keywords<br>
		<input type=\"text\" name=\"keywords\" class=\"searchbox\" size=\"26\">
		</td>
		</tr>


		 </form>"
?>

and for calling the results on the next page i have:

$place = $_POST['search']; 
$keywords = $_POST['keywords']; 
$query = "SELECT Region FROM hotel WHERE (Town LIKE '$place' OR County LIKE '$place') OR fieldnames LIKE '%$keywords%'ORDER BY $sortBy LIMIT $offset, $limit";

My first question is - is there a generic word for all fieldnames? I know i can't just write "fieldnames", but there are about 100 of them - do i have to list each one separately in my select query??
I also know i need to include an AND field value = "Y" type bit - but i'm not sure how to word this either....

My second question is - have i got the general gist of what needs to be done? Say someone writes in 'creche, tennis' - I only want hotels that match for creche AND tennis, not one or the other!

In fact, i have a lot of questions like this and don't expect everyone to answer it all for me! honest! i really think i need a decent tutorial but have been searching all day and not found one that suits - i'd be really grateful if anyone could point me towards one!

Any help at all would be much appreciated! Thanks!

LizzyD

devinemke

i suggest a complete overhaul of your data structure. 3 tables:

hotels
hotel_id (INT)
name (VARCHAR)
address (VARCHAR)
city (VARCHAR)
state (VARCHAR)
zip (VARCHAR)
phone (VARCHAR)

amenities
amenity_id (INT)
name (VARCHAR)

amenity_link
amenity_link_id (INT)
hotel_id (INT)
amenity_id (INT)

hotels in 1 table (with address info), amenities in another and a 3rd table to link the 2.
the amenities data might look like this:
1 tennis
2 spa
3 suana
4 golf

the amenity_link relates the amenity to the hotel. i would then create a form that has 2 text fileds: city and state. and a list of checkboxes for amenities. you could simply query the amenities table to get the array of possible amenities. the user would be able to check all or none of the amenities. then query the hotels table for matching city and state and do a table join to get the relavent amenities.

lizzyd

Thanks for the advice devinemke.

The only problem is that I've got about 3000 hotels in my database, and about 100 different facilities, so I'd really like a solution which doesn't involve me making a new database as it would take forever.

Also, I don't have room on my site to list all the options and then let users select them - I really need them to be able to type in keywords and click 'Go'...

A tricky one, I know.... Would appreciate it if anyone has any more ideas on it! (Thanks again for replying though Devinemke!)

LizzyD