relations via PHP or MySQL

I am curious as to what everybodies thoughts on the following problem are.

I have two tables in a database. One of them holds information about the site's members. The other table holds several pictures with associated captions in another table. The table structure is similar to this:

member_table ------
PK_ID -(primary key)
memberName
memberEmail
(other personal info)

image_table -----
PK_ID -(primary key)
memberID (foreign key linking to the member table)
image
caption

Now using data on that table I need to generate a dynamic page listing all site members and showing thumbnails of each member's image. I can think of two obvious ways to do this.

+ 1st way
Query the database for all of the members. Then loop through each member and query the database for each member's images.

+ 2nd way
Query the database for all of the members and in that query do a JOIN on the image table so the query contains both members and their images. Then loop through the results and extract all the member information into an object or an array also add all of the image data to the object/array as member array.

The difference between these two methods is basically do you put the load of building the member object (complete with images) on the SQL server (1st way) or on the php engine (2nd way). The first way results in far cleaner code in my experience, however it scales horribly. I was hoping that you all could give me some good suggestions that I haven't thought of yet..

I'd do the second one. I don't know how you figure the first makes for cleaner code, as it involves a couple of foreach loops. For the second, all you do is the one loop, and you make your query longer by 15 or 20 characters, plus it scales.

And for that matter, if you've got a one-to-one relationship here, why do you even have the image table broken out? If each member has exactly one image and one caption, and those images and captions can't overlap with those of other members, then I'd think you'd just put the image and caption in the member table.

If what you intend is for there to be a menu of images for members to choose from, and multiple members can have the same image, then you should have the images table broken out, but the foreign key should be in the members table and should point to the primary key in the images table corresponding to the chosen image. In this case, there should be no member_id in the images table.

There is actually a 1 to many relationship.

I have tended to go the second solution myself most of the time as well. The problem I run into (more of an annoyance) is this.

Due to the one to many nature of the relationship between the members and the images a query with a join results in the following



The problem being you can't really do a simple foreach() or while() all the time with this because of the duplicated member data (name and e-mail). At this point what I usually do is loop through all of the record and as soon as I see the primary key for the member table change I save the specific member data into an array and then until it changes it again I only save the image data to another child array. An example (pseudo-code) is:



Now this example it's not too bad...but now imagine a time when you need fields from four or five related tables and each of the relations is a 1-to-many relationship. This method could become very difficult. I am sure there is another, better way to do this. Which is where I need help.

Just to re-iterate however, the table relationship in my example is a one to many table where one member may have several images. It is not a many-to-many relationship. However I think the same issues would still apply to a many-to-many relationship.