Jeff
asked on
Send Multiple Select box to a database query
I am trying to send a Multiple select box toa database query. This is what I have so far.
<SELECT NAME="BR">
<OPTION VALUE="" SELECTED>Any number</OPTION>
<OPTION VALUE="1">1 or more</OPTION>
<OPTION VALUE="2">2 or more</OPTION>
<OPTION VALUE="3">3 or more</OPTION>
<OPTION VALUE="4">4 or more</OPTION>
<OPTION VALUE="5">5 or more</OPTION>
<OPTION VALUE="6">6 or more</OPTION>
</SELECT>
<SELECT NAME="BTH">
<OPTION VALUE="" SELECTED>Any number</OPTION>
<OPTION VALUE="1">At least 1 Full Bath</OPTION>
<OPTION VALUE="2">At least 2 Full Baths</OPTION>
<OPTION VALUE="3">At least 3 Full Baths</OPTION>
<OPTION VALUE="4">At least 4 Full Baths</OPTION>
<OPTION VALUE="5">At least 5 Full Baths</OPTION>
</SELECT>
<SELECT NAME="CTY[]" SIZE=4 MULTIPLE multiple>
<option value="Alba">Alba</option>
<option value="Alto">Alto</option>
<option value="Arp">Arp</option>
<option value="Athens">Athens</option>
<option value="Atlanta">Atlanta</option>
<option value="Avinger">Avinger</option>
<option value="Ben Wheeler">Ben Wheeler</option>
<option value="Big Sandy">Big Sandy</option>
<option value="Bivins">Bivins</option>
<option value="Bogata">Bogata</option>
</SELECT>
<SELECT NAME="TYP">
<option value="" selected>Any</option>
<option value="Condominiums">Condominiums</option>
<option value="Garden Homes">Garden Homes</option>
<option value="Manufactured Homes">Manufactured Homes</option>
<option value="Modular Homes">Modular Homes</option>
</SELECT>
<?php
$queryBuild = "";
if ($BR != "") { $queryBuild .= " AND BR>='$BR'"; }
if ($BTH != "") { $queryBuild .= " AND BTH>='$BTH'"; }
if (is_array($CTY) AND sizeof($CTY) == 1)
{
$queryBuild .= " AND CTY>='$CTY'";
}
if (is_array($CTY) AND sizeof($CTY) > 1)
{
$queryBuild .= "AND (";
foreach($CTY as $value)
{
$queryBuild .= "CTY>='$CTY' OR";
}
$queryBuild .= ")";
}
if ($TYP != "") { $queryBuild .= " AND TYP>='$TYP'"; }
Results with 1 city selected
$SQL = SELECT LN FROM mls WHERE BR>='2' AND BTH>='1' AND CTY='Alba' AND TYP='Condominiums'";
Results with 2+ city selected
$SQL = "SELECT LN FROM mls WHERE BR>='2' AND BTH>='1' AND (CTY='Alba' OR CTY='Alto' OR CTY='Arp' OR CTY='Athens') AND TYP='Condominiums'";
ASKER
OK, I am not sure I understand. I fixed the code and still get nothing?
$queryBuild = "";
if ($LPGT != "") { $queryBuild .= " AND LP <= '$LPLT' AND LP >= '$LPGT'"; }
if ($BR != "") { $queryBuild .= " AND BR>='$BR'"; }
if ($BTH != "") { $queryBuild .= " AND BTH>='$BTH'"; }
if (is_array($CTY) AND sizeof($CTY) == 1)
{
$queryBuild .= " AND CTY>='$CTY'";
}
if (is_array($CTY) AND sizeof($CTY) > 1)
{
$queryBuild .= "AND (";
foreach($CTY as $value)
{
$queryBuild .= "CTY=$CTY' OR";
}
$queryBuild .= ")";
}
}
$sql = "SELECT LN FROM mls WHERE TypMain = 'Residential' $queryBuild";
$result = mysql_query($sql) or die ('Database error: ' . $sql . "\n" . 'MySQL Said : '. mysql_error());
$num_rows = mysql_num_rows($result);
// Display the results
echo $num_rows;
echo "SQL = $sql";
RESULT
2842SQL = SELECT LN FROM mls WHERE TypMain = 'Residential' AND LP <= '10000000000' AND LP >= '10000' AND BR>='2' AND BTH>='1'
Here is the Array:
Array
(
[LN] =>
[LPGT] => 10000
[LPLT] => 10000000000
[BR] => 2
[BTH] => 1
[CTY] => Array
(
[0] => Alto
[1] => Arp
[2] => Athens
)
[ZIP1] =>
[ZIP2] =>
[ZIP3] =>
[ZIP4] =>
)
$queryBuild = "";
if ($LPGT != "") { $queryBuild .= " AND LP <= '$LPLT' AND LP >= '$LPGT'"; }
if ($BR != "") { $queryBuild .= " AND BR>='$BR'"; }
if ($BTH != "") { $queryBuild .= " AND BTH>='$BTH'"; }
if (is_array($CTY) AND sizeof($CTY) == 1)
{
$queryBuild .= " AND CTY>='$CTY'";
}
if (is_array($CTY) AND sizeof($CTY) > 1)
{
$queryBuild .= "AND (";
foreach($CTY as $value)
{
$queryBuild .= "CTY=$CTY' OR";
}
$queryBuild .= ")";
}
}
$sql = "SELECT LN FROM mls WHERE TypMain = 'Residential' $queryBuild";
$result = mysql_query($sql) or die ('Database error: ' . $sql . "\n" . 'MySQL Said : '. mysql_error());
$num_rows = mysql_num_rows($result);
// Display the results
echo $num_rows;
echo "SQL = $sql";
RESULT
2842SQL = SELECT LN FROM mls WHERE TypMain = 'Residential' AND LP <= '10000000000' AND LP >= '10000' AND BR>='2' AND BTH>='1'
Here is the Array:
Array
(
[LN] =>
[LPGT] => 10000
[LPLT] => 10000000000
[BR] => 2
[BTH] => 1
[CTY] => Array
(
[0] => Alto
[1] => Arp
[2] => Athens
)
[ZIP1] =>
[ZIP2] =>
[ZIP3] =>
[ZIP4] =>
)
There is a missing apostrophe here:
$queryBuild .= "CTY=$CTY' OR";
It should be
$queryBuild .= "CTY='$CTY' OR";
The other one is still wrong:
$queryBuild .= " AND CTY>='$CTY'";
Don't use >= (larger or equal), use = only.
But there is something else going on... the cities are not beeing put into the query at all. The array you are showing, I suppose that is the $_POST array? Where in your script is it output?
There is a missplaced } in there... I suppose this is not the full script. Can you show the full script?
When you are testing numerical values, you should not use quotes. It will give surprising results. For instance, "2" is larger than "10"... when you use quotes, the database uses alphabetical comparison, and "2" is later in the alphabet than "10".
$queryBuild .= "CTY=$CTY' OR";
It should be
$queryBuild .= "CTY='$CTY' OR";
The other one is still wrong:
$queryBuild .= " AND CTY>='$CTY'";
Don't use >= (larger or equal), use = only.
But there is something else going on... the cities are not beeing put into the query at all. The array you are showing, I suppose that is the $_POST array? Where in your script is it output?
There is a missplaced } in there... I suppose this is not the full script. Can you show the full script?
When you are testing numerical values, you should not use quotes. It will give surprising results. For instance, "2" is larger than "10"... when you use quotes, the database uses alphabetical comparison, and "2" is later in the alphabet than "10".
ASKER
<?php
print "<pre>\n";
print_r($_REQUEST);
print "</pre>\n";
?>
<br>
<br>
<?php
$LN = make_safe($_POST['LN']);
$LPGT = make_safe($_POST['LPGT']);
$LPLT = make_safe($_POST['LPLT']);
$BR = make_safe($_POST['BR']);
$BTH = make_safe($_POST['BTH']);
$CTY = make_safe($_POST['CTY']);
$AR = make_safe($_POST['AR']);
$ZIP1 = make_safe($_POST['ZIP1']);
$ZIP2 = make_safe($_POST['ZIP2']);
$ZIP3 = make_safe($_POST['ZIP3']);
$ZIP4 = make_safe($_POST['ZIP4']);
$POOL = make_safe($_POST['POOL']);
$eYOC = make_safe($_POST['eYOC']);
$lYOC = make_safe($_POST['lYOC']);
$WATER_FRONT = make_safe($_POST['WATER_FRONT']);
$WATER_VIEW = make_safe($_POST['WATER_VIEW']);
$STR = make_safe($_POST['STR']);
$HSN = make_safe($_POST['HSN']);
$LAKE = make_safe($_POST['LAKE']);
if ($LN != "")
{
$queryBuild = " AND LN='$LN'";
}
else
{
$queryBuild = "";
// if ($TYP != "") { $queryBuild .= " AND TYP = '$TYP'"; }
if ($LPGT != "") { $queryBuild .= " AND LP <= '$LPLT' AND LP >= '$LPGT'"; }
if ($BR != "") { $queryBuild .= " AND BR>='$BR'"; }
if ($BTH != "") { $queryBuild .= " AND BTH>='$BTH'"; }
if (is_array($CTY) AND sizeof($CTY) == 1)
{
$queryBuild .= " AND CTY='$CTY'";
echo sizeof($CTY);
}
if (is_array($CTY) AND sizeof($CTY) > 1)
{
$queryBuild .= "AND (";
foreach($CTY as $value)
{
$queryBuild .= "CTY='$CTY' OR";
}
$queryBuild .= ")";
}
/*
// {if 'AR' %cmd% variableExists:} AND (AR={set ARstring ' OR AR=' AR concatWith:}{ARstring}){/if}
if ($ZIP1 != "") { $queryBuild .= " AND (ZIP='$ZIP1'";
if ($ZIP2 != "") { $queryBuild .= " OR ZIP='$ZIP2'"; }
if ($ZIP3 != "") { $queryBuild .= " OR ZIP='$ZIP3'"; }
if ($ZIP4 != "") { $queryBuild .= " OR ZIP='$ZIP4'"; }
$queryBuild .= ")";
}
if ($POOL = "Y") { $queryBuild .= " AND POOL>''"; }
if ($eYOC != "") { $queryBuild .= " AND YOC>='$eYOC'"; }
if ($lYOC != "") { $queryBuild .= " AND YOC<='$lYOC'"; }
if ($WATER_FRONT != "") { $queryBuild .= " AND WATER_FRONT='$WATER_FRONT'"; }
if ($WATER_VIEW != "") { $queryBuild .= " AND WATER_VIEW='$WATER_VIEW'"; }
if ($STR != "") { $queryBuild .= " AND STR LIKE '$STR'"; }
if ($HSN != "") { $queryBuild .= " AND HSN='$HSN'"; }
if ($LAKE != "") { $queryBuild .= " AND Lake_Name LIKE '$LAKE'"; }
*/
}
$sql = "SELECT LN FROM mls WHERE TypMain = 'Residential' $queryBuild";
$result = mysql_query($sql) or die ('Database error: ' . $sql . "\n" . 'MySQL Said : '. mysql_error());
$num_rows = mysql_num_rows($result);
// Display the results
echo $num_rows;
echo "SQL = $sql";
?>
What does the function make_safe() do?
ASKER
function make_safe($variable) {
$variable = mysql_real_escape_string(t
return $variable;
}
$variable = mysql_real_escape_string(t
return $variable;
}
This function does not work for arrays. It should be like this:
function make_safe($variable) {
if(is_array($variable))
$variable = array_map('make_safe',$variable);
else
$variable = mysql_real_escape_string(trim($variable));
return $variable;
}
ASKER
I suspected that might be a problem! I tried commenting it out, with no luck.
I had to fix this:
if (is_array($CTY) AND sizeof($CTY) == 1)
{
foreach($CTY as $value)
{
$queryBuild .= " AND CTY='$value'";
}
}
And now it works with 1 city selected.
With multiple cities I get this error because it adds an extra "OR" ro the query
Database error: SELECT LN FROM mls WHERE TypMain = 'Residential' AND LP <= '10000000000' AND LP >= '10000'AND (CTY='Alba' OR CTY='Alto' OR CTY='Arp' OR ) MySQL Said : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1
if (is_array($CTY) AND sizeof($CTY) > 1)
{
$queryBuild .= "AND (";
foreach($CTY as $value)
{
$queryBuild .= "CTY='$value' OR ";
}
$queryBuild .= ")";
}
How do I get it to step through and only add an "OR" if there is more in the array?
I had to fix this:
if (is_array($CTY) AND sizeof($CTY) == 1)
{
foreach($CTY as $value)
{
$queryBuild .= " AND CTY='$value'";
}
}
And now it works with 1 city selected.
With multiple cities I get this error because it adds an extra "OR" ro the query
Database error: SELECT LN FROM mls WHERE TypMain = 'Residential' AND LP <= '10000000000' AND LP >= '10000'AND (CTY='Alba' OR CTY='Alto' OR CTY='Arp' OR ) MySQL Said : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1
if (is_array($CTY) AND sizeof($CTY) > 1)
{
$queryBuild .= "AND (";
foreach($CTY as $value)
{
$queryBuild .= "CTY='$value' OR ";
}
$queryBuild .= ")";
}
How do I get it to step through and only add an "OR" if there is more in the array?
There are several ways this can be done, try one of these:
# remove the last OR after the loop:
if (is_array($CTY) AND sizeof($CTY) > 1)
{
$queryBuild .= "AND (";
foreach($CTY as $value)
{
$queryBuild .= "CTY='$value' OR ";
}
$queryBuild = substr($queryBuild,0,strlen($queryBuild)-4);
$queryBuild .= ")";
}
# use a temporary array
if (is_array($CTY) AND sizeof($CTY) > 1)
{
$cityArray = array();
$queryBuild .= "AND (";
foreach($CTY as $value)
{
$cityArray[] = "CTY='$value'";
}
$queryBuild .= implode(' OR ',$cityArray).")";
}
ASKER
Here is what I finally came up with. Are there any improvments that can be made?
if (is_array($CTY) AND sizeof($CTY) == 1)
{
foreach($CTY as $value)
{
$queryBuild .= " AND CTY='$value'";
}
}
if (is_array($CTY) AND sizeof($CTY) > 1)
{
$arrayct = count($CTY);
$queryBuild .= " AND (";
foreach($CTY as $value)
{
if ($arrayct > 1)
{
$queryBuild .= "CTY='$value' OR ";
}
if ($arrayct == 1)
{
$queryBuild .= "CTY='$value'";
}
$arrayct--;
}
$queryBuild .= ")";
}
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
cxr, that is awesome! You have been a great help!
ASKER
Thanks!
if (is_array($CTY) AND sizeof($CTY) == 1)
{
$queryBuild .= " AND CTY>='$CTY'";
}
if (is_array($CTY) AND sizeof($CTY) > 1)
{
$queryBuild .= "AND (";
foreach($CTY as $value)
{
$queryBuild .= "CTY>='$CTY' OR";
}
$queryBuild .= ")";
}
You are testing for the value of a string, and should use = (equals), not >= (larger or equal).
$queryBuild .= " AND CTY='$CTY'";
$queryBuild .= "CTY='$CTY' OR";