group by

[doddsey_65]

im using a query to pull all posts, join the parent topic, join the user table on the poster of the post and join the attachments table on the pid of the attachment:

 

post_query = 
            "SELECT t.*, p.*, u.*, a.*
            FROM ".TBL_PREFIX."topics t
            JOIN ".TBL_PREFIX."posts p
            ON (p.p_tid = t.t_tid)
            JOIN ".TBL_PREFIX."users u
            ON (p.p_poster = u.u_username)
            LEFT JOIN ".TBL_PREFIX."post_attachments a
            ON (a.a_pid = p.p_pid)
            WHERE t.t_name = '$t_name'
            GROUP BY p.p_pid
            ORDER BY p.p_time_posted ASC";

 

then i echo the attachment if the post has any. unfortunatly it only displays one attachment name regardless of how many it has. if i remove the group by in the query then i get a duplicate of the post for how ever many attachments it has. Any one know what im doing wrong?

 

 

[gizmola]

doddsey,

  That is how joins work.  You get the product of the join.  This is why it is called a "result set".  Your code needs to handle the fact that the relationship includes a one to many.

[doddsey_65]

so what would you suggest? running a query within the foreach loop of the result set to get the attachments?

[gizmola]

No, get the result set, read it in, and when you're parsing it, build the hierarchy in objects or arrays.

[doddsey_65]

this is how i build the attachement list for a post:

 

if(!empty($row[$key]['a_name']))
            {
                $parts[$key] = explode('.',$row[$key]['a_name']);
                
                $size[$key] = round($row[$key]['a_size']/1024, 2);
                
                if($parts[$key][1] == 'jpg' || $parts[$key][1] == 'jpeg' || $parts[$key][1] == 'png' || $parts[$key][1] == 'gif' || $parts[$key][1] == 'bmp')
                {
                    $attachment_list .= '<div class="post_attachment"><p class="attachment_name">'.$parts[$key][0].'</p><a href="'.$this->vars['config']['asf_root'].'attachments/'.$row[$key]['p_poster'].''.$row[$key]['a_name'].'"><img class="attachment_image" alt="" src="'.$this->vars['config']['asf_root'].'attachments/'.$row[$key]['p_poster'].''.$row[$key]['a_name'].'" /></a><p class="attachment_description">'.$row[$key]['a_description'].'</p><p class="attachment_size"><b>Size: </b>'.$size[$key].'kb</p></div>';
                }
                else
                {
                    $attachment_list .= '<div class="post_attachment"><p class="attachment_name">'.$parts[$key][0].'</p><p class="attachment_description">'.$row[$key]['a_description'].'</p><p class="attachment_size"><b>Size: </b>'.$size[$key].'kb</p></div>';
                }
                
                $array['attachment_list'] = $attachment_list;
                
            }
            else
            {
                $array['attachment_list'] = null;
            }

 

is that what you mean?

 

[gizmola]

What i'm saying is that your result set is going to be one where you have multiple rows for the same post/attachment when you have multiple attachments. 

 

As you fetch those rows, one solution is to create an array of posts, and that array could have an element that is an array of attachments.

 

Your code needs to detect whether this is a new post or not.  If it is not a new post, then you'll add to the array of attachements for that post.

 

[doddsey_65]

im struggling to get this working or to understand the code behind it. could you perhaps give an example?

[gizmola]

I might do something like this:

 

$posts = array();
$postid = ''

//fetch results:
while ($row = mysql_fetch_assoc($result)) {
  $attachment_name = $row['attachment_name'];
  unset($row['attachment_name']);
  if ($row['pid'] != $postid) {
     $postid = $row['pid'];
     $posts[$postid] = $row;
     $posts[$postid]['attachments']= array();
  }
  if (!empty($attachment_name)) {
     $posts[$postid]['attachments'][] = $attachment_name;
  }
}

var_dump($posts);

[doddsey_65]

ive had to modify it a little since im using PDO and a foreach loop instead of a while loop but i get an empty result on the var_dump().

 

$post_query = 
            "SELECT t.*, p.*, u.*, a.*
            FROM ".TBL_PREFIX."topics t
            JOIN ".TBL_PREFIX."posts p
            ON (p.p_tid = t.t_tid)
            JOIN ".TBL_PREFIX."users u
            ON (p.p_poster = u.u_username)
            LEFT JOIN ".TBL_PREFIX."post_attachments a
            ON (a.a_pid = p.p_pid)
            WHERE t.t_name = '$t_name'
            GROUP BY p.p_pid
            ORDER BY p.p_time_posted ASC";
            
        $execute = self::$instance->query($post_query)
            or die(self::$instance->printError($post_query));
            
        $row = $execute->fetchAll();

        $attachments = array();
        $pid = '';
        
        foreach($row as $key => $value)
        { 
            $attachment_name = $row[$key]['a_name'];
            unset($attachment_name);
            
            if($row[$key]['p_pid'] != $pid)
            {
                $pid = $row[$key]['p_pid'];
                $post[$pid] = $row;
                $post[$pid]['attachments'] = array();
            }
            
            if(!empty($attachment_name))
            {
                $parts[$key] = explode('.',$row[$key]['a_name']);
            
                $size[$key] = $row[$key]['a_size'];
                
                if($parts[$key][1] == 'jpg' || $parts[$key][1] == 'jpeg' || $parts[$key][1] == 'png' || $parts[$key][1] == 'gif' || $parts[$key][1] == 'bmp')
                {
                    $post[$pid]['attachments'][] = '<div class="post_attachment"><a href="'.$this->vars['config']['asf_root'].'attachments/'.$row[$key]['a_name'].'"><img class="attachment_image" alt="" src="'.$this->vars['config']['asf_root'].'attachments/'.$row[$key]['a_name'].'" /></a><p class="attachment_description">'.$row[$key]['a_description'].'</p><p class="attachment_size"><b>Size: </b>'.$size[$key].'kb</p></div>';
                }
                else
                {
                    $post[$pid]['attachments'][] = '<div class="post_attachment"><p class="attachment_description">'.$row[$key]['a_description'].'</p><p class="attachment_size"><b>Size: </b>'.$size[$key].'kb</p></div>';
                }
            }    
            
            var_dump($post[$pid]['attachments']);
        }

 

[gizmola]

Does the query return a result set?

 

Also you used:  foreach($row as $key => $value)

 

Just use foreach($row as $value) and reference $value.

[doddsey_65]

ive amended the code a bit and the array is populated but still with only one attachment

 

if($row[$key]['p_pid'] != $pid)
            {
                $pid = $row[$key]['p_pid'];
                $post[$pid] = $row;
                $post[$pid]['attachments'] = array();
            }
            
            if(!empty($attachment_name))
            {
                $parts[$key] = explode('.',$row[$key]['a_name']);
            
                $size[$key] = $row[$key]['a_size'];
                
                if($parts[$key][1] == 'jpg' || $parts[$key][1] == 'jpeg' || $parts[$key][1] == 'png' || $parts[$key][1] == 'gif' || $parts[$key][1] == 'bmp')
                {
                    $post[$pid]['attachments'][] = '<div class="post_attachment"><a href="'.$this->vars['config']['asf_root'].'attachments/'.$row[$key]['a_name'].'"><img class="attachment_image" alt="" src="'.$this->vars['config']['asf_root'].'attachments/'.$row[$key]['a_name'].'" /></a><p class="attachment_description">'.$row[$key]['a_description'].'</p><p class="attachment_size"><b>Size: </b>'.$size[$key].'kb</p></div>';
                }
                else
                {
                    $post[$pid]['attachments'][] = '<div class="post_attachment"><p class="attachment_description">'.$row[$key]['a_description'].'</p><p class="attachment_size"><b>Size: </b>'.$size[$key].'kb</p></div>';
                }
            }    
            //var_dump($post[$pid]['attachments']);

            foreach($post[$pid]['attachments'] as $attachment)
            {
                $list .= $attachment;
            }

 

[gizmola]

I don't see anything overtly wrong with your code.  Check your original result set?