我在网站的"高级搜索"功能上遇到了麻烦。从本质上讲，这是一个高级的音乐家搜索功能(城市、名字成员、zip按文本框和流派按复选框)。下面是"高级搜索"php页面的代码…
<form method="post" action="advsearchresults.php">
<div class="control-group">
<div class="controls span4">
<label>City</label>
<input type="text" name="advCity" placeholder="Ex: San Marcos..." />
<label>Zip Code</label>
<input type="text" name="advZip" placeholder="Ex: 78666..." />
<label>Band Name</label>
<input type="text" name="advBand" placeholder="Ex: Catchy Band Name..." />
<label>Band Member Name</label>
<input type="text" name="advMember" placeholder="Ex: Steve Stevenson..." />
</div>
<div class="controls span2"><br/><br/><br/>
<label class="checkbox">
<input type="checkbox" name="check_list[]" value="Blues"> Blues
</label>
<label class="checkbox">
<input type="checkbox" name="check_list[]" value="Classical"> Classical
</label>
<label class="checkbox">
<input type="checkbox" name="check_list[]" value="Comedy"> Comedy
</label>
<label class="checkbox">
<input type="checkbox" name="check_list[]" value="Country"> Country
</label>
...
...
</div>
<div class="span12">
<center>
<input type="submit" class="btn btn-info" value=" Advanced Search ">
</div>
...
这里是"高级搜索结果"页面…
...
<?php
$advCity = $_POST['advCity'];
$advZip = $_POST['advZip'];
$advBand = $_POST['advBand'];
$advMember = $_POST['advMember'];
$check = $_POST['check_list'];
?>
...
<?php
if(isset($_POST['check_list']) && is_array($_POST['check_list'])){
foreach($_POST['check_list'] as $check) {
$genreQuery = "SELECT profile.PROFILE_GENRE, profile.PROFILE_BANDNAME
FROM profile INNER JOIN band ON profile.PROFILE_PROFILEID = band.BAND_PROFILEID
INNER JOIN bandmember ON band.BAND_BANDID = bandmember.BANDMEMBER_BANDID
WHERE band.BAND_CITY = '$advCity' OR profile.PROFILE_GENRE = '$check'
OR band.BAND_ZIP = '$advZip' OR profile.PROFILE_BANDNAME = '$advBand'
OR bandmember.BANDMEMBER_NAME = '$advMember'";
$result = mysql_query($genreQuery) OR die(mysql_error());
$num=mysql_numrows($result);
mysql_close();
if ($num > 0){
?>
<table class="table table-hover span8" method="post" action="bandpage.php">
<thead>
<tr>
<th>Genre</th>
<th>Band Name</th>
</tr>
</thead>
<?php
$i = 0;
while ($i < $num) {
$GENRE = mysql_result($result,$i,"profile.PROFILE_GENRE");
$BANDNAME = mysql_result($result,$i,"profile.PROFILE_BANDNAME");
?>
<tbody>
<tr>
<td><?php echo $GENRE; ?></td>
<td><a href="bandpage.php" name="band"><?php echo $BANDNAME; ?>
</a></td></tr</tbody>
<?php
$i++;
}
}
else {
?>
<table class="table span8">
<thead>
<tr>
<th>No musicians available for the city: <?php echo $advCity ?></th>
</tr>
</thead>
</table>
<?php
}
}
}
?>
根本不显示搜索结果，而是显示随机的表数据。任何帮助都非常感激!
格式化SQL:
SELECT
profile.PROFILE_GENRE,
profile.PROFILE_BANDNAME
FROM
profile
INNER JOIN band ON profile.PROFILE_PROFILEID = band.BAND_PROFILEID
INNER JOIN bandmember ON band.BAND_BANDID = bandmember.BANDMEMBER_BANDID
WHERE
band.BAND_CITY = '$advCity' OR
profile.PROFILE_GENRE = '$check' OR
band.BAND_ZIP = '$advZip' OR
profile.PROFILE_BANDNAME = '$advBand' OR
bandmember.BANDMEMBER_NAME = '$advMember'
您的SQL正在执行所有谓词，而不管它们是否被设置，因此，如果用户没有显式地提供$advBand的值，那么它将具有""的值(空字符串)，这仍然将包含在您的查询中。因为OR是可交换的，所以任何PROFILE_BANDNAME值为空的行都将返回。
有两种可能的解决方案:"动态SQL"，手动添加谓词，或者为每个谓词添加一个保护表达式。动态SQL可能容易出错，因此向WHERE添加表达式可能更容易，它应该像这样:
(这个例子使用了MySQL特定的field > ''语法，这是一种快速执行"非null或空"值比较的方法，它也使用了MySQL参数。
WHERE
(:advCity > '' AND band.BAND_CITY = :advCity ) OR
(:check > '' AND profile.PROFILE_GENRE = :check ) OR
(:advZip > '' AND band.BAND_ZIP = :advZip ) OR
(:advBand > '' AND profile.PROFILE_BANDNAME = :advBand ) OR
(:advMember > '' AND bandmember.BANDMEMBER_NAME = :advMember )
修改PHP的其余部分以使用MySQL参数对读者来说是一个练习:)