Search MySQL Database with Multiple Fields in a Form

Question

I have created a form where the user can search the database, and the result depends on how the user fills out the form.
For example, say I have name, address, city, state, and zip field, and the user fills out name and city fields, the results reflect the input. When the form submits all records are displayed. for this I write this:

if(isset($_POST['submit'])) {
        $sql = mysql_query("SELECT * FROM table WHERE name LIKE '%" . $_POST['name'] . "%'
                   OR address LIKE '%" . $_POST['address'] . "%'
                   OR city LIKE '%" . $_POST['city'] . "%'
                   OR state LIKE '%" . $_POST['state'] . "%'
                   OR zip LIKE '%" . $_POST['zip'] . "%'");
    }


        <form method="post" action="<?php $_SERVER['PHP_SELF']; ?>">
            <tr>
                <td>Name:</td>
                <td><input type="text" name="name" /></td>
            </tr>
            <tr>
                <td>Address:</td>
                <td><input type="text" name="address" /></td>
            </tr>
            <tr>
                <td>City:</td>
                <td><input type="text" name="city" /></td>
            </tr>
            <tr>
                <td>State:</td>
                <td><input type="text" name="state" /></td>
            </tr>
            <tr>
                <td>Zip:</td>
                <td><input type="text" name="zip" /></td>
            </tr>
            <tr>
                <td>&nbsp;</td>
                <td><input type="submit" name="submit" value="Search" /></td>
            </tr>
        </form>
    </table>

    <?php
        if(isset($_POST['submit'])) {
            while($row = mysql_fetch_array($sql)) {
                echo $row['name'] . "<br />";
            }
        }   
    ?>

But in this case a user may leave a field blank.

The coding here is not secure against malicious users. Please read stackoverflow.com/questions/60174/…. — David Gorsline
– David Gorsline, Commented May 9, 2012 at 16:43

Stelian Matei · Accepted Answer · 2014-12-16 11:11:39Z

14

Try this:

if(isset($_POST['submit'])) {
    // define the list of fields
    $fields = array('name', 'address', 'city', 'state', 'zip');
    $conditions = array();

    // loop through the defined fields
    foreach($fields as $field){
        // if the field is set and not empty
        if(isset($_POST[$field]) && $_POST[$field] != '') {
            // create a new condition while escaping the value inputed by the user (SQL Injection)
            $conditions[] = "`$field` LIKE '%" . mysql_real_escape_string($_POST[$field]) . "%'";
        }
    }

    // builds the query
    $query = "SELECT * FROM TABLE ";
    // if there are conditions defined
    if(count($conditions) > 0) {
        // append the conditions
        $query .= "WHERE " . implode (' AND ', $conditions); // you can change to 'OR', but I suggest to apply the filters cumulative
    }

    $result = mysql_query($query);

edited Dec 16, 2014 at 11:11

answered Feb 16, 2012 at 19:39

Stelian Matei

11.6k3 gold badges28 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

David Gorsline Over a year ago

The coding here is not secure against malicious users. Please read stackoverflow.com/questions/60174/….

digitaltoast Over a year ago

I see this answer is marked as best and uprated, but shouldn't $_POST['field'] be $_POST[$field] in line 9? I don't want to edit as I'm not a skilled enough php-er to be sure, or know why it's been written as it has. It works for me now I changed it, but have I opened up a security hole (post input has been sanitized).

Stelian Matei Over a year ago

@digitaltoast you're right. thank you for the correction. i updated the answer

Andrew Glass Over a year ago

Stelian - i came across this answer and ive tried to modify it the best i can myself but im having some trouble with it. would you mind scanning your eye over it? stackoverflow.com/questions/33917688/php-multiple-input-search when i run it it gives me all the results from the database, not a specific few

Collectives™ on Stack Overflow

Search MySQL Database with Multiple Fields in a Form

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related