PHP/MySQL如何设置一个更简单的SELECT查询？

Question

我有一个搜索功能，可以让你在不同类型的框中输入姓氏、名字和中间名。我对代码没有任何问题，但是有人知道如何优化它吗？

这段代码有多个if语句，它们找出哪些文本框不为空，然后在WHERE中使用，如下所示：

$where1 = $_POST['firstname'];
            $where2 = $_POST['midname'];
            $where3 = $_POST['lastname'];

            if(!empty($where1) && empty($where2) && empty($where3)){
                $result = $connection->query("SELECT * FROM senior WHERE firstname = '$where1'");
            } else if(!empty($where1) && !empty($where2) && empty($where3)){
                $result = $connection->query("SELECT * FROM senior WHERE firstname = '$where1' AND midname = '$where2'");
            } else if(!empty($where1) && !empty($where2) && !empty($where3)){
                $result = $connection->query("SELECT * FROM senior WHERE firstname = '$where1' AND midname = '$where2' AND lastname = '$where3' ");
            } else if(!empty($where1) && empty($where2) && !empty($where3)){
                $result = $connection->query("SELECT * FROM senior WHERE firstname = '$where1' AND lastname = '$where3' ");
            } else if(empty($where1) && !empty($where2) && !empty($where3)){
                $result = $connection->query("SELECT * FROM senior WHERE lastname = '$where3' AND midname = '$where2' ");
            } else if(empty($where1) && !empty($where2) && empty($where3)){
                $result = $connection->query("SELECT * FROM senior WHERE midname = '$where2'");
            } else if(empty($where1) && empty($where2) && !empty($where3)){
                $result = $connection->query("SELECT * FROM senior WHERE lastname = '$where3'");
            }

Answer 1

我给你的建议

$where = '';
if($where1) $where .= ($where ? " AND " : " ")."firstname = '$where1'";
if($where2) $where .= ($where ? " AND " : " ")."midname = '$where2'";
if($where3) $where .= ($where ? " AND " : " ")."lastname = '$where3'";
$query = "SELECT * FROM senior".($where ? " WHERE ".$where : "");
$result = $connection->query($query);

Answer 2

首先，我鼓励您使用Prepared Statements来提高安全性。

与您上面编写的代码相关，您可以尝试如下所示：

$query = "SELECT * FROM senior";
$firstcondition = true;

if (!empty($where1))
  addConditon($query, "firstname = ".$where1);
if (!empty($where2))
  addConditon($query, "midname = ".$where2);    
if (!empty($where3))
  addConditon($query, "lastname = ".$where3);

$result = $connection->query($query);

function addCondition($query, $condition) {
  if (!$firstcondition) 
    $query.= " AND ";
  else {
    $firstcondition = false;
    $query.= " WHERE ";
  }
  $query.= $condition;
}

Answer 3

只是一个建议:为什么不使用LIKE，因为它是一个搜索查询？

$where1 = $_POST['firstname'];
$where2 = $_POST['midname'];
$where3 = $_POST['lastname'];

$result = $connection->query("SELECT * FROM senior WHERE firstname LIKE '%".$where1."%' AND midname LIKE '%".$where2."%' AND lastname LIKE '%".$where3."%' ");