MySQL 存储过程跳过空参数

Question

我想我阅读了这里的所有文章，但仍然无法让我的代码 100% 工作。我是一个PHP表单，用户可以根据4个位置中的任何一个进行搜索，也可以完全跳过它，在这种情况下应该跳过"where location"子句并移动到下一个参数组。我的 mysql 存储过程如下所示：

CREATE PROCEDURE search( 
  IN inRoomsPerPage INT,
  IN inStartItem INT,
  IN inAddDate INT,
  IN inLocation1 ENUM('T','F'),
  IN inLocation2 ENUM('T','F'),
  IN inLocation3 ENUM('T','F'),
  IN inLocation4 ENUM('T','F')
)
BEGIN
SELECT * 
 FROM useradds as ua
 INNER JOIN household as hh on hh.idx_user_id = ua.idx_user_id
 INNER JOIN house as h on h.idx_user_id = ua.idx_user_id
 WHERE adddate >= (CURDATE() - INTERVAL inAddDate DAY) AND
 ((inLocation1 IS NULL OR downtown = inLocation1) OR 
 (inLocation2 IS NULL OR hwy = inLocation2) OR
 (inLocation3 IS NULL OR dewey = inLocation3) OR
 (inLocation4 IS NULL OR lewes = inLocation4))
 ORDER BY ua.adddate DESC
 LIMIT inStartItem, inRoomsPerPage;
 END$$

每当我选择任何一个或多个位置时，这都可以正常工作，但如果我尝试跳过它，则不起作用（它应该只选择最后一个 inAddDate 中的所有内容）。如果我更换

(inLocation1 IS NULL OR downtown = inLocation1)

跟

downtown = COALESCE(NULLIF(inLocation1, ''), downtown))

然后，如果我绕过该位置，它会选择所有内容，但如果我想搜索任何 2 个或更多位置，它不会返回任何内容。我相信一定有很好的方法来克服这个问题，因为我有更多的参数组要添加。

Answer 1

我的 php/html 部分看起来像这样：

<form action='' method='post'  >
    <select name='ckadddate'>
        <option value='1'>last 24 hours</option>
        <option value='3' selected>last 3 days</option>
        <option value='7'>last week</option></select>
in:<input type='checkbox' name='Downtown' >Downtown
     <input type='checkbox' name='Hwy' >HWY 1
      <input type='checkbox' name='Dewey'>Dewey
      <input type='checkbox' name='Lewes'>Lewes
<input name='SubmitSearch' type='submit' value='Search'>

调用存储过程的 php 函数部分是：

if(isset($_POST['SubmitSearch'])){
    $cklocation1 = $cklocation2= $cklocation3= $cklocation4= '';
    if(isset($_POST['Downtown']))$cklocation1 = 'T';
       if(isset($_POST['Hwy']))$cklocation2 = 'T';
       if(isset($_POST['Dewey']))$cklocation3 = 'T';
       if(isset($_POST['Lewes']))$cklocation4 = 'T';
    **if(!isset($_POST['Downtown']) && !isset($_POST['Hwy']) && !isset($_POST['Dewey']) && !isset($_POST['Lewes'])){
        $cklocation1 = $cklocation2= $cklocation3= $cklocation4= 'T';
    }**
$sql = 'CALL search_room(:RoomsPerPage, :StartItem,:Adddate,
:Location1,:Location2,:Location3,:Location4)';
$params = array (':RoomsPerPage' => ADDS_PER_PAGE, ':StartItem' => $start_item, 
':Adddate' => $_POST['ckadddate'],':Location1' => $cklocation1, 
':Location2' => $cklocation2, ':Location3' => $cklocation3, ':Location4' => $cklocation4
    );

这个接缝工作得很好，但我很确定它应该是一个更优雅的解决方案。