Link to home
Start Free TrialLog in
Avatar of BrighteyesDesign
BrighteyesDesignFlag for Afghanistan

asked on

How to show images from a relational database table

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
Avatar of johanntagle
johanntagle
Flag of Philippines image

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?
Avatar of BrighteyesDesign

ASKER

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!
Sorry Johan! (not john) I think I got your name wrong last time too!
ASKER CERTIFIED SOLUTION
Avatar of johanntagle
johanntagle
Flag of Philippines image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
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?
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)
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!)
You're welcome, and thank you for being willing to learn, instead of just insisting on getting the solution.