忽略foreach循环迭代通过MySQL查询重复结果 [英] ignore duplicate results from foreach loop iterating through mysql query
问题描述
我成立了一个多选的形式返回数组，然后循环他们通过MySQL查询返回的结果。
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 ...
推荐答案
如果你想保持目前的设计与存储在单个单元格观点，那么有什么可以做。虽然我不建议它给我下面的，因为你已经设计项目中这样的例子。
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.
注意：这是一个测试的例子，看到的基本功能。这不是最终的code原因SQL注入和其他东西都没有考虑服用。
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));
}
?>
虽然你看到一些foreach循环，没有必要担心的原因，他们运行包含用户数据的小一阳，因此认为它们乳宁超级快的原因没有涉及MySQL查询！
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!!
如果有任何问题仍然考虑放弃对DB模式和一些基本的说明的详细信息。希望这可以帮助！
If any problem remains consider giving details on db schema and some basic description. Hope this can help!
这篇关于忽略foreach循环迭代通过MySQL查询重复结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
