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 = '[e-mail]'
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 (
    '[e-mail]',
    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='[e-mail]',
    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