How do I Use data from 2 tables

[jaisol]

I'm designing a photo gallery, with about 100 pictures per page.

 

I want to store all image file names in a table called images (1 colum - file_name)

I also want to store user details to show if they have deleted an image and not show that image when they redraw the page.

I will call this table User_Deleted (2 colum - user_id, file_name)

I'm thinking of doing something like

 

$user_id = the current user

Select * from user_deleted where user_id = $user_id

retrive the file names and store into an array

 

run a loop to add " AND file_name != next file name in the array

and then parse it onto the end of a Select command

 

Select * from images WHERE file_name != 1st deleted file AND  file_name != 2nd deleted file AND .. however many files have been marked   LIMIT 100

I'm sure there must be an easier way of doing it, but I'm not smart enough to see it LOL

 

I think something like

 

Select * from images but not if they are in Select * from user_deleted where user_id = $user_id

would be a better way but I can't figure out how you write that sort of statement.

 

[kickstart]

Hi

 

You can do it in a single SELECT statement (something like as follows).

 

SELECT *
FROM images
LEFT OUTER JOIN user_deleted
ON images.file_name = user_deleted.file_name
WHERE user_deleted.file_name IS NULL

 

Note, it would probably be better to have 2 columns in the images table, one being an auto incremented id and the other the file name. And refer to the id field in the user_deleted table. A join on simple numeric fields like this should be quite a bit more efficient (both in speed and also in how much storage is used once you factor in all the possible rows on user_deleted).

 

All the best

 

Keith

[mikosiko]

In your case I will design your table images with 3 columns  image_id, filename, status (null=active or 1=deleted)... that will make the queries simple

[jaisol]

Many thanks

 

1st reply works for me. As I want to hide the image from a user that choses to 'delete' and not hide the image from every user.

 

Thanks again

 

 

[mikosiko]

gotcha... I was thinking in a per-user photo gallery... I see now that you are implementing a general photo gallery independent of the user.. the 1st solution is adjusted to that scenario.

 

+1 with the 2 columns Keith's suggestion for the images table