ignore duplicate results from foreach loop iterating through mysql query

Question

i set up a multi select form to return arrays and then looped them through mysql query to return results

i dont want duplicate results if the user selects multiple options and those options are in one record

for example user selects three different 'Views' and one property has all three views i dont want that displayed in the results three times ... thank you if you can help

    require ('db.php');

    $N = $_GET['Neigh'];
   $V = $_GET['view'];
   $C = $_GET['Con'];
  $F = $_GET['front'];
 $minPrice = $_GET['minprice'];
 $maxPrice = $_GET['maxprice'];
 $Year = $_GET['YearBuilt'];



   foreach($N as $Nvalue){
   if ($Nvalue != "\n\r" || $Nvalue != "" || $Nvalue !=NULL)
   foreach($C as $Cvalue){
   foreach($F as $Fvalue){
   foreach($V as $Vvalue){

   $query="SELECT *
   FROM `foo`
   WHERE `Building` LIKE '%{$Bvalue}%' && `Neigh` = '{$Nvalue}' && `View` 
   LIKE  '%{$Vvalue}%' && `Con` LIKE '%{$Cvalue}%'
   && `front` LIKE '%{$Fvalue}%' && `Listprice` BETWEEN '{$minprice}' AND '{$maxprice}' 
   && `Year_Built` >= '{$Year}' && `Status` LIKE '%Active%' GROUP BY `MLS` 
  ORDER BY `Neigh`, `price`, `tmk` ASC";

  $result=mysql_query($query) or die('Query failed: ' . mysql_error() . "<br />\n $query"); ;

  $num=mysql_num_rows($result);

sorry if this is a mess .. im self taught from the internet .. it does work but returns duplicates for multiple variables in the same record ...

Answer 1

SELECT * FROM foo WHERE Building LIKE ... && Neighborhood = .... && View LIKE ... && Condition LIKE ... && Frontage LIKE ... Listprice BETWEEN ... && Year_Built >= ... && Status LIKE ...

If I am not wrong this query indicates that you have a single table with all the above attributes.

At least View smells of a multiple value attribute. If it is found in the same table then it is a very classic example of redundant data. So if you have three views for a house you will end up storing 3 records for the very same building.

Some questions arise here:

1. How trusty can you identifier be, how correct.
2. If you have 2000 houses and half of them have 2 views you will end up with 1000 more records in the same table.
3. How easy is it to delete or update
4. How consistent are the queries (I think this is where your question falls) etc

If this is true then 99% you are not going to change anything while on a working environment, however it is good to know what are the flows in your system so you can take measures.

In the ideal case that you were in the building phase, still under development then for every attribute of multiple values you would need to transfer the field to a new table on its own with a foreign key to the building table.

Then if a user would select multiple views you would query like this:

select some_fields 
from building 
left join on views 
where view = view1 or view2 etc

This query cannot bring multiple records because there is no such thing, every building is defined only once, with a clear identifier (primary key) as a result of normalization, in the building table! On the other hand the multiple records in the view will help catch a record (if any or all views match).

And a last thing seeing 4 loops together usually it is a strong warning that the code needs serious optimization!

I also agree that distinct is your option for now!

Answer 2

If you want to keep the current design with view stored in a single cell, then there is something you can do. Although I do not suggest it I give an example below because you already have designed your project like this.

NOTICE: It is a testing example, to see the basic functionality. This is not final code cause sql injection and other things are not taken in consideration.

I do the assumption that views are stored in a single cell, space delimited, and if a view consist of more that one word - are place in between, for example City-Center.

Study this example and see if you can adjust it to your needs:

<?PHP
echo '<pre>';

//mysql connect
mysql_connect('localhost', 'root',''); 
mysql_select_db("test"); 
//add some tsting data
addTestingData();

//build sql from user input via $_GET   
$sqlConditions = builtSql();//build sql conditions
$sql = 'select * from `building` where '.$sqlConditions;//build final sql

//get data from mysql
$result  = mysql_query($sql )  ; 
while($row=   mysql_fetch_row($result) ) 
    print_r( $row );

///////////////end//////////////////////////////////////////// 

function addTestingData()
{

mysql_query("DROP TABLE IF EXISTS `Building`");
     mysql_query("
CREATE TABLE `Building` (
  `building_uniqueid` MEDIUMINT UNSIGNED NOT NULL  , 
  `building_street` VARCHAR(30) NOT NULL,
  `building_street_nr` VARCHAR(7) NOT NULL,  
  `building_neighborhood` VARCHAR(30) NOT NULL,  
  `building_view` VARCHAR(250) NOT NULL, 
  `building_condition` VARCHAR(150) NOT NULL,  
  `building_frontage` VARCHAR(30) NOT NULL,
  `building_listprice` float NOT NULL, 
  `building_year` smallint not null,  
  `bsnss_comments` VARCHAR(255), 
  PRIMARY KEY  (`building_uniqueid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;
"); 

     mysql_query('
insert into `building` (`building_uniqueid`,`building_street`,`building_street_nr`,
`building_neighborhood`,`building_view`,`building_condition`,`building_frontage`,
`building_listprice`,`building_year`,`bsnss_comments`) values 
("1","street1","strnr1","neighb1","Mountain Ocean Lake Park City-Center",
"good","frontage1","500.3","1990","good building")
'); 

     mysql_query('
insert into `building` (`building_uniqueid`,`building_street`,`building_street_nr`,
`building_neighborhood`,`building_view`,`building_condition`,`building_frontage`,
`building_listprice`,`building_year`,`bsnss_comments`) values 
("id2","street1","strnr1","neighb2","River Ocean Lake Park City-Center",
"very good","frontage1","800.5","1991","good building")
') or die(mysql_error()); 

     mysql_query('
insert into `building` (`building_uniqueid`,`building_street`,`building_street_nr`,
`building_neighborhood`,`building_view`,`building_condition`,`building_frontage`,
`building_listprice`,`building_year`,`bsnss_comments`) values 
("3","street3","strnr3","neighb1","Ocean Park City-Center",
"fantastic","frontage77","600.7","1994","good building")
'); 

     mysql_query('
insert into `building` (`building_uniqueid`,`building_street`,`building_street_nr`,
`building_neighborhood`,`building_view`,`building_condition`,`building_frontage`,
`building_listprice`,`building_year`,`bsnss_comments`) values 
("4","street4","strnr4","neighb1","Ocean Park Mountain City-Center",
"good","frontage1","500.23","1994","good")
'); 

  $_GET['Neighborhood']=array('neighb1');
  $_GET['View']=Array('Mountain','River', 'City Center');
  $_GET['Condition']=array('good','very good');
  $_GET['Frontage']=array('frontage77','frontage1');
  $_GET['minPrice']='500';
  $_GET['maxPrice']='600';
  $_GET['minYear']='1990';
  $_GET['maxYear']='1995';



}



function builtSql()
{

  $sqlBuild = '( ';

//formate sql for Neighborhood
foreach($_GET['Neighborhood'] as $value)
    $sqlBuild .=' `building_neighborhood` = \''.$value.'\' or ';   
    $sqlBuild=removeLastOr($sqlBuild);
    $sqlBuild.=') and (';

//formate sql for View
foreach($_GET['View'] as $value) 
    $sqlBuild .=' `building_view` LIKE \'%'.str_replace(" ", "-",$value).'%\' or ';   
    $sqlBuild=removeLastOr($sqlBuild);
    $sqlBuild.=') and (';

 //formate sql for Condition
foreach($_GET['Condition'] as $value) 
    $sqlBuild .=' `building_condition` = \''.$value.'\' or ';   
    $sqlBuild=removeLastOr($sqlBuild);
    $sqlBuild.=') and (';

 //formate sql for Frontage
foreach($_GET['Frontage'] as $value) 
    $sqlBuild .=' `building_frontage` = \''.$value.'\' or ';   
    $sqlBuild=removeLastOr($sqlBuild);
    $sqlBuild.=') and (';

 //formate sql for Price
$sqlBuild.=
' `building_listprice` BETWEEN \''.$_GET['minPrice'].'\' and \''.$_GET['maxPrice'].'\'   ';
    $sqlBuild=removeLastOr($sqlBuild);
    $sqlBuild.=') and (';

//formate sql for Year
$sqlBuild.=
' `building_year` BETWEEN \''.$_GET['minYear'].'\' and \''.$_GET['maxYear'].'\' '; 
    $sqlBuild.=')  ';

return $sqlBuild;
}

function removeLastOr($str)
{
    $tmp=substr($str ,0,(strlen($str )-2));
    return $tmp=substr($str ,0,(strlen($str )-3)); 
}

?>

Although you see a some foreach loops, there is no need to worry cause they run for small arays that contain user data, so consider them runing super fast cause there is no mysql query involved!!

If any problem remains consider giving details on db schema and some basic description. Hope this can help!