• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 422
  • Last Modified:

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'";

Open in new window

0
Jeff
Asked:
Jeff
  • 7
  • 6
1 Solution
 
Roger BaklundCommented:
This part of your code is wrong:

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";
0
 
JeffAuthor Commented:
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] =>
)
0
 
Roger BaklundCommented:
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".
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
JeffAuthor Commented:

<?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";
?>

Open in new window

0
 
Roger BaklundCommented:
What does the function make_safe() do?
0
 
JeffAuthor Commented:
function make_safe($variable) {
    $variable = mysql_real_escape_string(trim($variable));
    return $variable;
}
0
 
Roger BaklundCommented:
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;
}

Open in new window

0
 
JeffAuthor Commented:
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?
0
 
Roger BaklundCommented:
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).")";
}

Open in new window

0
 
JeffAuthor Commented:
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 .= ")";
}

Open in new window

0
 
Roger BaklundCommented:
The below snippet does the same thing with less code. Note that the implode() function will return a single item in the input array as a string without the separator. In other words, if there is a single CTY in the array, the result appended to $queryBuild is "AND (CTY='Alto')". The parantheses are not needed when no OR is used, but it is not an error if they are there. When you combine AND and OR in the sql expression, it is important to use parantheses to get correct results.
if(is_array($CTY)) {
  $cityArray = array();
  $queryBuild .= "AND (";
  foreach($CTY as $value)
    $cityArray[] = "CTY='$value'";
  $queryBuild .= implode(' OR ',$cityArray).")";
}

Open in new window

0
 
JeffAuthor Commented:
cxr, that is awesome! You have been a great help!
0
 
JeffAuthor Commented:
Thanks!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now