Ignore null values on MYSQL query

Question

I have a form for users to enter some information. After the form being submitted, it should query a database with the values that the user entered.

My problem here is that if some of the values that the user entered are null, it should remove from the query.

This is my code:

if(isset($_POST['submit']))
{
include("../includes/header.php");
include ("../scripts/db/connect.php");

//Gets variables from $_POST
$negocio = $_POST['negocio'];
$imovel = $_POST['imovel'];
$distrito = $_POST['distrito'];
$concelho = $_POST['concelho'];
$freguesia = $_POST['freguesia'];

$query = "SELECT * FROM imoveis WHERE negocio = $negocio and imovel = $imovel and distrito = $distrito and concelho = $concelho and freguesia = $freguesia";
}

Imagine if $negocio, $imovel, $concelho and $freguesia are equal to null, the query should be:

$query = "SELECT * FROM imoveis WHERE distrito = $distrito;

How can I do this?

You could use OR's instead of AND's or use a subquery with a CASE, along with a IS NULL. Your variables are unclear if they're strings or not and the db schema for this. — Funk Forty Niner
– Funk Forty Niner, Commented Apr 3, 2017 at 17:15
Alexandre you have two answer and both are correct, read them once more if you are confuse — arif_suhail_123
– arif_suhail_123, Commented Apr 3, 2017 at 18:07
I would call a stored proc and pass in all parameters not checking for null in php code. Once the parameters are in the stored proc, I would simply use a SELECT statement with WHERE clause and use OR between them. — Yawar Murtaza
– Yawar Murtaza, Commented Apr 3, 2017 at 19:46

user2860957 · Accepted Answer · 2017-04-03 17:33:34Z

Generate your query string dynamcilly depending on which value are set or not null, and than use that query

Run this code in a seperate file you will understand the point, after removing or adding comment to any variable, ($name,$street, $address or $qualification )

// you will see query will change depending on the set variable,
//I am using these name you can use any name for your variable

$name='my name';
//$address='some where on earth';
$street='this is my street';
//$qualification='i am very much qualified';

//now create the array only with the values which are not empty or not nul,
//I am using empty you can use null if you want with this example you can use any thing.
if(!empty($name)) $query_string_second_part[]=" AND name = '$name'";
if(!empty($address)) $query_string_second_part[]=" AND address = '$address'";
if(!empty($street)) $query_string_second_part[]=" AND street = '$street'";
if(!empty($qualification)) $query_string_second_part[]=" AND qualification = '$qualification'";

//hand type the first part for the query
$query_string_First_Part= "SELECT * FROM myTableName WHERE";
//Implode the array, if you want to see how it look like use echo,
$query_string_second_part= implode(" ", $query_string_second_part);
//as you can see we are adding AND with every value, so we need to remove the first AND
//with one space
//Make sure you give space in the second parameter. else it wont work means "" not correct but " " is correct
//Hint --> use one space in between the double qoutes
$query_string_second_part=  preg_replace("/AND/", " ", $query_string_second_part, 1);

//Join the first and second part together to create a full query
$query_string=$query_string_First_Part.$query_string_second_part;
echo ($query_string);//see how our query look like at the moment

David · Accepted Answer · 2017-04-03 17:15:38Z

1

You can add an input null check to each clause. So for example where you do this:

distrito = $distrito

You might instead do this:

(distrito = $distrito or $distrito IS NULL)

or perhaps:

(distrito = $distrito or $distrito = '')

Depending on the data types, the actual input being used to build the query, etc. Might take some tweaking and debugging when manually building a query like this (I suspect using prepared statements with query parameters will make this cleaner, as well as more secure), but the idea is the same either way.

Basically you're instructing it to match the row based on the value, or match the row based on the lack of value. So for any given clause, if the supplied value is null/empty, then all rows match and the clause becomes moot.

answered Apr 3, 2017 at 17:15

David

221k42 gold badges245 silver badges337 bronze badges

2 Comments

Alexandre Cristo Over a year ago

I don't understand your idea. If I try to query with a null value, it will return results that have null values

David Over a year ago

@AlexandreCristo: The idea is that if you supply a NULL value then all rows are returned, because that search value is interpreted as not having been used in the search. Consider what the (distrito = $distrito or $distrito IS NULL) for any given value you provide it, NULL or otherwise.

Collectives™ on Stack Overflow

Ignore null values on MYSQL query

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related