Table A (users)
id | name | ip 52433 Yashas 22.214.171.124 512 Admin 126.96.36.199
Table B (admin_record)
id | admin | time | date | reason | action 52433 512 2312352 1213213 Test WARN
id is FOREIGN key
Table B keeps a log of all actions an administrator takes on his users. And the table A stores the account information of the users & administrators.
I am trying to display a list of actions taken by a administrators on a user.
I want to write a query which gets me all the rows of table B where id = fixed number.
Here is what I want to display:
"Administrator1 [Name]" took action on "User Name" for "[Note]" on XX/XX/XXXX. "Administrator2 [Name]" took action on "User Name" for "[Note]" on XX/XX/XXXX. "Administrator3 [Name]" took action on "User Name" for "[Note]" on XX/XX/XXXX.
All I have is the user's id using which I need to obtain all the entries in the log along with the administrator names. I need to obtain the admin's name, users name and all the contents of table B related to a particular user in one single query. If possible, I want the to change the field name(not literally change, but want the SQL server to send the data with new field names to separate admin name & user name) of the admin's name to admin_name and user's name as . I mean something like this
SELECT users.name AS admin_name WHERE users.id = %i AND SELECT users.name AS user_name WHERE users.id = %i
(the query isn't correct,it us just an example to show what I am trying)
Progress so far:
SELECT player.name, player.ip, admin.name, record.date, record.time, record.action, record.reason FROM admin_record AS record JOIN users AS player ON player.id = record.id JOIN users AS admin ON admin.id = record.admin WHERE player.id=%i
where admin_record is table B & users is table A.