Help with joins for PHP application

[DeaconDesperado]

Hello all!

 

I'm new to the forum and this is my first post.  I've been working on learning PHP over the past two months, and between working on client projects I've made some progress in my understanding of PHP and MySQL.  I've built a few dynamic galleries and blogs, etc. and I'm just getting into database optimization using relational tables.  Forgive me if the inquiries that follow are newbie stuff... but I could really use some help grasping the theory!

 

My background is in OOP Actionscript for Flash and Javascript... I'm using PDO to connect to my database.

 

What I'm trying to build as an exercise is a kind of entomology (lepidoptery, all about moths)  site.  It's a simple app - the homepage will load a long list of thumbnails of different species from a database of profiles.  When clicked, that species profile will be loaded from the database.

 

My difficulty is with the database design and the concept of foreign keys and joins.  I am going to have one table that is just a directory, with fields for the id, name, description.  Then a foreign key on this database will link to a table that will hold all the paths for images of each species.

 

When I write my admin and listing scripts, how can I be sure that this foreign key auto increments and always matches the right entry in the directory table?  What I seem to be misunderstanding is if this procedure is handled by the JOIN query, or whether or not there is some configuration I need to make to the tables themselves in order to implement the one to many relationship.  For each species clicked, a different number of pictures will load, so it would make sense to have a table that merely has an id field linking back to the directory and a path field to the image.

 

I apologize if my wording has been vague.  Any help rendered would be much appreciated!

 

 

[gizmola]

Yes, this is pretty much a classic 1 -> Many relationship.  One "species" row can have related to it, Many "pictures".  When you make a 1-Many relation, the table on the "Many" side receives a "foreign key" (the primary key of a "foreign" table) in it.  Personally if I was going to build this in mysql, I would design the tables as such:

 

 

species

---------

species_id  int unsigned AUTO_INCREMENT primary key

name varchar(60)

description text

 

 

picture

---------

picture_id int unsigned AUTO_INCREMENT primary key

species_id int unsigned

path varchar(255)

 

 

 

One thing to understand about mysql is that using the standard MyISAM engine, you have no referential integrity between related tables.  So the database will not enforce constraints.  It also doesn't have COMMIT/ROLLBACK.  If you desire these things, you need to use a different engine.  Most people use the Innodb engine for those tables, if that's highly desired.

 

With that said, if you wanted to pull up all the information and pictures for a particular species --- (you'll get a row for every picture, and this also uses an inner join, so if no pictures exist, then you get no rows).

 

$sql = "SELECT name, description, path FROM species s, pictures p  WHERE p.species_id = s.species_id AND s.species_id = $species_id";

 

This is just meant as an example, and assumes that you would have gotten the $species_id, possibly from a get param.  Omittting that AND, you'd get a full result set of every picture. 

 

If you want at least one row you'd need to change this to use an OUTER join, but I'll leave that to you to investigate.

 

 

[DeaconDesperado]

Thanks so much for the clarification!  I was hoping the answer would be something along those lines, as that's the conclusion I was slowly arriving at.

 

So when I write my admin script to insert the profiles and images, how can I be certain that every entry into the images database has the correct key referencing back to the species table?  Should I just call a query to find what the new species_id will be when submitted and pass that to become the entries for the images as they are submitted?  How do you automate such a thing?  Is there a specific query that finds what the new key will be in on table at the moment of insertion and uses it as a value in another?

 

Thanks!

[gizmola]

If you are inserting a species AND a picture at the same time, then it's a 2 step process, nothing magical about it:

 

-insert the species row first

-- Use the appropriate mysql function/method to get the ID (last_insert_id() or ->insert_id()

-insert into picture, specifying the value you got from the insert_id above as the foreign key.

 

 

This is one place where mysql works a bit differently compared to a lot of other relational db's.  For example oracle has a seperate sequence object that basically gives you unique id's that you can get in advance of a query.  Mysql, you have to accomplish the insert, then see what it gave you.  This actually works fine, because each mysql connection has it's own thread and queries are serialized on the connection, so there's always one "last id", even if you reusue the connection for a variety of queries.  You can of course seperate this more fully in your code by making multple connections to the database. What a lot of people like to do, is have one connection for selects and one connection for data maniuplation (insert, update, delete).  You can even add some db security in this way by having totally seperate connections for each, with the select connection using a user that only has SELECT privileges on the table.

 

Well-- just musing at this point.  Hopefully you got the basic answer in the first part of the comment.

[DeaconDesperado]

Thanks again for the help.

 

I'm building my admin section currently.  I have the listing and insert scripts complete and am working on the update page.

 

I need to think through the logic of how I want to update the images.  Right now, I use GD to resize the images that are submitted for thumbnails, and the images in the database whose THUMB field is set to true (a TINYINT field using 0 and 1) are the images that are used in the nav listing on the homepage.

 

What do you think would be the most intuitive way to have the user submit images?  Whats the best method to make an update page that will use radio buttons to determine which image whose ID matches the one selected for editing is used as the profile thumbnail?  What kind of query would I do to check the radio box for the one currently selected as the thumb?  And is there some way to make sure in the SQL database the when a different thumb is selected via the edit pages the other images of that ID's thumb value set to false?  Am I approaching this theory correctly?

 

Thanks for all the help.  I hope you don't feel I'm being greedy with the questions.

 

If you'd like to view the product so far it's at http://www.newenglandmoths.com/

[gizmola]

No problem.  I have had to solve similar problems, so the question of usability is an interesting one.  Probably the most intuitive way is to have an images screen that is associated in the admin system with a particular moth.  From this screen you should be able to:

 

-Add new image

-Replace existing image

-Delete image

-Check box image as thumb

-Order images

 

For uploading images, you might look for some help in terms of pre-built upload class(es).  If you roll your own you'll probably find there's quite a lot of details involved -- not a bad exercise, but expect to learn as you go.  Read the php manual carefully about the $_FILES and move_uploaded_file() at minimum.  One thing I found useful was to to do some file renaming.  Basically I took the sequence of the picture row being created, and changed the image when it gets stored on the server to be seq_name.ext.  I also did things like lowercasing the name and removing spaces etc.  When I generated thumbnails, I could then name them seq_name_tn.ext. 

 

One last thing to be aware of, is that if you expect to have a really large number of these images, on a unix filesystem, things will slow down significantly if you reach a certain threshold of files in one directory.  So a great way of breaking these up is by setting up a subdirectory structure in advance, named 0 - 9.  So your dir structure could be:

 

/images/0

          /1

          /2 ...

 

Then you simply use the last digit of the sequence # to determine in which directory you should store the file. 

 

One other gotcha is that you may need to adjust things in the apache configuration regarding maximum post size, and the php.ini to allow uploads and to modify the max uploaded file size.  This pretty much depends on the size of your source image files.

 

 

Primarily however, it's simply built on an input form element of type "file", and the form "enctype" needs to be "multipart/form-data".  The rest of the magic takes place inside your serverside code.  Since you're dealing with images, you might find that the exif extension is useful for verifying the contents and size of images.