How to structure a query to search a string.

sgtpepper

A field in my database contains a string of upto 150 characters. For example:

A05B02C03D01E01G02H06F01I09J04K01N01N02Q01R04L01L03

Combos of three characters represent 'something'
For example:

A05 - Swimming Pools
B02 - Hot Tub
C03 - Hot Babe

So, the above string represents all the ammenities that is part of this particular listing.

I would like to be able to have a form were someone can search for specific ammenitites. So, lets say I have a form with a series of checkboxes 1 box for swimming pool with value of A05, another box for Hot Tub with a value of B02 and so on.

I think I need to group the checkboxes and create and array. So, something like features[] should be the variable for the checkboxes.

Here is where I am stuck. How can I structure my query so that it looks for data with the ammenities given in the checkboxes?

bubblenut

Well, it's not a very relational database design :rolleyes:. You should have an ameneties table, and then a link table between the one with this column in and the ameneties table. Is redesigning the database an option or is this for an existing system?

sgtpepper

Yes, I realize this.....but I didnt create the table....it is a feed from another database....I just have to try to make this sh** work! 🙂

bubblenut

Well guess you need a hand from, da da DA, The Dirty Hack Monster.
WHooooosshh
/DHM swoops in and takes over Bubble's computer
GGGRAAAAAAGHHH!!!!

//Searching for sites with A05 D01 & K01
$search=array('A05', 'DO1','KO1');

//If the order cant be assured
$sql="SELECT name, location FROM table WHERE ammenities LIKE '%AO5%' && ammenities LIKE '%DO1%' && ammenities LIKE '%KO1%'";
//if you know the order, then order in which they'll appear then order the array and then
$sql="SELECT name, location FROM table WHERE ammenities LIKE '%AO5%DO1%KO1%'";

/DHM flies off to seek out those who made this database and poke them with a stick

Did he help you? He's not much of a talker but he usually gets the goods out 🙂
Bubble

sgtpepper

Thanks for the help....but...I am not sure this is what I am looking for.

See the form will have checkboxes....many of them. All the values of the chosen checkboxes will be stored in features[]

I am not sure how to structure the query to search for amenities that match features[]

bubblenut

Like this 😃

$ammenities; //the array with all the ammenities is
$sql="SELECT name, location FROM table WHERE ammenities LIKE '%".implode("%' && ammenities LIKE '%", $ammenities)."%'";

If anyone knows of a way in which sprintf or the like can be used to better acieve this then please post it.

Bubble

sgtpepper

So I have my complete query like this:

$numresults = mysql_query("SELECT *,IF (lister_office_name like '$client_name', 1, 0)
AS featured
FROM mastertable
WHERE ( $selecttownstring )
AND list_price BETWEEN $lowprice AND $highprice
AND bedrooms like '$bedroomsel'
AND baths like '$bathselect'
AND features like '%'.implode('%' && features LIKE '%', $features).'%'
ORDER BY featured DESC, town ASC, type ASC, list_price DESC
"); // the query.

I get the error message :

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource on line 112
No results found matching your query

Did I incorporate the code wrong?

bubblenut

/slap!

You didn't read the code right

$sql="SELECT *,IF (lister_office_name like '$client_name', 1, 0) 
AS featured 
FROM mastertable 
WHERE ( $selecttownstring ) 
AND list_price BETWEEN $lowprice AND $highprice 
AND bedrooms like '$bedroomsel' 
AND baths like '$bathselect' 
AND features like '%'".implode("%' && features LIKE '%", $features)."'% ORDER BY featured DESC, town ASC, type ASC, list_price DESC"; // the query.
echo($sql);
$res=mysql_query($sql);

It's always a good idea to put your query into a variable before you run it so that you can echo it out and check it's as expected if you get any errors.