CODEWITHSUNDEEP
mysql
Eric T
Eric T

Reputation: 946

Alternate Syntax in MySQL for 'IS NOT NULL'

I'm using php to compile a long mySQL query. For the sake of context, I'm making a mySQL query that scans through the table 'rentals', which has the fields 'id', 'rent', 'type', 'num_bedrooms'. Example of an entry could be:

rent=600, type=house, num_bedrooms=5

or

rent=450, type=apartment, num_bedrooms=3

When filling out the search field, the user has the option to search 'any' for 'type' and 'num_bedrooms'. Paying attention to the line of SQL 'type IS NOT NULL': **Is it possible to say 'type = NOT NULL' instead?

(the idea I have is that if the user searches 'any' for 'type', then I set a variable '$type' in php as either 'house' to find houses, 'apartment' to find apartments, or 'NOT NULL' to find all)

<?php
$min_rent = 200;
$max_rent = 1000;
$type = "NOT NULL"; //or it can be equal to "house" or "apartment" 
$num_bedrooms = 2;  //e.g. 2 or more bedrooms

$sql = "SELECT * FROM properties WHERE ";
$sql .= "rent >= {$min_rent} AND rent <= {$max_rent} AND ";
$sql .= "type = {$type} ";  //in this case, I want 'type' to equal 'NOT NULL'
$sql .= "num_bedrooms >= {$num_bedrooms} ";
?>

Upvotes: 1

Views: 5377

Answers (4)

ejay56
ejay56

Reputation: 41

One option is:

SELECT * FROM properties WHERE type != ''

Upvotes: 0

Karolis
Karolis

Reputation: 9562

Actually there is an alternative to is not NULL. For example:

SELECT * FROM properties WHERE type = type

This will return all rows when type is not null.

Upvotes: 5

rajah9
rajah9

Reputation: 12329

Welcome to SO.

SQL treats a null differently than another string. It's not possible to say type = "NOT NULL". MySQL will return the types with that contain NOT NULL. (Not, as you might hope, those containing "house" or "apartment."

That line would have to read $sql .= "type IS NOT NULL "

Of course, you could put an IF statement that appends the above line in your special case.

Upvotes: 1

Brian Hoover
Brian Hoover

Reputation: 7991

No - IS NOT NULL is a special function.

Generally what you would do is put the $sql .= "type = {$type} ";

into an if statement, that would check the value of $type as the SQL statement was constructed.

Upvotes: 2

Related Questions