Hi All,
I have a page where you can create or edit user data. When you click the edit button, a box displays and tells you who and when that user was created AND who and when the user was updated last.
The issue is that the 'createdBy' and 'updatedBy' names are not correct, because of my query. I'm guess this is because all this data is within the same table and I'm not joining any tables?
Also, the created_by and updated_by fields are user_id's of the user who created or updated it.
Thanks for the help in advance.
SELECT firstname, lastname, email, level, deactivated_on, updated_on, created_on,
(SELECT CONCAT( firstname,' ', lastname)
FROM users
WHERE user_id = created_by AND deleted_on IS NULL) As createdBy,
(SELECT CONCAT( firstname,' ', lastname)
FROM users
WHERE user_id = updated_by AND deleted_on IS NULL) As UpdatedBy
FROM users
WHERE user_id = '".$page_user_id."' AND deleted_on IS NULL AND client_id = '".$_SESSION['client_id']."'
-- USERS Table --
user_id INTEGER
client_id INTEGER
firstname VARCHAR(25)
lastname VARCHAR(30)
email VARCHAR(55)
password VARCHAR(40)
level TINYINT
deactivated_on DATETIME*
created_on DATETIME
created_by INTEGER
updated_on DATETIME*
updated_by INTEGER*
deleted_on DATETIME*
deleted_by INTEGER*
