php - 使用函数和 if else 的多个搜索表单

Question

您好，我使用函数使用 PHP、HTML、SQL 创建了一个多搜索表单，例如，我有 3 个搜索字段名字、姓氏和电子邮件。我会让用户从其中任何一个输入，因此我需要 if else 语句，但为了能够满足所有条件，它需要很多 if else，所以我考虑使用一个函数来输出表和在数据库查询之后将它放在 if else 中。但是，如果我这样做，它似乎无法在数据库中搜索，它会输出“0 个结果”，但如果我删除该函数并将其放在脚本的末尾，我就可以在数据库中搜索但它无法检测到我的其他条件，即“您尚未输入任何值”

    function checkres()
    {
    //Get query on the database
        $result = mysqli_query($conn, $sql);

        //Check results
        if (mysqli_num_rows($result) > 0)
        {
        //Headers
            echo "<table border='1' style='width:100%'>";
        echo "<tr>";
            echo "<th>Image ID</th>"; 
        echo "<th>Lastname</th>";
        echo "<th>Firstname</th>";
        echo "<th>Email</th>";
        echo "<th>PhoneNumber</th>";
        echo "</tr>";

      //output data of each row
          while($row = mysqli_fetch_assoc($result))
            {
            echo "<tr>";
                      echo "<td>".$row['ID']."</td>";
                      echo "<td>".$row['LastName']."</td>";
                      echo "<td>".$row['FirstName']."</td>";
                      echo "<td>".$row['Email']."</td>";
                      echo "<td>".$row['PhoneNumber']."</td>";
                    echo "</tr>";

            }
                  echo "</table>";
        } else {
            echo "0 results";
        }
    }

   if (!empty($sfname) && empty($slname) && empty($semail) )
{
     $sql = "select * from Userlist where FirstName LIKE '%". $sfname  ."%'" ;

checkres();

}

 else  if (!empty($sfname) && !empty($slname) && empty($semail))

{
  $sql = "select * from Userlist where FirstName LIKE '%". $sfname  ."%' AND LastName LIKE '%". %slname. "%'";

checkres();

}

else
{
   echo "You have not yet entered any values ";
}

mysqli_close($conn);

?>

这是新的

<form  method="post" action="#"  id="searchform">
      First Name:<br>
          <input  type="text" name="fname">
      <br>Last Name:<br>
          <input type="text" name="lname">
      <br>Email: <br>
          <input type="text" name="email">
      <br>
      <input  type="submit" name="submit" value="Search">
    </form>
<?php
$sfname = $_POST["fname"];
$slname = $_POST["lname"];
$semail = $_POST["email"];
$servername = "xxx";
$username = "xxx";
$password = "xxx";
$dbname = "xxx";

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);

// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}


function checkres()
    {
    //Get query on the database
        $result = mysqli_query($conn, $sql);

        //Check results
        if (mysqli_num_rows($result) > 0)
        {
        //Headers
            echo "<table border='1' style='width:100%'>";
        echo "<tr>";
            echo "<th>Image ID</th>"; 
        echo "<th>Lastname</th>";
        echo "<th>Firstname</th>";
        echo "<th>Email</th>";
        echo "<th>PhoneNumber</th>";
        echo "</tr>";

      //output data of each row
          while($row = mysqli_fetch_assoc($result))
            {
            echo "<tr>";
                      echo "<td>".$row['ID']."</td>";
                      echo "<td>".$row['LastName']."</td>";
                      echo "<td>".$row['FirstName']."</td>";
                      echo "<td>".$row['Email']."</td>";
                      echo "<td>".$row['PhoneNumber']."</td>";
                    echo "</tr>";

            }
                  echo "</table>";
        } else {
            echo "0 results";
        }
    }

if(!empty($sfname) || !empty($slname) || !empty($semail)){



$emailQueryPart = !empty($semail) ? "Email LIKE '%$semail%'" : "";
    $lastnameQueryPart = !empty($slname) ? "LastName LIKE '%$slname%'" : "";
    $firstnameQueryPart = !empty($sfname) ? "FirstName LIKE '%$sfname%'" : "";

        $arr = array($emailQueryPart, $lastnameQueryPart,$firstnameQueryPart);


        $sql = "select * from Userlist";

        for($i = 0; $i < count($arr); $i++){

            if(!empty($arr[$i])){

                if($i > 0){
                    $sql.= " AND ".$arr[$i];
                }else{
                    $sql.= " WHERE ".$arr[$i];
                }
            }
        }

    }else{
        echo "You must enter at least one value";
    }

checkres();
mysqli_close($conn);

?>

Answer 1

你有一些错误:

$sql = "select * from Userlist where FirstName LIKE '%". $sfname  ."%' AND LastName LIKE '%". %slname. "%'";

你有 %slname 而不是 $slname。

另一个错误是在程序流程中。在两种情况下将达到您的其他条件，即:“您尚未输入任何值”:

1. 当所有字段都留空时
2. 当所有字段都填满值时。

你不想要那个。你必须改进你的逻辑，并基于它构建一个查询，这可以像这样完成:

    function checkres()
{
    //Get query on the database
    $result = mysqli_query($conn, $sql);

    //Check results
    if (mysqli_num_rows($result) > 0)
    {
        //Headers
        echo "<table border='1' style='width:100%'>";
        echo "<tr>";
        echo "<th>Image ID</th>";
        echo "<th>Lastname</th>";
        echo "<th>Firstname</th>";
        echo "<th>Email</th>";
        echo "<th>PhoneNumber</th>";
        echo "</tr>";

        //output data of each row
        while($row = mysqli_fetch_assoc($result))
        {
            echo "<tr>";
            echo "<td>".$row['ID']."</td>";
            echo "<td>".$row['LastName']."</td>";
            echo "<td>".$row['FirstName']."</td>";
            echo "<td>".$row['Email']."</td>";
            echo "<td>".$row['PhoneNumber']."</td>";
            echo "</tr>";

        }
        echo "</table>";
    } else {
        echo "0 results";
    }
}

if(!empty($sfname) || !empty($slname) || !empty($semail)){

    $emailQueryPart = !empty($semail) ? "Email LIKE '$semail'" : "";
    $lastnameQueryPart = !empty($slname) ? "LastName LIKE '%$slname%'" : "";
    $firstnameQueryPart = !empty($sfname) ? "FirstName LIKE '%$sfname%'" : "";

    $arr = array($emailQueryPart, $lastnameQueryPart,$firstnameQueryPart);


    $sql = "select * from Userlist";

    for($i = 0; $i < count($arr); $i++){

        if(!empty($arr[$i])){

            if($i > 0){
                $sql.= " AND ".$arr[$i];
            }else{
                $sql.= " WHERE ".$arr[$i];
            }
        }
    }

}else{
    echo "You must enter at least one value";
}

checkres();
mysqli_close($conn);

?>