query problems

[doddsey_65]

im trying to group all forums into their respective categories like so:

 

category 1

  forum 1

  forum 2

 

category 2

  forum 3

  forum 4

 

but im having problems with the query:

 

$forum_query = mysqli_query($link, "SELECT cid, c_name, 
                        group_concat(f_name seperator ', ') forums
                        FROM ".TBL_PREFIX."categories
                        LEFT JOIN ".TBL_PREFIX."forums 
                        using (cid)
                        group by ".TBL_PREFIX."categories")
                        or die(mysqli_error($link));

while($forum_info = mysqli_fetch_array($forum_query, MYSQLI_ASSOC))
{
    echo $forum_info['cat_name']."<br>";
    echo $forum_info['forums']."<br>"; 
}

 

i get an sql error before seperator.

 

the database tables are as follows(btw TBL_PREFIX is "asf_")

 

asf_categories

cid    c_name

 

asf_forums

fid    cid    f_name

 

so to summarise im trying to group all foums with cid of 1 into cateogry with cid of 1 and so on.

 

Any pointers?

[BlueSkyIS]

post the sql before execution, and the complete mysqli_error?

[Maq]

i get an sql error before seperator.

 

What's the error?

[doddsey_65]

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 'seperator ', ') forums						FROM asf_categories						LEFT JOIN asf_forums ' at line 2

[BlueSkyIS]

seperator should be separator

[mikosiko]

        group_concat(f_name seperator ', ') forums

 

SEPERATOR ?  ...

 

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

 

Blue beat me

[doddsey_65]

thanks for the replies. how would i go about not including anything from forums table where the pid != 0?

i tried adding a where clause but it didnt show any categories.

also is this the best way to do this? at the minute for tests im only pulling f_name but i will be wanting to pull alot more from the forum table later. heres my revised code:

 

$forum_query = mysqli_query($link, "SELECT cid, c_name, 
                        group_concat(f_name separator '<br />') forums
                        FROM ".TBL_PREFIX."categories
                        LEFT JOIN ".TBL_PREFIX."forums 
                        USING (cid)
                        GROUP BY ".TBL_PREFIX."categories.cid")
                        or die(mysqli_error($link));

[mikosiko]

pid != 0?

 

in the tables descriptions that you posted before is not a pid field

 

post the sentence that you tried