I am a high school student learning PHP and MySQL. I am trying to create a simple photo sharing site that allows a user to sign up and start uploading images. Each user has a profile that shows all of their pictures.
I currently only have one table, called "users": username, email, password
How should I set up a table for the pictures? And how do I link to each picture's filepath?
Thanks
The first question is where are you storing the user's photos? I would recommend this:
Table (User's):
Username | Password | Email
---------------------------
user1 | fdgfdg | fdfgdf
user2 | ffdgfd | fgdfgf
user3 | dgfdgg | fgdgdd
Then, whenever a user signs up, create a folder with that user's name and a subdirectory titled photos.
Create a Table for Each User.
Table user1
Photo | Title | info
----------------------------
duck.jpg | My Duck! | fgdf
cat.png | Le Cat | dfgd
Then, when a user logs in, list all the Titles and link them to the photo dynamically.
So I would be
My Duck --> http://mysite.com/user1/photos/myduck.jpg
Where user1 would be the logged in username, the photos would automatically be added and myduck.jpg would be pulled from the database.
However, this opens up two holes: - Hotlinking - If someone knows the username and photo title, they can access the photo without being given permission.
On your user table create a column called Id (this should never change) You normally use something like an INT AUTOINCREMENT number for this so the database allocates it automatically, and this should be marked as the PRIMARY KEY
Create a table called photos
Table photos
Id = autoincrement primary key
UserID = int (you can set this up as a foreign key, but to keep it simple, just make it indexed)
Title = varchar
Info = varchar
Filename = varchar
Table Photos
Id | UserId | Title | info | Filename | (add other columns to suit)
------------------------------------------------------------
1 | 1 | Duck | xyz | duck.jpg |
2 | 1 | Cat | xyz | cat.jpg |
3 | 2 | Mouse | xyz | mouse.jpg |
As you can see from the data above Photo 1 and 2 belong to user 1 and photo 3 belongs to user 2
To select all the photos for username 'bob' you would do something like this
SELECT Photos.Id, Photos.Filename
FROM Photos
JOIN Users ON Photos.UserId=Users.Id
WHERE Users.username = 'bob'