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