CODEWITHSUNDEEP
phpmysqldatabase
user1576827
user1576827

Reputation: 1

MySQL SELECT Statement: Multiple items and Owner's Information

So here is the exemple: I have a database with "artists" and "paintings". I need to create a page with the information of the artist (I get the artist id from the URL) and also every paintings associated with that artist, I have both table linked.

Is there a way to do that request with only 1 statement?

I tried this:

SELECT * 
FROM artiste, toile 
WHERE artiste_id = {$id} AND 
      fk_artiste_id = artiste_id

It works, but since I'm using a while to fetch the data, I get 3 times the artist's information (since there is 3 paintings associated with that artist), and if I do a GROUP BY artiste_id, I only get 1 artist and it's first painting, not all of them.

Thanks for your time! (Excuse my poor english).

Upvotes: 0

Views: 144

Answers (3)

ApplePie
ApplePie

Reputation: 8942

If you need only information about the painting you can make a query for information on that table only.

SELECT 
    toile.* 
FROM 
    artiste, toile 
WHERE 
    artiste.artiste_id = {$id} AND 
    toile.fk_artiste_id = artiste.artiste_id

I'm sorry if this is not perfectly MySQL compatible, I've been working with MS SQL mostly lately.

Edit: Another way would be to use an INNER JOIN. Again, if someone can correct if I'm wrong on the exact syntax, here's what it should look like:

SELECT 
    toile.* 
FROM 
    toile INNER JOIN artiste ON artiste.artiste_id = toile.fk_artiste_id 
WHERE
    artiste.artiste_id = {$id}

Both parts need to be in separate queries. You need one query per set of data you want to retrieve or per question you are asking, if you prefer. If the question is "What is the data I possess about artist X ?" then you shall make a query answering just that. If your question is "What images do I possess that are paintings of artist X ?" then this is another question that should be dealt with another query.

http://sqlfiddle.com/#!3/47ff0/2

Upvotes: 2

Hans
Hans

Reputation: 2320

Just do two queries.

With SQL, information comes back in a tabular format, so you won't be able to get just one copy of the artist and all of their paintings in a single query.

Also, I noticed your use of {$id}, and the tags say PHP so I must recommend prepared statements to help your application's security and maintainability. Don't mess around with SQL Injection vulnerabilities (and bugs).

Getting lots of copies of the artist might actually be faster, but that's purely a performance optimization. If you're worried about performance, my best advice is to stop worrying about performance.

Upvotes: 1

Pevara
Pevara

Reputation: 14310

The result you are getting is actualy quiet normal. You are actually fetching all paintings from a given artist, and adding the information of the artist to each painting. You could consider doing two queries, one for the artist info and one for the paintings. Or you could keep your query and make sure you output the artist info before your loop, and then output the painting info inside the loop and just skip the artist info that was appended to it.

Upvotes: 4

Related Questions