PHP search function....use two conditions from the same variable

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.

You need a SQL statement which will say something like this: WHERE city = Ontario OR city = Alberta. How to retrieve values from multiple checkboxes, read here: http://stackoverflow.com/questions/4997252/get-post-from-multiple-checkboxes — Jo Smo, Mar 27 '14 at 19:30
for your html form you can use a select input with multiple selections enabled or I think you need to use a different 'name' for each check box for province such as province_1 province_2 — dboals, Mar 27 '14 at 19:33

score 1 · Answer 1 · answered Mar 27 '14 at 19:31

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.

answered Mar 27 '14 at 19:31

AbraCadaver

78,200
7
66
87

I still need `mysqli_stmt_bind_param` even though nothing is being written to the table? – JLA Mar 27 '14 at 19:33
@JLA: Not written, but a statement is being sent to the database. Prepared statements are preferred and safer, though you could do it the way you have it now. – AbraCadaver Mar 27 '14 at 19:34
Hi AbraCadaver, I'm still having issues. I am now getting an error `cannot run the query because: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE province IN ('Alberta', 'Ontario') ORDER BY surname LIMIT 0, 5' at line 1` . What might the problem be? – JLA Mar 28 '14 at 16:18

PHP search function....use two conditions from the same variable

1 Answers1