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

Avatar of undefined
Last Comment

8/22/2022 - Mon

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?

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!
Your help has saved me hundreds of hours of internet surfing.

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question

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)
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.

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.