PHP 搜索功能...使用来自同一变量的两个条件

Question

所以我正在编写我的搜索引擎脚本，它运行良好。但我想添加在同一列中搜索两个选择的功能。例如，我希望搜索引擎能够使用户能够获取安大略省和阿尔伯塔省的人们的结果。我的输入表单允许用户选中同一字段中的两个框，但我的脚本仅搜索所选的第二个条件。

这是搜索脚本。

    <?php
    require_once("models/config.php");
    define("NUMBER_PER_PAGE", 5);  

    function pagination($current_page_number, $total_records_found, $query_string = null)
    {
        $page = 1;

        echo "Page: ";

        for ($total_pages = ($total_records_found/NUMBER_PER_PAGE); $total_pages > 0; $total_pages--)
        {
            if ($page != $current_page_number)
                echo "<a href=\"?page=$page" . (($query_string) ? "&$query_string" : "") . "\">";

            echo "$page ";

            if ($page != $current_page_number)
                echo "</a>";

            $page++;
        }
    }

    $page = ($_GET['page']) ? $_GET['page'] : 1;
    $start = ($page-1) * NUMBER_PER_PAGE;

    $personid = ($_POST['personid']) ? $_POST['personid'] : $_GET['personid'];
    $firstname = ($_POST['firstname']) ? $_POST['firstname'] : $_GET['firstname'];
    $surname = ($_POST['surname']) ? $_POST['surname'] : $_GET['surname'];
    $address = ($_POST['address']) ? $_POST['address'] : $_GET['address'];
    $city = ($_POST['city']) ? $_POST['city'] : $_GET['city'];
    $province =($_POST['province']) ? $_POST['province'] : $_GET['province'];
    $postalcode = ($_POST['postalcode']) ? $_POST['postalcode'] : $_GET['postalcode'];
    $phone = ($_POST['phone']) ? $_POST['phone'] : $_GET['phone'];
    $email = ($_POST['email']) ? $_POST['email'] : $_GET['email'];

    $sql = "SELECT * FROM persons WHERE 1=1";

    if ($personid)
        $sql .= " AND personid='" . mysqli_real_escape_string($mysqli,$personid) . "'";

    if ($firstname)
        $sql .= " AND firstname='" . mysqli_real_escape_string($mysqli,$firstname) . "'";

    if ($surname)
        $sql .= " AND surname='" . mysqli_real_escape_string($mysqli,$surname) . "'";

    if ($address)
        $sql .= " AND address='" . mysqli_real_escape_string($mysqli,$address) . "'";

    if ($city)
        $sql .= " AND city='" . mysqli_real_escape_string($mysqli,$city) . "'";

    if ($province)
        $sql .= " AND province='" . mysqli_real_escape_string($mysqli,$province) . "'";

    if ($postalcode)
        $sql .= " AND postalcode='" . mysqli_real_escape_string($mysqli,$postalcode) . "'";

    if ($phone)
        $sql .= " AND phone='" . mysqli_real_escape_string($mysqli,$phone) . "'";

    if ($email)
        $sql .= " AND email='" . mysqli_real_escape_string($mysqli,$email) . "'";

    $total_records = mysqli_num_rows(mysqli_query($mysqli,$sql));

    $sql .= " ORDER BY surname";
    $sql .= " LIMIT $start, " . NUMBER_PER_PAGE;


    pagination($page, $total_records, "personid=$personid&firstname=$firstname&surname=$surname&address=$address&city=$city&province=$province&postalcode=$postalcode&phone=phone&email=$email");


    $loop = mysqli_query($mysqli,$sql)
        or die ('cannot run the query because: ' . mysqli_error($mysqli,i));

     echo "<table border='1' cellpadding='10'>";
            echo "<tr> <th>First Name</th> <th>Surname</th> <th>Email</th> <th></th> <th></th></tr>";

    while ($record = mysqli_fetch_assoc($loop)) {
        echo "<tr>";
                    echo '<td>' . $record['firstname'] . '</td>';
                    echo '<td>' . $record['surname'] . '</td>';
                    echo '<td>' . $record['email'] . '</td>';

                    echo ("<td><a href=\"records.php?$record[personid]\">Edit</a></td>");

                    echo '<td><a href="delete.php?id=' . $record['personid'] . '">Delete</a></td>';
                    echo "</tr>"; 


    }
    echo "</table>";

    echo "<center>" . number_format($total_records) . " search results found</center>";


    pagination($page, $total_records, "personid=$personid&firstname=$firstname&surname=$surname&address=$address&city=$city&province=$province&postalcode=$postalcode&phone=phone&email=$email");
    ?>

如果您需要查看它，这是我的脚本中的部分，它以复选框列表的形式回显省份列表(当然有很多缺失，但这只是为了给您一个想法)

  <p><b>Province:</b>
 <?   
while ($row = mysqli_fetch_assoc($result)) { 
 echo "<input type='checkbox' name='province' value=' {$row['province']} '>  {$row['province']} ";
  } 
  ?>  </p><br />

关于如何实现这项工作有什么想法吗？提前致谢。

Answer 1

将复选框设为数组:

name='province[]'

然后内爆数组并在查询中使用IN()构造。

WHERE province IN(?)

您使用mysqli，因此请使用准备好的语句mysqli_stmt_bind_param来构建查询。我更喜欢 PDO。