I'm building an API for my site that allows users to delete the files they upload. Obviously, I want to check if the user owns that file before they delete it through the API.
I have a files table and a users table, here's the schema:
f_id, s_id, u_id, name, size, uploaded
u_id, username, password, email, activated, activation_code
u_id is a foreign key. The u_id field in the files table points to the u_id in the users table.
Given the users username, I want to find the users u_id, and then check if they own the file through the file ID (f_id).
I wrote this SQL:
$sql = 'SELECT u.username
FROM `users` u
JOIN `files` f
ON u.u_id = f.u_id
WHERE f_id = ? AND u.u_id = ?
LIMIT 1';
I'm assuming that'd work if I was given the users u_id in the API request, but alas I'm given only their username.
How can I modify that SQL to find their user ID and use that?
Thanks.
Edit: Alright I've got this query but it's always returning an empty result set even though both the file ID and username exist.
SELECT u.username
FROM `users` u
JOIN `files` f
ON u.u_id = f.u_id
WHERE f.f_id = ? AND u.username = ?
LIMIT 1
