detecting and handling null returns from db

[jodunno]

Hello,

I'm a total newbie with db and db design. I currently have a login system with screwed up logic (manually entered id primary keys) until i learn how to connect these tables by userid, hence relational. Anyway, i have two columns in the user settings table: currentLogin and lastLogin. If you sign in for the first time, then there is no last login. My page obviously shows an error on the first login. Otherwise, the last login is displayed the next time that you login. I want to be able to detect a first login so that i can display a message instead of the last login data, which will be null. How can i do this correctly?

should i use a php if statement to detect null? if (empty($lastLogin)) ?

basically, i don't want my php script to show an error when the database returns a null value. How can this be done correctly?

Thank you.

[jodunno]

well i've tried the if (empty($lastLogin)) method and it works. I just display a simple message: 'details unavailable'. I don't want to detect a first login and maybe the null value isn't a first login anyway (perhaps corruption, tampering, etc). So, i will just use if empty(), unless there is a better method.

 

Thank you.

[Barand]

Another approach would be to have a table in which you record users' logins.

CREATE TABLE `user_login` (
  `login_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `login_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`login_id`),
  KEY `idx_user_login_user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

To get the last login you query the table for the latest time. For example, suppose the table contains

+----------+---------+---------------------+
| login_id | user_id | login_time          |
+----------+---------+---------------------+
|        1 |       1 | 2020-05-08 10:31:20 |
|        2 |       2 | 2020-05-09 14:54:11 |
|        3 |       1 | 2020-05-09 14:55:39 |
+----------+---------+---------------------+

then, for user #1

SELECT
    COALESCE(MAX(login_time), 'Data unavailable') as last_login 
FROM user_login
WHERE user_id = 1;

+---------------------+
| last_login          |
+---------------------+
| 2020-05-09 14:55:39 |
+---------------------+

but for user #3

SELECT
    COALESCE(MAX(login_time), 'Data unavailable') as last_login 
FROM user_login
WHERE user_id = 3;

+------------------+
| last_login       |
+------------------+
| Data unavailable |
+------------------+

After getting the last login, write the new login (the login time will be inserted automatically)

INSERT INTO user_login (user_id) VALUES ( ? );

 

[jodunno]

Hi Barand,

wow! that is a pretty elegant way of including last login data. You're clearly an sql expert. I didn't know about COALESCE and i didn't think about a separate table for logins. Very nice example. Thank you! I will make a copy of your post and use it as a guideline for revamping my code. I will also need to redesign my database. I'll let you know how it goes. I am learning alot from you but most importantly how to be a better database designer/programmer. Thank you very much! I really appreciate your wisdom.

Best wishes :-)