这是查询,它返回对我的游轮和票价表的搜索:
SELECT *, MIN(fares.offered) FROM cruises,fares
WHERE cruises.departs > CURDATE() AND (cruises.destination LIKE %s OR
cruises.second_destination LIKE %s) AND
EXTRACT(YEAR_MONTH from cruises.departs) LIKE %s
AND cruises.fromport LIKE %s
AND cruises.ship LIKE %s AND cruises.live = 'Y'
AND fares.cruise_id = cruises.id
GROUP BY fares.cruise_id ORDER BY cruises.departs, cruises.fromport"
我有一些跳转菜单,以便用户可以缩小搜索范围,例如上面的查询返回2012年7月的所有游轮,有一些,一些从伦敦出发,一些从利物浦出发。
我的港口选择菜单上填充了所有航班,如下所示
London
Liverpool
London
London
Liverpool
7月每次返回的航次记录一次。
我只想要
London
Liverpool
这是选择代码:
<select name="jumpMenu3" id="jumpMenu3" onchange="MM_jumpMenu('parent',this,0)">
<option value="">Select a port</option>
<?php
$port = '';
mysql_data_seek($cruises, 0);
while ($row_cruises = mysql_fetch_assoc($cruises)) {
if ($row_cruises['fromport'] != $port) {
$port = $row_cruises['fromport'];
?>
<option value="index.php?subj=2&destination=<?php
echo urlencode($row_cruises['destination']);
?>&departs=<?php
echo date('Ym',strtotime($row_cruises['departs']));
?>&port=<?php
echo urlencode($port);?>"<?php
if ($_GET['port'] == $row_cruises['fromport']) {
echo "selected="selected"";
}
?>><?php echo $port; ?></option>
<?php } ;
}
if(mysql_num_rows($cruises) > 0) {
mysql_data_seek($cruises, 0);
$row_cruises = mysql_fetch_assoc($cruises);
}
?>
</select>
我想到了 GROUP BY,但我不能在港口的搜索查询中使用它,因为显然每个港口都有不止一个航班,也许我需要对月份选择选项进行单独的查询 - 或者我可以使用 php 分组吗?
最好的方法是另一个查询来检索端口名称,如下所示:
SELECT DISTINCT fromport FROM cruises WHERE cruises.departs > CURDATE()
您可以添加应适用的其他条件。
第二种方法可以在 PHP 中完成:
$ports = array();
mysql_data_seek($cruises, 0);
while ($row_cruises = mysql_fetch_assoc($cruises)) {
if (!in_array($row_cruises['fromport'], $ports)) {
$ports[] = $row_cruises['fromport'];
?>
<option value="index.php?subj=2&destination=<?php echo urlencode($row_cruises['destination']);?>&departs=<?php echo date('Ym',strtotime($row_cruises['departs']));?>&port=<?php echo urlencode($row_cruises['fromport']);?>"<?php if ($_GET['port'] == $row_cruises['fromport']) {echo "selected="selected"";}?>><?php echo $row_cruises['fromport']; ?></option>
<?php
}
}
if(mysql_num_rows($cruises) > 0) {
mysql_data_seek($cruises, 0);
$row_cruises = mysql_fetch_assoc($cruises);
}