doddsey_65 Posted January 21, 2011 Share Posted January 21, 2011 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? Quote Link to comment https://forums.phpfreaks.com/topic/225210-query-problems/ Share on other sites More sharing options...
BlueSkyIS Posted January 21, 2011 Share Posted January 21, 2011 post the sql before execution, and the complete mysqli_error? Quote Link to comment https://forums.phpfreaks.com/topic/225210-query-problems/#findComment-1163114 Share on other sites More sharing options...
Maq Posted January 21, 2011 Share Posted January 21, 2011 i get an sql error before seperator. What's the error? Quote Link to comment https://forums.phpfreaks.com/topic/225210-query-problems/#findComment-1163115 Share on other sites More sharing options...
doddsey_65 Posted January 21, 2011 Author Share Posted January 21, 2011 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 Quote Link to comment https://forums.phpfreaks.com/topic/225210-query-problems/#findComment-1163118 Share on other sites More sharing options...
BlueSkyIS Posted January 21, 2011 Share Posted January 21, 2011 seperator should be separator Quote Link to comment https://forums.phpfreaks.com/topic/225210-query-problems/#findComment-1163120 Share on other sites More sharing options...
mikosiko Posted January 21, 2011 Share Posted January 21, 2011 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 Quote Link to comment https://forums.phpfreaks.com/topic/225210-query-problems/#findComment-1163122 Share on other sites More sharing options...
doddsey_65 Posted January 21, 2011 Author Share Posted January 21, 2011 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)); Quote Link to comment https://forums.phpfreaks.com/topic/225210-query-problems/#findComment-1163125 Share on other sites More sharing options...
mikosiko Posted January 21, 2011 Share Posted January 21, 2011 pid != 0? in the tables descriptions that you posted before is not a pid field post the sentence that you tried Quote Link to comment https://forums.phpfreaks.com/topic/225210-query-problems/#findComment-1163191 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.