Creating each operation and testing
Because we will use PHP in later stages, let's prepare each operation now. Here, we will insert some temporary data.
Remember to check that the acquisition and update operations are working properly.
User authentication
These are some SQL code you can use to develop your database.
You can look for a user by inputting an e-mail address and password. You can assume it was successful if the count is 1
.
For increased password security, after having carried out MD5 encryption, you should store the password as a character string of 40 characters after being put through SHA1.
SELECT COUNT(id) as auth FROM users WHERE users.email = 'extkazuhiro@xenophy.com' AND users.passwd = SHA1(MD5('password')) AND users.status = 1;
Selecting the user list
This is used when you want to collect data for use in a grid. Make note of the fact that we are not performing the limit operation with PagingToolbar
:
SELECT users.id, users.email, users.lastname, users.firstname FROM users WHERE users.status = 1;
Adding users
To add a user, put the current time in created
and modified
:
INSERT INTO users ( email, passwd, lastname, firstname, modified, created ) VALUES ( 'someone@xenophy.com', SHA1(MD5('password')), 'Kotsutsumi', 'Kazuhiro', NOW(), NOW() );
Updating the user information
Every time the modified
file should be set to NOW()
for it to be used as a time stamp. Other fields should be updated as needed.
UPDATE users SET email='extkazuhiro@xenophy.com', passwd=SHA1(MD5('password')), lastname='Kotsutsumi', firstname='Kazuhiro', modified=NOW() WHERE id=1
Deleting users
Deletion from this system is not a hard purge where the user data is permanently deleted. Instead we will use a soft purge, where the user data is not displayed after deletion but remains in the system. Therefore, note that we will use UPDATE
, not DELETE
. In the following code, status=9
denotes that the user has been deleted but not displayed. (status=1
will denote that the user is active).
UPDATE users SET status=9 WHERE id=1