Jump to content

detecting and handling null returns from db


Recommended Posts

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.

Link to post
Share on other sites

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.

Link to post
Share on other sites

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 ( ? );

 

Link to post
Share on other sites

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 :-)

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.