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} ";
?>

Answer

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.

Leave a Reply

Your email address will not be published. Required fields are marked *