matching array elements against array in database

[mrMarcus]

Ok, what i have so far is a form that users will be filling out to search the database .. i am stuck on allowing the use of checkboxes as an added feature.

 

i have a field in the database called 'utilities', and the info stored in it is inputted into it using implode(|) .. so an example would be, Water|Hydro|Heat|Cable.

 

now, the value's from the checkboxes are coming through as an array, and i need to somehow run the selected checkboxes against the values in the 'utilities' field and display the appropriate results .. the results must contain at least the selected values in the form, no less .. so if all 4 checkboxes are selected, then results containing all 4 values must be returned .. no less than that.

 

i gave it my best, but i keep coming up short .. i'm also getting some results displaying twice, and am pretty sure that has something to do with the 'while' being inside a 'foreach'.

 

any and all help would be fantastico.

 

my form...

<form action="do" method="POST" enctype="mutlipart/form-data">
      <input type="checkbox" name="u[]" value="Cable">Cable<br />
      <input type="checkbox" name="u[]" value="Water">Water<br />
      <input type="checkbox" name="u[]" value="Hydro">Hydro<br />
      <input type="checkbox" name="u[]" value="Heat">Hydro<br />
      <input type="text" name="city" value=""><br />
      <input type="submit" value="Search" name="submit">
</form>

my php...

if (isset($_POST['submit'])) {
      if (is_array($_POST['u'])) {
            $asdf = $_POST["u"];
            $arrCount = count($asdf);
            //print_r($asdf);
            foreach($asdf as $key => $value){                  
                  $query = "SELECT * FROM table WHERE iCity=\"$city\" AND utilities LIKE \"%$value%\"";
                  $result = mysql_query($query);
                  $numRows = mysql_num_rows($result);
                  while ($row = mysql_fetch_array($result)) {
                        $exUtil = explode("|", $row["utilities"]);
                        if(in_array($value, $exUtil)){
                              if(count($exUtil) < $arrCount){
                                    break;
                              }
                              $id = $row["listingID"];
                              $util = $row["utilities"];
                              $City = $row["iCity"];            
                              print "<a href=\"$id\">$util - $City</a><br />\n";
                        }else{
                              break;
                        }                              
                  }
                  if ($numRows == 0) { //No results.
                        header ("Location: http://www.errorpage.com");
                        mysql_close();
                        exit;
                  }
            }                  
      }
}

[Barand]

Don't store arrays of values, normalise the data and put them as separate rows in a "utility" table.

 

However you could try array_intersect()

 

$exUtil = explode("|", $row["utilities"]);
if (array_intersect($exUtil, $asdf) == $asdf) 
{
    // we have a match
}

[mrMarcus]

thanks for the response .. well, the reason i am storing the information the way i am is because the number of utilities is predetermined by what the user has selected when creating an account on the site.  for instance, user#1 might have only selected that his property has Heat and Water available, while User#2 might have Heat, Water, Cable and Hydro.  i just figured when i started out that a more efficient way of storing the data as per the individual user would be to have one field per listing number that is used for storing utilities (Heat|Water|Cable|Hydro).

 

the only problem i'm still having is that it's returning listings/results that contain at least one of the selected values .. it's not being strict to match and return only listings/results containing at least all selected values.

[mrMarcus]

too update my own question, i believe i have solved it .. i thought i'd post what i changed/did just for others reference.

 

if (isset($_POST['submit'])) { //make sure submit button is pressed; create array.
            if (is_array($_POST['u'])) {
                  $asdf = $_POST["u"];
                  $arrCount = count($asdf);
                  print_r($asdf);
                  echo "<br>";
                  $query = "SELECT * FROM table WHERE iCity=\"$city\" AND Visible=1 AND Status=1";
                  $result = mysql_query($query);
                  $numRows = mysql_num_rows($result);

## lost the foreach and while, replaced with a for .. also added a switch statement ##

                  for($row=0; $row = mysql_fetch_array($result); $row++){
                        $exUtil = explode("|", $row["utilities"]);
                        if($counted = array_intersect($exUtil, $asdf)){
                              if(count($counted) < $arrCount){
                                    $i = "stop";
                              }else{
                                    $i = "go";
                              }
                              switch($i){
                                    case stop:
                                          break;
                                    case go:
                                          if(count($exUtil) < $arrCount){
                                                break;
                                          }
                                          $id = $row["listingID"];
                                          $util = $row["utilities"];
                                          $City = $row["iCity"];            
                                          print "<a href=\"$id\">$util - $City</a><br />\n";
                                          
                                          break;
                              }
                        }
                  }
                  if ($numRows == 0) { //No results.
                        header ("Location: http://www.errorpage.com");
                        mysql_close();
                        exit;
                  }                  
            }
      }

[Barand]

modified the array intersect method slightly

 

<?php
$data = array (
    array(1,3,2,5),
    array(5,4),
    array(5,3,1,2,4),
    array(4,3,2,1)
    );

$srch = array(3,1,2,5) ;
sort($srch);

foreach ($data as $items)
{
    sort($items);
    if (array_intersect($srch, $items ) == $srch) 
    {
        echo '<pre>', print_r($items, true), '</pre>';
    }
}
?>