在mysql查询中使用php form multiple select

Question

我正在尝试使用搜索表单的结果进行查询。我的其中一个字段是"state“。我希望用户能够选择多个状态，然后获得反映所有选定状态的结果列表。这是我用于状态的单个select表单元素的代码。它反映了如果没有选择州以及性别、销售和salename，则选择所有记录：

$query  = "SELECT *, DATE_FORMAT(saledate,'%m/%d/%Y') as saledatex FROM sdusa WHERE";


if (!empty($state)){ 
$query .= " state = '$state' AND";
}

if (!empty($sex)){ 
$query .= " sex = '$sex' AND";
}

if (!empty($sale)){ 
$query .= " breeder = '$sale' AND";
}

if (!empty($salename)){ 
$query .= " salename LIKE '%$salename%' AND";
}

$query .= " saledate >= $salestart
AND saledate <= $saleend
AND birthdate >= $birthstart
AND birthdate <= $birthend
AND actbw <= $actbw1 
AND adj205ww >= $adj205ww1
AND adj365yw >= $adj365yw1
AND rea >= $rea1
AND imf >= $imf1
AND sc >= $sc1

$result = mysql_query($query) or die('Error, query failed');

如何调整查询以从状态的多选列表中获取结果，该列表将为我提供反映所有选定状态的结果列表？我想我可能需要添加一个"foreach“语句或者一个计数器--但是因为我是php的新手，所以我对代码不是很确定。

当前输出如下所示：

 <table align='center' width='780' border='1' cellpadding='0' cellspacing='0'     bordercolor='#000000'>
 <tr>
<td width='55' class='s11'><div align='center'>Reg #</div></td>
<td width='100' class='s11'><div align='center'>Name</div></td>  
 <td width='135' class='s11'><div align='center'>Sire</div></td>
 <td width='125' class='s11'><div align='center'>MGS</div></td>
 <td width='180' class='s11'><div align='center'>Breeder</div></td>
 <td width='25' class='s11'><div align='center'>St</div></td>
 <td width='60' class='s11'><div align='center'>Sale Date</div></td>  
 <td width='25' class='s11'><div align='center'>BW</div></td>
 <td width='25' class='s11'><div align='center'>WW</div></td>
<td width='25' class='s11'><div align='center'>YW</div></td>
 <td width='25' class='s11'><div align='center'>M</div></td>
 </tr>
 <?php 
 while($row = mysql_fetch_array($result))
{
echo "<tr height='20'>";
echo "<td class='s11'><div align='center'><a href='sdindselect.php?registration_num=". $row ['registration_num'] ."'>" . $row['registration_num'] . "</a> </div></td>";
echo "<td class='s11'><div align='center'>" . $row['name'] . " </div></td>";
echo "<td class='s11'><div align='center'>" . $row['sire'] . " </div></td>";
echo "<td class='s11'><div align='center'>" . $row['sire3'] . " </div></td>";
echo "<td class='s11'><div align='center'>" . $row['breeder'] . $row['salname'] ." </div></td>";
echo "<td class='s11'><div align='center'>" . $row['salestate'] . " </div></td>";
echo "<td class='s11'><div align='center'>" . $row['saledatex'] . " </div></td>";
echo "<td class='s11'><div align='center'>" . $row['bw'] . " </div></td>";
echo "<td class='s11'><div align='center'>" . $row['ww'] . " </div></td>";
echo "<td class='s11'><div align='center'>" . $row['yw'] . " </div></td>";
echo "<td class='s11'><div align='center'>" . $row['milk'] . " </div></td>";
echo "</tr>";
}
?>

谢谢你的帮助！

Answer 1

更新！多选列表将其值作为数组传递。你需要做的就是这样：

if (!empty($state))
{
  $query .= "("; // Encapsulate state values so they'll be resolve independently

  foreach($states as $term)
  {
    if($term !== '')
    {
      $query .= " state = '".trim($term)."' OR";
    }
  }
  $query .= ") AND";
}

Answer 2

这应该会为你做好准备：

if (!empty($state)){

$states = array();

取决于查询字符串中每个$state值的分隔方式

示例: $state = "New Jersey,Wyoming,Oregon,Georgia,Maine,Arkansas"

$states  = explode(",",$state); 
$first = true;

foreach($states as $term){

    if($term !== ''){

        if(!$first) $query .= " OR";

        $query .= " state = '".trim($term)."'";
        $first = false;

    }
}

$query .= " AND";

}

希望能有所帮助。