· 8 years ago · Aug 21, 2018, 05:06 AM
1ignore duplicate results from foreach loop iterating through mysql query
2require ('db.php');
3
4 $N = $_GET['Neigh'];
5 $V = $_GET['view'];
6 $C = $_GET['Con'];
7 $F = $_GET['front'];
8 $minPrice = $_GET['minprice'];
9 $maxPrice = $_GET['maxprice'];
10 $Year = $_GET['YearBuilt'];
11
12
13
14 foreach($N as $Nvalue){
15 if ($Nvalue != "nr" || $Nvalue != "" || $Nvalue !=NULL)
16 foreach($C as $Cvalue){
17 foreach($F as $Fvalue){
18 foreach($V as $Vvalue){
19
20 $query="SELECT *
21 FROM `foo`
22 WHERE `Building` LIKE '%{$Bvalue}%' && `Neigh` = '{$Nvalue}' && `View`
23 LIKE '%{$Vvalue}%' && `Con` LIKE '%{$Cvalue}%'
24 && `front` LIKE '%{$Fvalue}%' && `Listprice` BETWEEN '{$minprice}' AND '{$maxprice}'
25 && `Year_Built` >= '{$Year}' && `Status` LIKE '%Active%' GROUP BY `MLS`
26 ORDER BY `Neigh`, `price`, `tmk` ASC";
27
28 $result=mysql_query($query) or die('Query failed: ' . mysql_error() . "<br />n $query"); ;
29
30 $num=mysql_num_rows($result);
31
32<?PHP
33echo '<pre>';
34
35//mysql connect
36mysql_connect('localhost', 'root','');
37mysql_select_db("test");
38//add some tsting data
39addTestingData();
40
41//build sql from user input via $_GET
42$sqlConditions = builtSql();//build sql conditions
43$sql = 'select * from `building` where '.$sqlConditions;//build final sql
44
45//get data from mysql
46$result = mysql_query($sql ) ;
47while($row= mysql_fetch_row($result) )
48 print_r( $row );
49
50///////////////end////////////////////////////////////////////
51
52function addTestingData()
53{
54
55mysql_query("DROP TABLE IF EXISTS `Building`");
56 mysql_query("
57CREATE TABLE `Building` (
58 `building_uniqueid` MEDIUMINT UNSIGNED NOT NULL ,
59 `building_street` VARCHAR(30) NOT NULL,
60 `building_street_nr` VARCHAR(7) NOT NULL,
61 `building_neighborhood` VARCHAR(30) NOT NULL,
62 `building_view` VARCHAR(250) NOT NULL,
63 `building_condition` VARCHAR(150) NOT NULL,
64 `building_frontage` VARCHAR(30) NOT NULL,
65 `building_listprice` float NOT NULL,
66 `building_year` smallint not null,
67 `bsnss_comments` VARCHAR(255),
68 PRIMARY KEY (`building_uniqueid`)
69) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;
70");
71
72 mysql_query('
73insert into `building` (`building_uniqueid`,`building_street`,`building_street_nr`,
74`building_neighborhood`,`building_view`,`building_condition`,`building_frontage`,
75`building_listprice`,`building_year`,`bsnss_comments`) values
76("1","street1","strnr1","neighb1","Mountain Ocean Lake Park City-Center",
77"good","frontage1","500.3","1990","good building")
78');
79
80 mysql_query('
81insert into `building` (`building_uniqueid`,`building_street`,`building_street_nr`,
82`building_neighborhood`,`building_view`,`building_condition`,`building_frontage`,
83`building_listprice`,`building_year`,`bsnss_comments`) values
84("id2","street1","strnr1","neighb2","River Ocean Lake Park City-Center",
85"very good","frontage1","800.5","1991","good building")
86') or die(mysql_error());
87
88 mysql_query('
89insert into `building` (`building_uniqueid`,`building_street`,`building_street_nr`,
90`building_neighborhood`,`building_view`,`building_condition`,`building_frontage`,
91`building_listprice`,`building_year`,`bsnss_comments`) values
92("3","street3","strnr3","neighb1","Ocean Park City-Center",
93"fantastic","frontage77","600.7","1994","good building")
94');
95
96 mysql_query('
97insert into `building` (`building_uniqueid`,`building_street`,`building_street_nr`,
98`building_neighborhood`,`building_view`,`building_condition`,`building_frontage`,
99`building_listprice`,`building_year`,`bsnss_comments`) values
100("4","street4","strnr4","neighb1","Ocean Park Mountain City-Center",
101"good","frontage1","500.23","1994","good")
102');
103
104 $_GET['Neighborhood']=array('neighb1');
105 $_GET['View']=Array('Mountain','River', 'City Center');
106 $_GET['Condition']=array('good','very good');
107 $_GET['Frontage']=array('frontage77','frontage1');
108 $_GET['minPrice']='500';
109 $_GET['maxPrice']='600';
110 $_GET['minYear']='1990';
111 $_GET['maxYear']='1995';
112
113
114
115}
116
117
118
119function builtSql()
120{
121
122 $sqlBuild = '( ';
123
124//formate sql for Neighborhood
125foreach($_GET['Neighborhood'] as $value)
126 $sqlBuild .=' `building_neighborhood` = ''.$value.'' or ';
127 $sqlBuild=removeLastOr($sqlBuild);
128 $sqlBuild.=') and (';
129
130//formate sql for View
131foreach($_GET['View'] as $value)
132 $sqlBuild .=' `building_view` LIKE '%'.str_replace(" ", "-",$value).'%' or ';
133 $sqlBuild=removeLastOr($sqlBuild);
134 $sqlBuild.=') and (';
135
136 //formate sql for Condition
137foreach($_GET['Condition'] as $value)
138 $sqlBuild .=' `building_condition` = ''.$value.'' or ';
139 $sqlBuild=removeLastOr($sqlBuild);
140 $sqlBuild.=') and (';
141
142 //formate sql for Frontage
143foreach($_GET['Frontage'] as $value)
144 $sqlBuild .=' `building_frontage` = ''.$value.'' or ';
145 $sqlBuild=removeLastOr($sqlBuild);
146 $sqlBuild.=') and (';
147
148 //formate sql for Price
149$sqlBuild.=
150' `building_listprice` BETWEEN ''.$_GET['minPrice'].'' and ''.$_GET['maxPrice'].'' ';
151 $sqlBuild=removeLastOr($sqlBuild);
152 $sqlBuild.=') and (';
153
154//formate sql for Year
155$sqlBuild.=
156' `building_year` BETWEEN ''.$_GET['minYear'].'' and ''.$_GET['maxYear'].'' ';
157 $sqlBuild.=') ';
158
159return $sqlBuild;
160}
161
162function removeLastOr($str)
163{
164 $tmp=substr($str ,0,(strlen($str )-2));
165 return $tmp=substr($str ,0,(strlen($str )-3));
166}
167
168?>
169
170select some_fields from bulding
171left join on views
172where view = view1 or view2 etc