php - How to join multiple tables related by other tables
Get the solution ↓↓↓I'm developing a site where people can publicate their houses for rent. I'm using php 5.2.0 and MySQL 5+
the publications are stored in a table like this
ta_publications
+---+
I have diferent publications, which have "features" such as "internet", "made service", "satellite tv", etc.
These features might change in the future, and I want to be able to add/remove/modify them, so I store them in the database in a table.
{-code-2}
which are related to the publications using the following table
{-code-3}
I think it's pretty easy to understand; There is a publication called name_001 which have internet, wi-fi and satellite tv.
I have the same data-schema for the images, I store them in this table
{-code-4}
And use the following table to relate them to the publications
{-code-5}
the column order gives the order in wich publications should be displayed when listing a single publication.
Philipp Reichart provided me with a query that will search and get all the publications that have certain features. It works for listing the publications, I can't modified it to return me the data I need.
So I figured I'll run that query and get all of the publications that pass the search criteria and then use another query to list them.
The listing of these publications shall include all publication data (everything on ta_publications)+ all of it's features + the most important (order 0) image src.
I could, for every publication, have two simple querys wich will return, separately, the most important image and all the features it has, but when listing 25 publications per page, it'll be 1 search query + (2 querys per publication * 25 publications) = 51 different querys, clearly not very efficient.
EDIT:
My question is, how can I create a SQL query that, given some publication ids, will return: all publication data (everything on ta_publications) + all of it's features + the most important (order 0) image src
Answer
Answer
Solution:
You'll get redundant publication and image data with this one, but here is a way to do it with one query:
SELECT p.id, p.name, p.date,
f.id, f.name,
i.id, i.src
FROM ta_publications p
JOIN ta_publication_features pf ON p.id = pf.publication_id
JOIN ta_features f ON f.id = pf.type_id
JOIN ta_publication_images pi ON p.id = pi.publication_id
AND pi.order = 0
JOIN ta_images i ON i.id = pi.img_id
WHERE p.id IN ( -- list of publication ids );
Answer
Solution:
Select pub.Name as [Publication], f.name as [Feature], i.name as [Image] from ta_publications pub
Join ta_publications_features pf on pf.publication_id = pub.id
Join ta_features f on f.id = pf.type_id
Join ta_publication_images pi on pi.publication_id = pub.id
Join ta_images i on i.id = pi.img_id
Where pub.id = 'appropriate id'
order by i.[order]
If I've followed your structure correctly this should give you the joins you need. and you can add desired columns to the result in the select statment by adding .. hope this helps.
Share solution ↓
Additional Information:
Link To Answer People are also looking for solutions of the problem: illegal string offset
Didn't find the answer?
Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.
Similar questions
Find the answer in similar questions on our website.
Write quick answer
Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.