Search form with checkbox array - help with MYSQL

Need help writing the MYSQL statement to get search results

[aquas]

Hi,

I am using Dreamweaver because I am a designer who has jumped into server-side scripting and have little clue how to do the code writing. I like how Dreamweaver does it visually.

That being said, I may have reached the visual limits of Dreamweaver's power.

I have a form with which I have successfully retrieved the correct records from a single checkbox and a single textfield. But I want to also filter the results with a checkbox group. I have seen this question answered on this forum, but have not been able to put the pieces together in the right place.

The form on search.php searches apartment listings by move date, max rent and bedrooms. The bedrooms field is the checkbox group so I use name="bedrooms[]". It is on results.php that I am struggling. I think I need a variable to do something with the array and then use that variable in the SELECT statement.

Dreamweaver does this with the single value parameters, what do I need to add to get results for the bedrooms array?

<code>
$varCity_rsResult = "%";
if (isset($_POST['city'])) {
$varCity_rsResult = $_POST['city'];
}
$varMaxRent_rsResult = "%";
if (isset($_POST['maxRent'])) {
$varMaxRent_rsResult = $_POST['maxRent'];
}
$varDateAvailable_rsResult = "%";
if (isset($_POST['moveDate'])) {
$varDateAvailable_rsResult = $_POST['moveDate'];
}
mysql_select_db($database_Jedigcr1, $Jedigcr1);
$query_rsResult = sprintf("SELECT * FROM listings_aj WHERE listings_aj.city = %s AND listings_aj.dateavailable >= %s AND listings_aj.rent < %s", GetSQLValueString($varCity_rsResult, "text"),GetSQLValueString($varDateAvailable_rsResult, "date"),GetSQLValueString($varMaxRent_rsResult, "int"));
</code>

Thanks in advance for any help.

[eelixduppy]

Welcome to WebmasterWorld!

You should first check to see if

$_POST['bedrooms']

is an array so that you can handle it accordingly:

if([url=http://www.php.net/is-array]is_array[/url]($_POST['bedrooms'])) {
#construct your query from the array
#an example could be something like the following:
$query = "SELECT * FROM `table` WHERE `bedroom` IN ('";
$query .= [url=http://www.php.net/implode]implode[/url]("', '", $bedrooms)."')";
}
#execute query as normal

Of course your query construction will have other elements to it, but if you concatenate them correctly it should be fine.

Try to mess around with the code that I gave you to see if you can get something to work.

P.S. I forgot to mention that you must also account for the fact that it may NOT be an array, in which case only one checkbox was selected and you should handle it how you had it working before.

[edited by: eelixduppy at 5:00 pm (utc) on May 10, 2008]

[Pico_Train]

The purists won't like this one but this is often something I do in this case.

if(!empty($_POST['bedrooms']))
{
$bedrooms ="0";
foreach($_POST['bedrooms'] as $b)
{
$bedrooms .= ",".$b;
}
}else{

$bedrooms = "";
}
Then in your sql statement you can add something like this:

if(!empty($_POST['bedrooms']))
{

$bedroom_query = "AND bedroom IN ($bedrooms)"

}else{

$bedroom_query = "";

}

And your query would look like so:

"SELECT * FROM listings_aj
WHERE listings_aj.city = %s
$bedroom_query";

[edited by: Pico_Train at 6:46 am (utc) on May 10, 2008]