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

Question

So I am working on my search engine script and it is working well. But I would like to add the functionality to search for two selections in the same column. For example, I would like the search engine to give the user the ability to get results for people in both Ontario and Alberta for example. My input form lets the user check two boxes in the same field, but my script only searches for the second conditions selected.

Here is the search script.

    <?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");
    ?>

In case you need to see it, here's the part in my script that echoes out the list of provinces as a checkbox list (of course there is a lot of missing, but it is just to give you an idea)

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

Any ideas on how to make this work? Thanks in advance.

Answer 1

Make the checkboxes an array:

name='province[]'

Then implode the array and use the IN() construct in the query.

WHERE province IN(?)

Your using mysqli so use prepared statements mysqli_stmt_bind_param to construct the query. I prefer PDO.