SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Enthusiast
    Join Date
    Feb 2005
    Location
    Glasgow, Scotland
    Posts
    97
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Need help with this MySQL query

    Database structure:
    Code:
    distro_item
    itemid, album, format, label, category, etc.
    
    distro_artist_album
    itemid, artistid
    
    distro_artist
    artistid, artist
    Query to get all the artists for each item:
    Code:
    SELECT i.itemid, a.artist, i.album, i.format FROM distro_items AS i
    	INNER JOIN distro_items_extra AS ie ON i.itemid = ie.itemid
    	INNER JOIN distro_artist_album AS aa ON aa.itemid = i.itemid
    	INNER JOIN distro_artist AS a ON a.artistid = aa.artistid
    	ORDER BY i.itemid DESC
    This gives an output (sample for two items, one with 3 artists)
    Code:
           ITEMID        ARTIST       ALBUM        FORMAT
               2        Artist2       Title2        CD
               1        Artist1       Title1        7"
               1        Artist2       Title1        7"
               1        Artist3       Title1        7"
    I want an output of:
    Code:
     <a href=script.php?itemid=$itemid>Artist1 | Artist2 | Artist3 - Title - 7"</a>
    Is there a way to change my query to give an easier way of accomplishing this? Or is there a standard way to process that result to get what I want using PHP?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    try the GROUP_CONCAT function in mysql 4.1
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Feb 2005
    Location
    Glasgow, Scotland
    Posts
    97
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    How would I implement it though? Someone else told me to look at that, but I couldn't figure it out from the developer section on the mysql site.

  4. #4
    Mlle. Ledoyen silver trophy seanf's Avatar
    Join Date
    Jan 2001
    Location
    UK
    Posts
    7,168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Moved to MySQL forum

    Sean
    Harry Potter

    -- You lived inside my world so softly
    -- Protected only by the kindness of your nature

  5. #5
    SitePoint Enthusiast
    Join Date
    Feb 2005
    Location
    Glasgow, Scotland
    Posts
    97
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oops, was sure I posted it here. Sorry.

    I just checked my servers installation and it's not 4.1. I'm developing a general purpose script, so I'd rather not use features from versions not globally adopted yet. I guess I'll just have to convert it using PHP.

  6. #6
    SitePoint Enthusiast Mickj's Avatar
    Join Date
    Jun 2002
    Location
    Margate, Kent, UK
    Posts
    52
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Teeej
    Database structure:
    Code:
    distro_item
    itemid, album, format, label, category, etc.
     
    distro_artist_album
    itemid, artistid
     
    distro_artist
    artistid, artist
    Query to get all the artists for each item:
    Code:
    SELECT i.itemid, a.artist, i.album, i.format FROM distro_items AS i
    	INNER JOIN distro_items_extra AS ie ON i.itemid = ie.itemid
    	INNER JOIN distro_artist_album AS aa ON aa.itemid = i.itemid
    	INNER JOIN distro_artist AS a ON a.artistid = aa.artistid
    	ORDER BY i.itemid DESC
    This gives an output (sample for two items, one with 3 artists)
    Code:
    ITEMID ARTIST ALBUM FORMAT
    2 Artist2 Title2 CD
    1 Artist1 Title1 7"
    1 Artist2 Title1 7"
    1 Artist3 Title1 7"
    I want an output of:
    Code:
    <a href=script.php?itemid=$itemid>Artist1 | Artist2 | Artist3 - Title - 7"</a>
    Is there a way to change my query to give an easier way of accomplishing this? Or is there a standard way to process that result to get what I want using PHP?
    The way I would do that and maybe the only way I know of is to create a joined table between your distro_artist_album and distro_artist for the album you want a list of artists for i.e. combination Artists like Britney Spears Feat. Madonna using the itemid then when the loop completes you will have a list of artists that relate to an album so another query to get the album would do it.

    or you could include the album details in the first query loop and at the first record grab the album details and store them away for when the loop completes similer to what you have above

    but not sure if you want a single album output or a list of albums each with it's related artists?
    Be Proud, Be Loud, Be Heard.
    Systems designed by me
    Battle Of The Bands
    http://www.database-dreams.co.uk/Com...p?act=artchart
    Worldwide Music Charts
    http://www.database-dreams.co.uk/Com...ex.php?act=RCH
    UK Charts history (Started 9 Setember 01)
    http://www.database-dreams.co.uk/Com...php?act=charts

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    mickj, never run a query in a loop, always get all the data with a single query, and then, if necessary, do a loop over the output of the query to format the results

    teeej, here's the query to do the GROUP_CONCAT, should you ever get to 4.1
    Code:
    select i.itemid
         , i.album
         , i.format 
         , group_concat(a.artist separator ' | '
               order by a.artist) as artists
      from distro_items as i
    inner
      join distro_artist_album as aa 
        on i.itemid = aa.itemid 
    inner
      join distro_artist as a 
        on aa.artistid = a.artistid
    group
        by i.itemid
         , i.album
         , i.format 
    order 
        by i.itemid desc
    i removed the distro_items_extra because you weren't using any of its columns

    here's the same query, but with multiple results, so that you can easily process the result in an output loop to detect the albums
    Code:
    select i.itemid
         , i.album
         , i.format 
         , a.artist
      from distro_items as i
    inner
      join distro_artist_album as aa 
        on i.itemid = aa.itemid 
    inner
      join distro_artist as a 
        on aa.artistid = a.artistid
    order 
        by i.itemid desc
    this is basically what you had, minus the distro_items_extra table
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Enthusiast Mickj's Avatar
    Join Date
    Jun 2002
    Location
    Margate, Kent, UK
    Posts
    52
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry thats what I ment to say just didn't say it very well.

    I'll Have to give your query a go as would be usefull in a Section im working on at the min.

    Got to admit when I was new to it all I did do exactly what you said as I didn't know enough to correctly join tables but I learn at least that much over the last few years.

    mick
    Be Proud, Be Loud, Be Heard.
    Systems designed by me
    Battle Of The Bands
    http://www.database-dreams.co.uk/Com...p?act=artchart
    Worldwide Music Charts
    http://www.database-dreams.co.uk/Com...ex.php?act=RCH
    UK Charts history (Started 9 Setember 01)
    http://www.database-dreams.co.uk/Com...php?act=charts

  9. #9
    SitePoint Enthusiast
    Join Date
    Feb 2005
    Location
    Glasgow, Scotland
    Posts
    97
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Mickj : The problem with how you described it is that I can get a whole list of items with just one query, compared to two queries per item I want to list.. so I really need to do it the way I have in my post. I wrote PHP code lastnight which is very messy (because the first and last items in the table to be processed are special cases) to basically do what GROUP_CONCAT() does.. I'll make sure to change over to that in future versions of the script when 4.1 is standard.

    Thanks for the code, r937.

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    4.1 has been in production status since october 2004

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Enthusiast
    Join Date
    Feb 2005
    Location
    Glasgow, Scotland
    Posts
    97
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hmmm.. I didn't realise that my code would only select items with the extra info.. that was obvious unwanted. But your tidying up of my query has inadvertently shown a problem with my query for editing individual items.

    My code is:

    Code:
    // Get the item info from the database first
    $itemquery = "SELECT * FROM distro_items AS i, distro_items_extra AS ie
    				WHERE i.itemid = ie.itemid
    				AND i.itemid = '$itemid'";
    $getitem = mysql_query($itemquery);
    				
    // Check that the item id entered has a match in the db
    if(mysql_num_rows($getitem) > 0)
    {
    ETC...
    Obviously this will only get a row if there is an entry for this item in distro_item_extra. Is there any way to get the distro_item table ALWAYS and then get distro_item_extra if it exists? I'm pretty much looking for the union/or from relational algebra equivalent in MySQL.

    Edit - to make this perfectly clear:

    I have two tables, both relating to the same item. I want to try and get both in one query but only one of them DEFINITELY exists.. I need a query that will get the mandatory table and then the other only if it exists.

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    you've described a basic LEFT OUTER JOIN
    Code:
    select * 
      from distro_items AS i
    left outer
      join distro_items_extra AS ie
        on i.itemid = ie.itemid
     where i.itemid = '$itemid'
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Enthusiast
    Join Date
    Feb 2005
    Location
    Glasgow, Scotland
    Posts
    97
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    For some reason whenever I read about outer joins I could never figure out what they were for. And now I know!

    Thanks a lot for all your help!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •