How to show images from a relational database table

BrighteyesDesign
BrighteyesDesign used Ask the Experts™
on
As part of a property site I had all info held in one table called 'properties'. However I have just changed the  make up.

Before there were 10 columns in the properties table: image1, image2 etc...I thought having a specific table for images was the way to go so the image filenames are now in a separate relational table called 'images'

I'm not sure though how to display the images. Before, for the thumbnail results page I would use...

SELECT propertyID, pricePrefix, additionalimage1, country_code, image1, name, FORMAT(prices,2) as prices, `currency` FROM properties WHERE cityCode = '456' ORDER by RAND ()"

Open in new window


How do I make this query display an image/filename from the images table if the 'propertyid' value matches the 'PropertyID' from the 'properties' table
Screen-Shot-2012-05-25-at-13.57..png
Screen-Shot-2012-05-25-at-14.02..png
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2012

Commented:
Still not yet getting the hang of doing SQL joins?  The solution here is very similar to those of your earlier questions.  If you don't mind right now I'm more inclined to guide you to the right solution rather than just giving you the answer.  First a clarification - do you want to display just one or two images (in your original SQL you seem to be getting two images) or do you want all images?  If the former, how do you want to choose among those in the images table, in case there are more than one or two, just random?

Author

Commented:
Hi John, it is just one image. Never thought about which one should be chose. I'll probably add a column called 'selected'. The user can then choose image they want as the thumb and send a 'YES' value to the database? does that sound ok?

I just havn't had to use them since so i'm still a bit ropey on them. I will have a look over those previous questions though!

Author

Commented:
Sorry Johan! (not john) I think I got your name wrong last time too!
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Top Expert 2012
Commented:
Yes that sounds okay, but to move forward let's forget about that first.  Take a look at this: http://www.w3schools.com/sql/sql_join_inner.asp

So if you want to list all properties along with the filenames of images associated with it (desired output below), what will your SQL be?

property_name | filename
propert1             | img1.png
propert1             | img2.png
propert2             | img3.png
propert2             | img4.png
propert2             | img5.png
(and so on)

Once you have that you only need to add the other columns you need and an appropriate WHERE clause, including filtering only for the selected image.
Top Expert 2012

Commented:
By the way would it be possible for a property to not have even just one corresponding entry in images?  If so take a look at http://www.w3schools.com/sql/sql_join_left.asp

Author

Commented:
Great, thanks for that link, very clear. My image database is set up exactly as you mention so following that link was very easy.

I have got that working! Just one thing, I can't get the price format working?

"SELECT properties.propertyID, properties.pricePrefix, properties.country_code, properties.mainimage, properties.name, FORMAT(prices,0) as properties.prices, properties.currency FROM properties INNER JOIN images ON properties.propertyID=images.propertyid WHERE country_code<> 'GBR' ORDER by RAND ()"

Open in new window


I have tried...

FORMAT(prices,0) as properties.prices

FORMAT(properties.prices,0) as properties.prices

FORMAT(properties.prices,0) as prices

All with no joy?
Top Expert 2012

Commented:
Hmmm "FORMAT(properties.prices,0) as prices" should work.  Can you post the full table definition of properties?

BTW, if you are going to use screenshots, please choose the "embed" option so we can easily see them in your post instead of having to download them (my "Downloads" folder is full of your screenshots, I need to clean them up haha)

Author

Commented:
It does work, must have done something wrong before.

Thanks so much for your help, I think the penny has dropped this time so there shouldn't be any more SQL join questions from me!

(and yes, i'll embed my pix in the future!)
Top Expert 2012

Commented:
You're welcome, and thank you for being willing to learn, instead of just insisting on getting the solution.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial