Please help...photo album

[JPBaseball1322]

Hi all, I'm new here and looking for myself. I have a project to design a website that acts as a photo album using php and mysql. I'm struggling on how to get started. Essentially I have determined that I have 3 things to accomplish to get started.

1) Determine my database schema--- So I believe I need to create 3 tables here. One will be an albums table containing the album ID, title, date created and date modified. My second will be a photos table containing the photo ID, title, a caption and the album it belongs to. The third I believe needs to be something to relate the two tables. This I'm not sure how to do. Any suggestions.

2) Create tables and upload some pictures- This I think I can do once I figure out the full schema

3) Write code to display pictures that are stored in the database on my website.


I'm not looking for someone to do this work for me. I'm just looking for some suggestions so that I can do my best to finish this and LEARN what I have done. Any help would be greatly appreciated. Thank you so much for your time and effort.

[Alan]

1) While you could go further and create more tables etc. it's best to keep it simple to start with. You will need 3 tables (as you said). One is for albums, another for photos and another is called a "junction table" (or an "associate entity", depending on your perspective). A junction table is only needed if a photo can exist in one or more albums. The junction table is needed for many-to-many relationships. So if many photos reside in many albums, you will need it. If a photo can only exist in one album at any time, then you only need to define a one-to-many relationship.

2) Once the schema's have been defined, you can issue insert statements to populate the tables.

3) Not a problem really. All you have to do is open a connection, send a query to the database which will fetch the data according to your query, return the data to PHP and then loop through the result set outputting the data.

e.g. (I know you said you didn't want code but an example never hurt anyone. :))


<?php
$db = mysqli_connect(DB_HOST, DB_USER, DB_PASS, DB_NAME, DB_PORT);

if(!$db)
{
die('Database connection error');
}

$sql = "SELECT *
FROM albums
ORDER BY title
LIMIT 10";

$resultSet = mysqli_query($db, $sql);

if(mysqli_num_rows($resultSet) > 0)
{
while($row = mysqli_fetch_assoc($resultSet))
{
echo $row['ID'] . $row['title'] . "<br/>\n";
}
}

mysqli_close($db);
exit();
?>