CODEWITHSUNDEEP
phpmysql
Alexandre Cristo
Alexandre Cristo

Reputation: 331

Ignore null values on MYSQL query

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?

Upvotes: 1

Views: 2297

Answers (2)

user2860957
user2860957

Reputation: 476

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

Upvotes: 3

David
David

Reputation: 218828

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.

Upvotes: 1

Related Questions