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.
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 = '[email protected]'
AND
users.passwd = SHA1(MD5('password'))
AND
users.status = 1;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;To add a user, put the current time in created and modified:
INSERT INTO users (
email,
passwd,
lastname,
firstname,
modified,
created
) VALUES (
'[email protected]',
SHA1(MD5('password')),
'Kotsutsumi',
'Kazuhiro',
NOW(),
NOW()
);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='[email protected]',
passwd=SHA1(MD5('password')),
lastname='Kotsutsumi',
firstname='Kazuhiro',
modified=NOW()
WHERE
id=1Deletion 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