我设置了一个多选表单来返回数组,然后通过mysql查询循环返回结果
如果用户选择了多个选项,并且选项在一个记录中
例如,用户选择了三个不同的"视图",并且一个属性具有所有三个视图,我不希望它在结果中显示三次。。。如果你能帮助,谢谢
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);
对不起,如果这一团糟。。我在网上自学。。它确实有效,但返回同一记录中多个变量的重复项。。。
如果你想将当前的设计和视图存储在一个单元格中,那么你可以做一些事情。虽然我不建议这样做,但我在下面举一个例子,因为你已经这样设计了你的项目。
注意:这是一个测试示例,用于查看基本功能。这不是最终的代码,因为没有考虑sql注入和其他因素。
我假设视图存储在一个单元格中,以空格分隔,如果一个视图由多个单词组成,则放在两者之间,例如City Center。
研究这个例子,看看你是否可以根据自己的需求进行调整:
<?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循环,但无需担心,因为它们是为包含用户数据的小型aray运行的,所以请考虑它们运行速度极快,因为不涉及mysql查询!!
如果仍然存在任何问题,请考虑提供数据库模式的详细信息和一些基本描述。希望这能有所帮助!
从
foo
中选择*,其中Building
喜欢&;Neighborhood
=。。。。&;View
类似&;Condition
类似&;Frontage
类似。。。Listprice
介于…之间&;Year_Built
>=&;Status
喜欢。。。
如果我没有错的话,这个查询表明您有一个具有以上所有属性的表。
至少View有多重值属性的味道。如果它在同一个表中找到,那么它就是冗余数据的一个非常经典的例子。因此,如果你有一栋房子的三个视图,你最终会为同一栋建筑存储3条记录。
这里出现了一些问题:
- 您的标识符有多可靠,有多正确
- 如果你有2000栋房子,其中一半有两个景观,你最终会得到同一表中还有1000条记录
- 删除或更新有多容易
- 查询的一致性如何(我认为这就是您的问题所在(等等
如果这是真的,那么99%的人在工作环境中不会改变任何事情,但最好知道你的系统中有什么流量,这样你就可以采取措施。
在理想的情况下,您处于构建阶段,仍在开发中,那么对于多个值的每个属性,您都需要将字段单独转移到一个新表中,并使用构建表的外键。
然后,如果用户要选择多个视图,你会这样查询:
select some_fields
from building
left join on views
where view = view1 or view2 etc
这个查询不能带来多条记录,因为没有这样的东西,每栋建筑只定义一次,带有一个明确的标识符(主键(规范化的结果,在构建表中!另一方面,视图中的多个记录将有助于捕获一个记录(如果有或所有视图匹配(。
最后一件事是看到4个循环在一起,通常这是一个强烈的警告,代码需要认真优化!
我也同意distinct是您目前的选择