group concat

[doddsey_65]

im trying to pull the information for a post which is to include 3 db tables(posts, users, attachments)

 

Ive tried the following code:

 

$query = $link->query("SELECT 
            p.*, 
            u.u_username, u.u_posts, u.u_avatar, 
            u.u_signature, u.u_avatar_cropped,
            group_concat(a.a_name, a.a_size, a.a_date_posted separator '<br />') attachments
            FROM ".TBL_PREFIX."posts as p
            JOIN ".TBL_PREFIX."users as u
            ON (u.u_username = p.p_poster)
            LEFT JOIN ".TBL_PREFIX."post_attachments as a
            USING (".TBL_PREFIX."posts.p_pid)
            WHERE p.p_tid = '$tid'
            GROUP BY p.p_pid
            ORDER BY p.p_time_posted ASC")or die(print_link_error());

 

but that generates the error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.p_pid)

        WHERE p.p_tid = '158'

        GROUP BY p.p_pid

        ORDER BY p.p_time_pos' at line 10"

 

All i am trying to do is pull everything from posts, join users and select all attachments that belong to the post.

Here is my database setup(shortened)

 

asf_posts

p_pid | p_name | p_poster

  1            test    doddsey65

 

asf_users

u_uid | u_username

  1        doddsey65

 

asf_attachments

a_aid | a_pid | a_name

  1            1      name

 

can anyone help?

[samshel]

print the query and post here please.

[doddsey_65]

printed query:

 

        SELECT 
		p.*, 
		u.u_username, u.u_posts, u.u_avatar, 
		u.u_signature, u.u_avatar_cropped,
		group_concat(a.a_name, a.a_size, a.a_date_posted separator '<br />') attachments
		FROM asf_posts as p
		JOIN asf_users as u
		ON (u.u_username = p.p_poster)
		LEFT JOIN asf_post_attachments as a
		USING (asf_posts.p_pid)
		WHERE p.p_tid = '158'
		GROUP BY p.p_pid
		ORDER BY p.p_time_posted ASC

[samshel]

Try this:

 

  SELECT 
		p.*, 
		u.u_username, u.u_posts, u.u_avatar, 
		u.u_signature, u.u_avatar_cropped,
		group_concat(a.a_name, a.a_size, a.a_date_posted separator '<br />') attachments
		FROM asf_posts as p
		JOIN asf_users as u
		ON (u.u_username = p.p_poster)
		LEFT JOIN asf_post_attachments as a
		USING (p_pid)
		WHERE p.p_tid = '158'
		GROUP BY p.p_pid
		ORDER BY p.p_time_posted ASC

 

or you can try using "ON" instead of "USING" and specify field names from both tables as you have done for JOIN asf_users

 

[doddsey_65]

i tried it but im getting the error

 

"Unknown column 'p_pid' in 'from clause'"

[samshel]

How about using ON CLAUSE with both table and field names something like

 

SELECT 
		p.*, 
		u.u_username, u.u_posts, u.u_avatar, 
		u.u_signature, u.u_avatar_cropped,
		group_concat(a.a_name, a.a_size, a.a_date_posted separator '<br />') attachments
		FROM asf_posts as p
		JOIN asf_users as u
		ON (u.u_username = p.p_poster)
		LEFT JOIN asf_post_attachments as a
		ON (p.p_pid = a.p_pid) /* replace proper field names from respective tables here */
		WHERE p.p_tid = '158'
		GROUP BY p.p_pid
		ORDER BY p.p_time_posted ASC

[doddsey_65]

thanks, that works. However when i use this to display the name for the 2 attachments i have on the test post:

 

<?php
foreach($row as $key => $value) 
{
        $title = $row[$key]['attachments'];
}
?>

 

echoing $title shows the name of both posts twice.

 

example of the html output:

 

<div>
attachment1
attachment2
</div>

<div>
attachment1
attachment2
</div>

 

when it should be:

 

<div>
attachment1
</div>

<div>
attachment2
</div>

[samshel]

Need to understand what you are trying to do and how many records are there in all three tables.

[doddsey_65]

this query is to show a forum post and any attachments belonging to that post.

 

lets just say there is one record in the asf_posts table

 

p_pid | p_tid  | p_name | p_content                    | p_author

  1          5          post        this is the content        doddsey65

 

so i pull all of the data from asf_posts where the p_tid = the current topic id.

 

i then need to grab some info for the user that made the post so i can display their signature and avatar. So i join asf_users

 

u_uid  |  u_username  | u_avatar  | u_sig

  3          doddsey65        image        this is my sig

 

but i also want to get all attachments from asf_post_attachments where a_pid = the current post id for which there are 2

 

a_aid | a_pid | a_name

  1          1          attachment

  2          1          attachment2

 

now if the post has an attachment then the content of the post will be like so

 

this is the content

[attachment=attachment.jpg]

[attachment=attachment2.jpg]

 

so i do some regex and replace the bbcode with the name of the attachment. I know i can just use \\1 but im testing this because the attachment size will be pulled from the database also.

 

so the bbcode will be replaced with

 

<div>

attachment name

</div>

 

and a new div will be created for each attachment with each attachments name within it

 

<div>

attachment name2

</div>

 

here is my code:

 

<?php
$query = $link->query("SELECT 
            p.*, 
            u.u_username, u.u_posts, u.u_avatar, 
            u.u_signature, u.u_avatar_cropped,
            group_concat(a.a_name) attachments
            FROM ".TBL_PREFIX."posts as p
            JOIN ".TBL_PREFIX."users as u
            ON (u.u_username = p.p_poster)
            LEFT JOIN ".TBL_PREFIX."post_attachments as a
            ON (p.p_pid = a.a_pid)
            WHERE p.p_tid = '$tid'
            GROUP BY p.p_pid
            ORDER BY p.p_time_posted ASC")or die(print_link_error());

$row = $query->fetchAll();

$title = array();

foreach($row as $key => $value) 
{
$p_content = str_replace('<br>', '', $row[$key]['p_content']);

$title[] = $row[$key]['attachments'];

$bb[$key] = "#\[attachment=(.*?)\]#si";
$html[$key] = '<div>'.$title[$key].'</div>';

$p_content = preg_replace($bb[$key], $html[$key], $p_content);
}
?>

 

but as i said it displays the name of each attachment in both divs.

any clearer?

[samshel]

not sure if u understand u correct. u can try following code and confirm i understand u correct

removed the group_concat and pulled out the content out of the loop assuming you will have only one post with that id.

<?php

$query = $link->query("SELECT

            p.*,

            u.u_username, u.u_posts, u.u_avatar,

            u.u_signature, u.u_avatar_cropped,

            a.a_name attachments

            FROM ".TBL_PREFIX."posts as p

            JOIN ".TBL_PREFIX."users as u

            ON (u.u_username = p.p_poster)

            LEFT JOIN ".TBL_PREFIX."post_attachments as a

            ON (p.p_pid = a.a_pid)

            WHERE p.p_tid = '$tid'

            GROUP BY p.p_pid

            ORDER BY p.p_time_posted ASC")or die(print_link_error());

 

$row = $query->fetchAll();

 

$title = array();

 

//FEtching records for 1 post only so first record should give u the content.

$p_content = str_replace('<br>', '', $row[0]['p_content']);

 

foreach($row as $key => $value)

{

$title[] = $row[$key]['attachments'];

 

$bb[$key] = "#\[attachment=(.*?)\]#si";

$html[$key] = '<div>'.$title[$key].'</div>';

 

$p_content = preg_replace($bb[$key], $html[$key], $p_content);

}

echo $p_content;

?>

[/code]

[doddsey_65]

i still cant get it to work, it just displays the same name for both attachments.

 

<?php

$query = $link->query("SELECT 
            p.*, 
            u.u_username, u.u_posts, u.u_avatar, 
            u.u_signature, u.u_avatar_cropped,
            group_concat(a.a_name) as name
            FROM ".TBL_PREFIX."posts as p
            JOIN ".TBL_PREFIX."users as u
            ON (u.u_username = p.p_poster)
            LEFT JOIN ".TBL_PREFIX."post_attachments as a
            ON (p.p_pid = a.a_pid)
            WHERE p.p_tid = '$tid'
            GROUP BY p.p_pid
            ORDER BY p.p_time_posted ASC")or die(print_link_error());
$row = $query->fetchAll();

foreach($row as $key => $value) 
{

$p_content = str_replace('<br>', '', $row[$key]['p_content']);

$name = preg_split('|,|', $row[$key]['name']);

        for($i=0; $i<count($name); $i++)
        {
            $bb[$key] = "#\[attachment=(.*?)\]#si";
            $html[$key] = $name[$i];
        }
            
        $p_content = preg_replace($bb[$key], $html[$key], $p_content);

echo $p_content;
?>

 

$p_content has to be in the foreach loop as there may be more than one post within this topic.