Grabbing Relational Data

Databases
1

I’ve been trying to understand how to achieve this in mysql with a query. Ultimately the output will be used by PHP.

My Tables:
[LOG]
±---------±-------------±-----±----±--------±---------------+
| Field | Type | Null | Key | Default | Extra |
±---------±-------------±-----±----±--------±---------------+
| id | int(11) | | PRI | NULL | auto_increment |
| user_id | int(11) | YES | | NULL | |
| asset_id | int(11) | YES | | NULL | |
| datetime | datetime | YES | | NULL | |
| ip | varchar(255) | YES | | NULL | |
±---------±-------------±-----±----±--------±---------------+

[USERS]
±------------±-------------±-----±----±--------±---------------+
| Field | Type | Null | Key | Default | Extra |
±------------±-------------±-----±----±--------±---------------+
| id | int(11) | | PRI | NULL | auto_increment |
| username | varchar(255) | YES | | NULL | |
| email | varchar(255) | YES | | NULL | |
| password | varchar(255) | YES | | NULL | |
| last_login | datetime | YES | | NULL | |
| admin | tinyint(4) | YES | | 0 | |
| outlet_name | varchar(255) | YES | | NULL | |
| contact | varchar(255) | YES | | NULL | |
±------------±-------------±-----±----±--------±---------------+

[ASSETS]
±----------------±-------------±-----±----±--------±---------------+
| Field | Type | Null | Key | Default | Extra |
±----------------±-------------±-----±----±--------±---------------+
| id | int(11) | | PRI | NULL | auto_increment |
| avam_category | int(11) | YES | | NULL | |
| asset_name | varchar(255) | YES | | NULL | |
| avam_asset_type | int(11) | YES | | NULL | |
| amazon_file | varchar(255) | YES | | NULL | |
| asset_added | datetime | YES | | NULL | |
| asset_thumbnail | varchar(255) | YES | | NULL | |
±----------------±-------------±-----±----±--------±---------------+

My “Log” table holds data about which assets users downloaded. I only keep user_id and asset_id in this log because info about the User and Asset may change (ie name).

When I output the activity log I’d like to include users.email and assets.asset_name in my query to make my log output human readable.

What is this called? Any help is appreciated.

2

I think you are trying to use JOINS, example:

PS: I don’t know why the word “log” capitalized, it might be a reserved word.

SELECT
	log.*,
	users.email,
	assets.asset_name,
	
FROM log 

INNER JOIN users
	ON (
		log.user_id = users.id
	)
	
INNER JOIN assets
	ON (
		log.asset_id = assets.id
	)

If assets is empty, it won’t show anything but if you wanted
the username/log to appear even if it’s empty you might do:


SELECT
	log.*,
	users.email,
	assets.asset_name,
	
FROM log 

INNER JOIN users
	ON (
		log.user_id = users.id
	)
	
LEFT OUTER JOIN assets
	ON (
		log.asset_id = assets.id
	)