Searching My database for multiple fields

[coolphpdude]

Hey again guys! This is a two part question...

 

scenario: I have a number of different tables containing a number of different fields and i want to give people on my site an advanced search. The easiest way to explain this is for you to think of it as an estate agent's website. People visit the site and search for houses based on a number of different criteria, for example they would have a number of diffent boxes they could narrow a search down such as 'number of rooms', 'type of property', 'price' etc... Now this is similar to my site, I want people to b able to choose to narrow there search down but not be forced to enter information into all boxes.

 

So i want a search where a user would be able to select the number of rooms they want a property to have but if they choose not to enter that information then the intial value of '0' (or null) would be submitted to the search query. A user should be able to enter number of rooms AND/OR property type AND/OR price. IF they dont enter that information then a value of '0' (or null) would be submitted for each of the options to the query however if they do select a value then this would form the query.

 

This is where im struggling. After racking my small brains and trying to think logically about how i would do this, my logic tells me (and correct me if im wrong) that if i choose too insert each variable 1 at a time into a search query such as

 

$search_result = mysql_query("SELECT * FROM tablename WHERE rooms='$rooms' AND type='$type' AND price='$price'",$database);

 

...then if a user hadnt entered how many rooms but had entered 'flat' as the property type they wanted then the sql would be searching the 'rooms' field for 0 (which it obviously WONT find) AND 'type' for 'flat' and will return no result even if a 'flat' did exist in the database

 

What I have considered is building up the query as i go, so for example...

 

if ($rooms != '0')

{

$query='rooms='$rooms' ';

}

 

so where a value is submitted that IS NOT null then 'build a part of the query'. Would this work? I then have the issue of the text i am trying to enter into the $query variable contains a '=' sign so it is saying my syntax is wrong and not working. How do i correct this?

 

That is where i am up to at the moment, am i heading in the right direction or is there an easier way of doing this? Please be aware that the site is not an estate agents site and will contain alot more searchable fields which is why i'd prefer to avoid building the query as each variable is checked for a null value because this would result in a lot of IF statements.

 

I know this is very long winded so my appologies for that.

 

Cheers

 

[lemmin]

Make a string to use in place of the rooms='$rooms'.

 

initialize it to :

$roomsstring = "LIKE '%'";

 

then, if the box is checked, you can set it to:

$roomsstring = "= '$rooms'";

 

Then, when you query:

mysql_query("SELECT * FROM tablename WHERE rooms " . $roomsstring . " AND ...";

[coolphpdude]

i dont understand strings and the LIKE part, sorry im fairly new to this. Could you explain a little?

[lemmin]

The strings simply change what the query is actually doing.

When you request "WHERE something = 'a value'", you are getting results that match that criteria directly. Since you can't ask for something to match the criteria of everything, you can ask it to find things similar to everything. The LIKE clause allows you to find substrings in the data. The "%" operator is used as a wildcard (normally on one or both sides of a string, to find it as a substring). When you use "LIKE '%'", you are asking for everything in that field, which is what you want to happen when the user has not checked the "search this field" box. You already know about the WHERE clause.

 

When you query the sql database, you send a command in the form of a string. For example:

 

mysql_query("SELECT * FROM tablename WHERE rooms = '$rooms');

 

is the same as

$roomsstring = "= '$rooms'";
mysql_query("SELECT * FROM tablename WHERE rooms $roomsstring");

 

I may have confused you with the syntax I used to concatinate the strings, I've just always used that way.

[coolphpdude]

i think i follow it. I will read it properly tomorrow and reply then.

 

Thanks for your reply, your help is much appreciated mate!

 

 

[coolphpdude]

I will give this a try now. The search options for a user would just be drop down menu's, for example the 'rooms' drop down menu would contain the values '1, 2, 3, 4, 5, 6, etc...' the 'house type' would contain 'flat, bungalow, bedsit, etc...' and the 'price' would contain '£100000, £110000, £120000, etc...'. The initial value would be set to 'not applicable' with an actual value of '0'.

 

so basically what i am looking to do is:

 

IF value is NOT 0

{

build query

}

else

{

move on

}

 

but this is going to be a lot of IF statements if i give the users a lot of search options. The user will not tick a box to say 'use this search field', i need to make it so that the search is automatically included if a user selects an option from the drop down list but not included if the user leaves it on its initial value.

[coolphpdude]

well im passed the problem about trying to insert text containing an '=' sign into a variable but i am still stuck!

 

//test query

if ($rooms != '0')
{
$roomsstring = "= '$rooms'";
}

else
{
echo "no rooms selected";
}

$query = mysql_query("SELECT * FROM tablename WHERE rooms $roomsstring");

 

I understand this bit but to make it also search a different field i have to come up with a way that automatically puts an 'AND' into the query to continue it on for the next field. Any ideas?