MySQL - Interpret NULL as 0

Question

i have the following MySQL-Query:

$sql = "SELECT `eid` FROM `"._ST_EVENT."` WHERE `tid` = ".$tid." AND DATE(`zeitpunkt`) >= CURDATE() ORDER BY `zeitpunkt` ASC;";

The variable $tid could be NULL, but should be interpreted as '0'. Is it possible to do this directly in the mysql-query or should i have a if-clause like this before executing the query?

if ($tid == NULL) {
    $tid = 0;
}

Thanks!

warning your code may be susceptible to sql injection attacks. — Daniel A. White
– Daniel A. White, Commented Jun 2, 2012 at 11:51

Andrius Naruševičius · Accepted Answer · 2012-06-02 11:53:09Z

1

COALESCE(id, 0) in mysql is your friend :)

7 Comments

khaled_webdev Over a year ago

if id=null i think will return null not 0 as i have understand

khaled_webdev Over a year ago

from doc: Returns the first non-NULL value in the list, or NULL if there are no non-NULL values.

Ravinder Reddy Over a year ago

@khaled_webdev why dont you just try select COALESCE( null, 0 ), COALESCE( 'not null', 0 ); and check the results.

Andrius Naruševičius Over a year ago

Well, programming is nice because there are many ways to solve the same problem. If it was Tar whose solution was fitting you the most, make sure you upvote it and mark as an answer :)

Andrius Naruševičius Over a year ago

P.S. I was not really sure if I understood what you meant by COALESCE(selected_team, 0) being still NULL. It just is impossible :)

|

Somnath Muluk · Accepted Answer · 2012-06-07 04:56:57Z

1

Use this condition:

"WHERE `tid` = ".($tid==null?0:$tid)."

edited Jun 7, 2012 at 4:56

Somnath Muluk

58.3k39 gold badges225 silver badges230 bronze badges

answered Jun 2, 2012 at 11:56

khaled_webdev

1,43015 silver badges20 bronze badges

1 Comment

khaled_webdev Over a year ago

i have write it in short time cause buzzy

Steve · Accepted Answer · 2012-06-02 11:56:18Z

0

Or:

$sql = "SELECT `eid` FROM `"._ST_EVENT."` WHERE `tid` = ".(!$tid ? 0 : $tid)." AND DATE(`zeitpunkt`) >= CURDATE() ORDER BY `zeitpunkt` ASC;";

answered Jun 2, 2012 at 11:56

Steve

3,0785 gold badges31 silver badges39 bronze badges

Comments

Ravinder Reddy · Accepted Answer · 2012-06-02 11:58:52Z

0

Use ifnull in your query.

$sql = "SELECT `eid` FROM `"._ST_EVENT."` WHERE `tid` = ifnull(".$tid.",0) AND DATE(`zeitpunkt`) >= CURDATE() ORDER BY `zeitpunkt` ASC;";

answered Jun 2, 2012 at 11:58

Ravinder Reddy

24.1k6 gold badges54 silver badges86 bronze badges

1 Comment

Ravinder Reddy Over a year ago

@Torben I am not sure, why COALESCE is not working on your end. Alternatively you can try ifnull on the column value.

triclosan · Accepted Answer · 2012-06-02 12:28:19Z

0

If you not prefer ANSI SQL you can use mysql IFNULL(id, 0)

edited Jun 2, 2012 at 12:28

answered Jun 2, 2012 at 11:57

triclosan

5,7846 gold badges30 silver badges53 bronze badges

Collectives™ on Stack Overflow

MySQL - Interpret NULL as 0

5 Answers 5

7 Comments

1 Comment

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

7 Comments

1 Comment

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related