PHP form input & mySQL Null

Question

I've got a form that submits data to a PHP script. I'm then querying a mySQL DB with the script to return the results.

Form:

<form class="search" action="customers_table.php" method="post">
    Customer ID: <input type="text" id="id" name="id" autocomplete="off"/><br/>
    Label: <input type="text" id="label" name="label" autocomplete="off"/><br/>
    Phone: <input type="text" id="phone" name="phone" autocomplete="off"/><br/>
    State: <input type="text" id="province" name="province" autocomplete="off"/><br/>
    <input type="submit" value="search"/>
</form>

PHP script:

$label = "%".$_POST['label']."%";
$id = "%".$_POST['id']."%";
$phone = "%".$_POST['phone']."%";
$province = "%".$_POST['province']."%";

$db->query("SELECT  id       AS 'Customer ID', 
                    label       AS 'Name', 
                    province    AS 'State', 
                    phone       AS 'Phone' 
            FROM customer 
            WHERE label     LIKE :label 
            AND   id        LIKE :id
            AND   phone     LIKE :phone
            AND   province  LIKE :province");

The problem is that some of the fields that I search on can contain NULL values in the DB. For example, 'phone' could be NULL.

Therefore, if phone is left blank in the above form and the form is submitted, I won't see the results where phone is NULL.

My query is looking for ...AND phone LIKE '%%' which excludes the NULL values.

Is there any way I can search on either phone LIKE '%whatever%' or if phone is empty, then nothing?

I could probably whack together something like the below, but I suspect there may be a better way? I'd be doing this for multiple columns, potentially.

    // if phone is entered, add to SQL query
    if(isset($_POST['phone'])) {
        $phone_qry = "AND phone LIKE :";
        $phone = "%".$_POST['phone']."%";
    }
    // if phone not entered, don't add to query
    else {
        $phone_qry = "";
        $phone = "";
    }


    $label = "%".$_POST['label']."%";
    $id = "%".$_POST['id']."%";
    $phone = "%".$_POST['phone']."%";
    $province = "%".$_POST['province']."%";

    $db->query("SELECT  id       AS 'Customer ID', 
                        label       AS 'Name', 
                        province    AS 'State', 
                        phone       AS 'Phone' 
                FROM customer 
                WHERE label     LIKE :label 
                AND   id        LIKE :id"
                . $phone_qry . $phone . " 
                AND   province  LIKE :province");

Try COALESCE: WHERE COALESCE(Label, '') LIKE :label etc. That changesLabel to an empty string if it's null. — Ed Gibbs
– Ed Gibbs, Commented Mar 1, 2015 at 0:42

Kevin Murphy · Accepted Answer · 2015-03-01 01:01:09Z

0

Thanks Ed! COALESCE: WHERE COALESCE(Label, '') LIKE :label worked perfectly. Exactly what I was after.

answered Mar 1, 2015 at 1:01

Kevin Murphy

1

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

Collectives™ on Stack Overflow

PHP form input & mySQL Null

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related