How to select all where the condition is null?

Question

I have a php code with a query:

$query = "SELECT * FROM TDdb WHERE status = $status AND occupation =$occupation";

I am sending the values status and occupation with a client application to this php code. This works when I send both status and occupation. But I want it to return rows if I just send status but not occupation also ( I mean no matter what the occupation is).

does anyone have any suggestions? I would appreciate any help.

PS: I want to do it without if statement and just but changing the query

what do you mean "without if statement" and how do you expect to change the query without it? — cernunnos, Apr 17 '13 at 13:28
obligatory warning regarding SQL injection: http://stackoverflow.com/questions/60174/how-to-prevent-sql-injection-in-php — Alkini, Apr 17 '13 at 13:32

score 1 · Accepted Answer · answered Apr 17 '13 at 13:39

1

Personally I would create a base query and append conditions wherever you have them, like so:

$sql = 'SELECT * FROM TDdb';
$conditions = array();
$args = array();

if ($action) {
    $conditions[] = 'status = :status';
    $args[':status'] = $status;
}

if ($occupation) {
    $conditions[] = 'occupation = :occupation';
    $args[':occupation'] = $occupation;
}

if ($conditions) {
    $sql .= ' WHERE ' . join(' AND ', $conditions);
}

$stmt = $db->prepare($sql);
$stmt->execute($args);

answered Apr 17 '13 at 13:39

Ja͢ck

170,779
38
263
309

thanks jack. your idea helped a lot. finally I created something like this: – Alex Apr 17 '13 at 14:27
$query = 'SELECT * FROM TDdb'; if ($status) { $query = $query." WHERE status = '".$status."'"; } if ($occupation) { $query = $query." AND occupation = '".$occupation."'"; } – Alex Apr 17 '13 at 14:27
@user2108800 That will not work if `$occupation` is defined but not `$status`. – Ja͢ck Apr 17 '13 at 14:29

score 1 · Answer 2 · answered Apr 17 '13 at 14:03

Looks like you've got a few good options for how to do it in SQL, or how to make the SQL string variable in PHP.

One reason to consider using an 'if' in the PHP code for the database access performance.

When you introduce an 'or' condition like that in SQL, you're not going to get index access. It is much harder for the database to determine what path it should take than for the PHP code because the SQL engine optimizes the query without knowing what the variable will resolve to at execution.

You already know in the PHP which version of the query you really want. This will perform better if you make that choice there.

score 0 · Answer 3 · answered Apr 17 '13 at 13:28

0

This will work if you pass an occupation or a NULL value.

SELECT * 
FROM   TDdb 
WHERE  status = $status 
       AND ($occupation IS NULL OR occupation = $occupation)

answered Apr 17 '13 at 13:28

Tom

6,593
3
21
42

that won't work, if $occupation is NULL then your query will look like this: `...WHERE status = X AND ( IS NULL OR occupation = )`, you need to add the quotes – Damien Legros Apr 17 '13 at 13:42
If the value is passed as a NULL this will work. If the value is passed as an empty string you would need quotes. – Tom Apr 17 '13 at 13:45
both `$occupation = NULL;` and `$occupation = ""` won't work, `$occupation = "NULL";` will work but won't have the expected behavior – Damien Legros Apr 17 '13 at 13:56
If a NULL value is passed (not an empty string, or a string = "NULL"), `$occupation IS NULL` will evaluate to true, and the OR clause will be ignored. – Tom Apr 17 '13 at 19:00
This produces a syntax error (at least on my mysql server version 5.5.13, maybe that's different with newer versions) – Damien Legros Apr 17 '13 at 19:18

score 0 · Answer 4 · answered Apr 17 '13 at 13:30

0

"SELECT * FROM TDdb WHERE status = '$status' AND (occupation = '$occupation' OR occupation IS NULL)";

answered Apr 17 '13 at 13:30

Damien Legros

519
3
7

score 0 · Answer 5 · answered Apr 17 '13 at 13:42

Apart from the solution provided by @Tom and @Damien Legros, you may create two query strings one with occupation and one without occupation. Something like:

$query = "SELECT * FROM TDdb WHERE status = $status";
if ($occupation != "") { 
   /*When you have value for occupation*/
   $query .= " AND occupation =$occupation";
}

So in this case, data will be returned if you have only the status field. Secondly, please check if the status and occupation fields in table are varchar then you have to enclose them in single quotes (').

score 0 · Answer 6 · answered Apr 17 '13 at 14:31

Thanks everyone for help. specially jack. finally i created my query like this:

$query = 'SELECT * FROM TDdb';

if ($status) {
    $query = $query." WHERE status = '".$status."'";
}

if ($occupation) {
    $query = $query." AND occupation = '".$occupation."'";
}

How to select all where the condition is null?

6 Answers6