SELECT from database when i don´t know position of null values

Question

Good afternoon. I need your help to make correctly query into database.

I have following table in DB with adresses, eg.

id	street	house_nuber	city	district
1	First street	1225	City One	NULL
2	NULL	25	Small city	NULL
3	Second street	51	Roswell	District nine
4	Third street	15963	Last city	Another district

In the table above you can see i have adresses. I also have random NULL values in rows. My table has about 50k records for now.

PHP code fill variables with following values

$street = 'First street';
$houseNum = 1125;
$city = 'City One';
$district = NULL;

I need to return ID from database of matching adress. If i know, where NULL value is, query is easy.

SELECT id FROM adress WHERE street=$street AND house_number=$houseNum AND city=$city AND district IS NULL

(normaly i use PDO, this is only for ilustration)

And now my question. How to make query, If i can´t predict, where NULL value is? I tryed to make query with empty strings, but for database is not same the NULL and empty and i always had false return.

Thak you and have a nice day.

When you have a table with IDs and additional columns of data, queries that specify the ID are generally “lookups” or “retrievals”, whereas queries that specify the other columns are “searches”, which is what you have. When searching, you just throw what you know at the database, and if you don’t know it, you don’t include it. This might mean dynamically building your SQL statement. There’s also the [spaceship operator](https://stackoverflow.com/a/13353424/231316) which might help. — Chris Haas, Sep 19 '21 at 15:35
Hi Chriss Haas, I tryed to aply spaceship operator, but this operator did not solve my problem. Anyway thank you for your response, I learned something new :) — Robin, Sep 20 '21 at 11:21

score 1 · Accepted Answer · answered Sep 19 '21 at 18:40

1

To match exactly rows in your table, you can use NULLIF() to convert your PHP empty strings to SQL NULL values and NULL-safe operator to compare NULL to them without NULL result.

Ex. :

$sql = <<<SQL
    SELECT id 
    FROM adress 
    WHERE TRUE 
        AND street <=> NULLIF({$pdo->quote($street)}, '')
        AND house_number <=> NULLIF({$pdo->quote($houseNum)}, '')
        AND city <=> NULLIF({$pdo->quote($city)}, '')
        AND district <=> NULLIF({$pdo->quote($city)}, '')
    SQL;

answered Sep 19 '21 at 18:40

JCH77

1,125
13
13

Hi JCH77, thank you for your solution, it works perfectly! I wrote to Chris that the spaceship operator didn't work for me. Apparently, I used it wrong :-). In addition, I learned a very important function. Thanks – Robin Sep 20 '21 at 11:40

score 0 · Answer 2 · answered Sep 19 '21 at 16:48

You can append the where condition like the below:

$street_query = $street != null ? " AND street='$street' " : " AND street IS NULL ";
$houseNum_query = $houseNum != null ? " AND house_number='$houseNum' " : " AND house_number IS NULL ";
$city_query = $city != null ? " AND city='$city' " : " AND city IS NULL ";
$district_query = $district != null ? " AND district='$district' " : " AND district IS NULL ";

$query = "SELECT id FROM adress WHERE 1 = 1 $street_query $houseNum_query $city_query $district_query";

Note: 1 = 1 to let next AND + condition is always a correct query.

Thanks for your answer. Your solution is interesting, but it won't completely solve my situation. In any case, it is instructive. Thank you. — Robin, Sep 20 '21 at 11:39

score 0 · Answer 3 · answered Sep 19 '21 at 20:49

Try below query it finds street, house, etc null or not (match condition)

$query = "SELECT id FROM adress WHERE id>0 
AND street = IF($street != null, $street, NULL)
AND house_number = IF($houseNum != null, $houseNum, NULL)
AND city= IF($city != null, $city, NULL) 
AND district= IF($district != null, $district, NULL)

SELECT from database when i don´t know position of null values

3 Answers3