Page 1 of 1
theory behind linking multiple images to articles
Posted: Tue Jan 08, 2008 11:01 am
by Justinio
Hi hopefully you can help, I have fried my brain.
I am creating a website for an estate agent and cant get my head around how to deal with the image galleries. I have built everything else and can link a single image to each property but cannot link multiple images to each property on the more details pages.
The main page lists all properties and shows one image and a piece of intro text for each property with a more info button for each property.
The more info button takes you to a Detail page for that property and has a gallery of multiple pictures specific to that property.
- - - - - - - - -
What I cant work out is the logic and how to structure the admin pages for the user to upload the images, and how to make them specific too each property.
In sequence for adding a new property, (in theory) do I:
Stage 1
1) Input a new Category for the images for that property (say House number 1=first category)
2) Submit that category.
Stage 2
3) Upload each image and assign it to that category.
4) Submit the information.
Stage 3
5) Then complete the rest of the property information on another form and select the primary Image for that property.
How does that sound. . . ?
Thanks if you can help
Justin
[/syntax]
Posted: Tue Jan 08, 2008 2:46 pm
by superdezign
Yeah, you're thinking right. So.. what's the problem?
When I create administrative pages, I basically make it a lot more tightly fit to the database than the typical user interface, so the administrators are actually technically dealing with the database tables themselves. The only thing I don't make them (including myself) deal with is the relationship tables, which it sounds like is the point where you're getting stuck.
It sounds like you currently have a one-to-one relationship where each image has one category and each category has one image. However, it sounds like you're after a one-to-many relationship where one image only has one category, but one category can have multiple images, correct?
This can still be done with only two tables. The category table doesn't need to have any reference to the images table at all. The images table, however, must have a category set for each image. From there, when displaying categories, you'd get all of the images for that category from the images table.
Let me know if you follow so I don't end up confusing you with too much information.

Posted: Tue Jan 08, 2008 8:37 pm
by alex.barylski
Two ways of accomplishing this task as I can see:
1) Each listing will have a unique MLS ID (or PKID). Each listing could have a directory under 'images' such as:
images/KF11726/
Under this directory you would upload the images which are associated with that listing. Then when you build the listing page you would glob the image files and generate a tabular display.
2) Each listing would require an array of associated images, stored in either a database table or as files as well. Each time you built the listings page you would look up these ID's and find the matching image files.
Personally I'd opt for the former approach. Why? Because listing images are likely to be popular items to change and uploading different images using web forms is annoying. Keeping each image inside a unique directory which maps to the MLS ID would make it easier for your client to upload images via FTP or similar and avoid the requirement of an image uploader - better for both you and your client. Unless you need exacting control over images, such as an image wizard to assist in cropping, etc I always go with this approach. Permissions on a shared host is going to be the biggest PITA with this approach.
Posted: Wed Jan 09, 2008 3:23 am
by Justinio
Hi Superdezign and thanks for your help I appreciate it, I may need some elaborating, what you have said the theory is right in my head but I realize my problem now is translating it into tables.
I have translated it into tables below to see if I understand the conversion correctly:
Category table – Step01 User adds a category to the category table and submits.
--------------------------------
| cat_id | category
--------------------------------
| 1 | house01
| 2 | house02
| 3 | house03
| 4 | house04
| 5 | house05
--------------------------------
Image table – Step02 User uploads each image and assigns it to a category.
----------------------------------------------------------------------------
| Image_id | cat_id | filename | caption
----------------------------------------------------------------------------
| 1 | 1 | house01.jpg | 2 bedroom flat
| 2 | 1 | house02.jpg | lounge area
| 3 | 1 | house03.jpg | garden
| 4 | 2 | house04.jpg | 4 bedroom house
| 5 | 2 | house05.jpg | lounge area
----------------------------------------------------------------------------
That’s how it translates in my head, does that seem right?
My current solution is a one-to-one relationship like you guessed between my main table and my image table, and the user selects an image to link to as the main image see below:
Main Property table
-----------------------------------------------------------------------------/
| Article_id | Image_id | price | type
-----------------------------------------------------------------------------\
| 1 | 1 | 400,000 | Flat
| 2 | 3 | 350,000 | Semi-Detached
| 3 | 2 | 250,000 | Flat
-----------------------------------------------------------------------------/
Image table
----------------------------------------------------------------------------
| Image_id | filename | caption
----------------------------------------------------------------------------
| 1 | house01.jpg | 2 bedroom flat
| 2 | house02.jpg | 1 bedroom flat
| 3 | house03.jpg | 3 bedroom Semi-Detached
----------------------------------------------------------------------------
Thanks
Justin
Posted: Wed Jan 09, 2008 4:33 am
by Kieran Huggins
Take a look at how ORMs do
has_many and
belongs_to associations between models:
Code: Select all
houses
----------------------------
id (int)
(columns about the house)
images
----------------------------
id (int)
house_id (int)
(columns about the image)
Then you can select all the images for a house like so:
Code: Select all
SELECT * FROM `images` WHERE `id`=`house_id`
Is that what you're looking for?
Posted: Wed Jan 09, 2008 7:01 am
by superdezign
Justinio wrote:That’s how it translates in my head, does that seem right?
That is precisely what I described. Good job. ^_^
Then, you'd use the query that
Kieran provided, except... minus the typo
Code: Select all
select * from `images` where `house_id` = ?
Of course, replacing the question mark with a value.
Posted: Wed Jan 09, 2008 10:15 am
by Justinio
Okay, I’m glad this is getting somewhere now, thanks guys I am nearly there. Sorry for this being long winded, bit out of my depth
When you say ‘house_id’ in the SQL do you mean the id of the house? Or the cat_id? I’m a little confused, but probably because of what I have built before.
At the moment my main id for each house is in the ‘property’ table called ‘article_id’ I am referencing that id to pull all the info in on each page.
I have listed my code and at the bottom, all the tables are listed below.
I may be missing a trick and my existing structure may be complicating matters. . .
--------------------------------------------------------------------------------------------------------------------
On my page that lists all houses I am using:
SELECT * FROM ‘property’ LEFT JOIN ‘images’ USING (image_id) ORDER BY title DESC
The Join is because I am linking to the images table to select the primary house image to be displayed.
-------------------------------------------------------------------------------------------------------
On my page that shows the house details (after clicking more info) I am using:
SELECT price, type, article, filename, caption
FROM ‘property’ LEFT JOIN ‘images’ USING (image_id)
WHERE property.article_id = $article_id";
This page is where I need to show all the other images in the category so I guess I need to add cat_id in some how?
-------------------------------------------------------------------------------------------------------
This is my process for creating a new house/property entry and my tables are shown too.
STEP01 (FORM 01) - User adds a category to the category table and submits.
Table name=Category
------------------------------
| cat_id | category
------------------------------
| 1 | house01
| 2 | house02
| 3 | house03
| 4 | house04
| 5 | house05
------------------------------
STEP02 – (FORM 02) User uploads each image and assigns it to a category.
Table name=Images
---------------------------------------------------------------------------
| Image_id | cat_id | filename | caption
---------------------------------------------------------------------------
| 1 | 1 | house01.jpg | 2 bedroom flat
| 2 | 1 | house02.jpg | lounge area
| 3 | 1 | house03.jpg | garden
| 4 | 2 | house04.jpg | 4 bedroom house
| 5 | 2 | house05.jpg | lounge area
---------------------------------------------------------------------------
STEP03 – (FORM 03) User adds all other house information into main table
This table and form was how I have been linking the primary image to each property. By having a drop down box populated with all images from the image database. Then selecting one, which adds the value into the image_id field in this table below.
Table name=property
-----------------------------------------------------------------------------/
| article_id | Image_id | price | type
-----------------------------------------------------------------------------\
| 1 | 1 | 400,000 | Flat
| 2 | 3 | 350,000 | Semi-Detached
| 3 | 2 | 250,000 | Flat
-----------------------------------------------------------------------------/
Posted: Wed Jan 09, 2008 12:13 pm
by superdezign
Justinio wrote:When you say ‘house_id’ in the SQL do you mean the id of the house? Or the cat_id? I’m a little confused, but probably because of what I have built before.
Yeah. I used house_id to be consistent with
Kieran's suggestion. Keep in mind though that we don't do the work for you, we just lead you in the right direction, so you usually shouldn't take code literally.
As for the rest of your post, I feel like you're giving us too much information.. Be more specific with where the problem actually is.
In your query, you should join the image table with the category table using the category id, or get the images for each category in a separate query. If you want to get all of the images by category, look at this:
Code: Select all
select from `images` left join `category` on `category`.`cat_id` = `images`.`cat_id` order by `images`.`cat_id`
Posted: Wed Jan 09, 2008 12:40 pm
by Justinio
Thankyou superdezign, I appreciate your help and yes didnt mean to come across as wanting the work done i kinda took it a bit too literally.
I Kinda dumped everything in as i was a little confused. I am glad my theory is right with your help.
I will build on your suggestion and hopefully should be there, I will let you know!
Appreciate the help
Justin