Check db table field (datetime) if it's empty or not

Question

After successful user registration my MySQL db table puts 0000-00-00 00:00:00 into lastlogin_date and lastlogout_date fields by default. type of both fields is datetime. In my php code i'm checking if user enters to the site for the first time

$lastlogin = $db->query("SELECT `lastlogin_date` FROM `users` WHERE `id`='$_SESSION[id]'");
    $row = $lastlogin->fetch_array(MYSQLI_BOTH);
    $lastlogin=$row['lastlogin_date'];
    if(empty($lastlogin))
    { do something}

But, seems, empty doesnt work in this case. how to check if the lastlogin_date field is empty or not? if($lastlogin=='0000-00-00 00:00:00')??

if ($lastlogin == '0000-00-00 00:00:00') is not a bad option — tttony
– tttony, Commented Sep 1, 2011 at 4:56

Vincent Savard · Accepted Answer · 2011-09-01 04:51:42Z

7

0000-00-00 00:00:00 is a value. If you don't want to put any value, you should store NULL, which would make much more sense. NULL is the lack of value.

That being said, I think the best way is to select a boolean instead of your column :

-- (lastlogin_date is NULL) if you change your table structure
SELECT (lastlogin_date = '0000-00-00 00:00:00') as has_logged_in
  FROM users
 WHERE id = ?;

When you fetch your query, you can use $row['has_logged_in'], which is a boolean.

answered Sep 1, 2011 at 4:51

Vincent Savard

36.1k10 gold badges71 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Tural Ali Over a year ago

i heard that "null" is buggy. better to use empty string or default "0" but actually i don't understand the difference between them and pros cons

Vincent Savard Over a year ago

How is NULL buggy? That's a complete non sense. NULL only takes 1 bit while a value can take up to several bytes, and NULL is the lack of value, as I said. Using an arbitrary value can also makes queries like SELECT COUNT(some_col) totally wrong. There's no point to use a value instead of NULL when the latter is more appropriated.

Herbert Over a year ago

Where in the world did you hear that NULL is buggy? lol! I'd be really interested in hearing more about that. :) As @Vincent points out, NULL indicates "no value". How can that be buggy?

Collectives™ on Stack Overflow

Check db table field (datetime) if it's empty or not

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related