How to search with multiple columns?

Question

I have an app with the following form:

<form action="<?php echo base_url('registration/registrationSearch'); ?>" method="post">
    <td widtd="40">&nbsp;</td>
    <td><input type="text" name="txt_apply_for"        class="form-control" /></td>
    <td><input type="text" name="txt_name"             class="form-control" /></td>
    <td><input type="text" name="txt_mobile"           class="form-control" /></td>
    <td><input type="text" name="txt_address"          class="form-control" /></td>
    <td><input type="text" name="txt_state"            class="form-control" /></td>
    <td><input type="text" name="txt_city"             class="form-control" /></td>
    <td><input type="text" name="txt_area"             class="form-control" /></td>
    <td><input type="text" name="txt_email"            class="form-control" /></td>
    <td><input type="text" name="txt_work_experience"  class="form-control" /></td>
    <td><input type="text" name="txt_pan"              class="form-control" /></td>
    <td><input type="text" name="txt_refrence"         class="form-control" /></td>
    <td><input type="text" name="txt_refrence_others"  class="form-control" /></td>
    <td><input type="text" name="txt_status"           class="form-control" /></td>
    <td><input type="text" name="txt_payment_others"   class="form-control" /></td>
    <td><input type="text" name="txt_application_date" class="form-control" /></td>
    <td><input type="submit" name="sbt" class="form-control" value="search" /></td>
</form>

I noticed that when I submit it with only the name, for example, then I get no results.
The same happens when I search for name and mobile: no results returned.
My search only works when I search for every field.

This is the query generated when I am searching for name and mobile:

SELECT * 
FROM `application` 
WHERE `apply_for` = '' 
AND `full_name` = 'Ashish' 
AND `mobile` = '1234567890' 
AND `Mumbai` = '' 
AND `Maharshtra` = '' 
AND `Mumbai` = '' 
AND `area` = '' 
AND `email_id` = '' 
AND `work_experinece` = '' 
AND `pan` = '' 
AND `refrence` = '' 
AND `refrence_for_others` = '' 
AND `approved` = '' 
AND `payment_status` = ''

What am I doing wrong that my search only works when every field is filled?
How to properly search the database when there's a field on the form that was not filled?

P.S.: I am using PHP on my backend.

Answer 1

Using the AND boolean operator in the WHERE clause means that all of those conditions must be met in order to return a record. In the example in your question, it is only going to return records where all the columns are empty except for full_name and mobile.

Option 1: Dynamically create the WHERE clause string in your php code. Add statements to the WHERE clause only if the form's field value is not blank. Referencing the example in your question, your sql would look like this. If more form fields had values, just append those to the WHERE clause.

SELECT * 
FROM `application` 
WHERE `full_name` = 'Ashish' 
AND `mobile` = '1234567890'

Option 2: Have your sql check for null values.

SELECT * 
FROM `application` 
WHERE `mobile` = ifnull([your parameter value], `mobile`)
AND `full_name` = ifnull([your parameter value], `full_name`) 
AND `mobile` = ifnull([your parameter value], `mobile`)
etc.

Side note: Be sure to use parameterized queries or you are prone to sql injection attacks. See How can I prevent SQL injection in PHP? for a very easy way to harden your code.

Answer 2

Another option, you could edit the script in your backend, that you are using to fetch the data, to be something like this:

<?php
    // Grab every data sent to you.
    $name = $_POST['txt_name'];
    $mobile = $_POST['txt_mobile'];
    // .. other data here
    $payment_status = $_POST['txt_status'];

    // Create the arrays to store the parameters you will use
    $types = array();
    $where = array();
    $param = array();

    // Let's check every parameter...
    // Have the user sent `name` to us?
    if( $name != null ) {
        // Then let's create a WHERE to it...
        // How to choose the type: https://php.net/manual/en/mysqli-stmt.bind-param.php
        $types[] = 's';
        $where[] = '`full_name` = ?';
        $param[] = $name;
    }

    // The same for `mobile`
    if( $mobile != null ) {
        $types[] = 's';
        $where[] = '`mobile` = ?';
        $param[] = $mobile;
    }

    // ... other parameters here...

    // And to `payment_status`
    if( $payment_status != null ) {
        $types[] = 's';
        $where[] = '`payment_status` = ?';
        $param[] = $payment_status;
    }

    $sql = "SELECT * FROM `application`";

    if( !empty( $param ) ) {
        $types = implode( '', $types )
        $sql = $sql . ' ' . implode( ' AND ', $where );
    }

    $stmt =  $mysqli->stmt_init();

    $stmt->prepare( $sql );
    if( !empty( $param ) ) {
        $stmt->bind_param( $types, ...$param );
    }
    $stmt->execute();

    // Use the fetched data any way you want...
?>

Not related:

• There are other options to build a query and fetch data. (This is only one of those options, and an oversimplified solution while at it.)
• Please keep in mind that you should (try to) properly create the objects that you would use.
• Avoid using a single script that does everything. And try to use a good framework.

I hope it helps. If not, then please let me know.