SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Enthusiast
    Join Date
    Jan 2009
    Posts
    81
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    I need help on a complicated JOIN query (part 2)

    After getting such great responses for my last thread, I thought I'd toss you guys another one

    I have two tables I need to query in order to get the data necessary to display a gallery: galleries and galleryimages. The only data I need from 'galleries' is field 'name', and I need all the fields from 'galleryimages'. Here's my query so far:
    Code:
    SELECT 
    galleries.name
    , allImages.*
    FROM galleries
    INNER JOIN (SELECT * FROM galleryimages WHERE galleryID = ? ORDER BY orderID LIMIT 4) as allImages
    WHERE galleries.id = ?
    The problem lies in how the return data is structured. I'm ending up with one (PHP) multidimensional array with 2 levels. The first level has 4 elements (b/c of my LIMIT clause), and each one of those contains an array containing galleries.name and galleries.*. As such, galleries.name is repeated and I want to avoid this. I'd like my result set to have galleries.name once, then a field called 'allImages' which contains the result set of the 4 images.

    I hope my explanation of what I want to happen isn't too mangled, and thanks in advance!

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    What's wrong with doing this the way this would normally be done in any book, with one row per image retrieved the same as it is stored? A simple join adds the gallery name column

    Code:
    SELECT 
      galleries.name, galleryimages.image 
    FROM 
      galleries
    INNER JOIN 
      galleryimages
        ON galleryimages.galleryID = galleries.ID
    WHERE 
      galleries.id = ?
    ORDER BY 
      orderID 
    LIMIT 
      4
    Why do you want the images stuffed into one column of one row? Why do you want to avoid galleries.name being repeated?

  3. #3
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,194
    Mentioned
    17 Post(s)
    Tagged
    5 Thread(s)
    This isn't a job for MySql but php.

    You will also also need the primary key for galleries and the foreign key for gallery_images. For a simple two level hierarchy this will work:

    PHP Code:
    <?php


    $gallery 
    =
    array(    
        array(
            
    't0_id'=>25
            
    ,'t1_id'=>1234
        
    )
        ,array(
            
    't0_id'=>6
            
    ,'t1_id'=>78
        
    )
        ,array(
            
    't0_id'=>6
            
    ,'t1_id'=>89
        
    )
        ,array(
            
    't0_id'=>5
            
    ,'t1_id'=>23
        
    )
        ,array(
            
    't0_id'=>3
            
    ,'t1_id'=>90
        
    )
        ,array(
            
    't0_id'=>6
            
    ,'t1_id'=>120
        
    )
        ,array(
            
    't0_id'=>25
            
    ,'t1_id'=>45
        
    )
    );

    $pk = array();
    $data = array();

    foreach(
    $gallery as $row) {
      
    $index array_search($row['t0_id'],$pk);

      if(
    $index===false) {

         
    $data[] = array('id'=>$row['t0_id']);
         
    $pk[] = $row['t0_id'];
         
    $index array_search($row['t0_id'],$pk);
         
    $data[$index]['gallery_images'] = array();

      }


      
    $data[$index]['gallery_images'][] = array('id'=>$row['t1_id']);
    }

    echo 
    '<pre>',print_r($data),'</pre>';
    HTML Code:
    Array
    (
        [0] => Array
            (
                [id] => 25
                [gallery_images] => Array
                    (
                        [0] => Array
                            (
                                [id] => 1234
                            )
    
                        [1] => Array
                            (
                                [id] => 45
                            )
    
                    )
    
            )
    
        [1] => Array
            (
                [id] => 6
                [gallery_images] => Array
                    (
                        [0] => Array
                            (
                                [id] => 78
                            )
    
                        [1] => Array
                            (
                                [id] => 89
                            )
    
                        [2] => Array
                            (
                                [id] => 120
                            )
    
                    )
    
            )
    
        [2] => Array
            (
                [id] => 5
                [gallery_images] => Array
                    (
                        [0] => Array
                            (
                                [id] => 23
                            )
    
                    )
    
            )
    
        [3] => Array
            (
                [id] => 3
                [gallery_images] => Array
                    (
                        [0] => Array
                            (
                                [id] => 90
                            )
    
                    )
    
            )
    
    )
    If you need this to be done on any number of levels the problem becomes much more complex. However, I'll spare you that recursive nightmare for now.

    This would be your updates query and just fill the arrays with the information you need in the previous code. I just used the primary key of image and gallery for simplicities sake. The query is generic to the idea though so modify it as you feel necessary. Furthermore, in your subquery I would recommend defining the actual columns and avoiding the evil star. If you only need one column then select that column only.

    Code SQL:
    SELECT 
         t0.id AS t0_id
        ,t0.name t0_name
        ,t1.id AS t1_id
        ,t1.gallery_id AS t1_gallery_id
     FROM 
        galleries AS t0
     LEFT
     JOIN
         (SELECT * FROM galleryimages ORDER BY orderID LIMIT 4) AS t1
      ON
       t0.id = t1.gallery_id
    WHERE 
       t0.id = ?
    Last edited by oddz; Apr 2, 2009 at 15:39.

  4. #4
    SitePoint Enthusiast
    Join Date
    Jan 2009
    Posts
    81
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the replies. I realized what I wanted isn't possible in SQL, so I'm massaging the array in PHP.


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
  •