I have three main tables (see below) on my site. In the example, user 23 wanted to post a list of his “top ten albums of 1988”, it could have been his top ten sports cars of all time. The first table called collections_list is where the table row id, category id (1 for cars, 2 for music, 3 for sports, etc.), the collection name, the poster user id, the date the list was created and if the list is approved for viewing. The second table, collection_items is what holds the users list (items 1 to 10) with their comments for each item in the list. The third table holds the comments from other users (user 15 or 47) not the original user (23) that posted the list.
I have a page the lists all the categories, in this case the user selected music, the next page shows all the lists for music, where the user can select a list to view (at a late time the user will be able to add comments if a register user, like user 15 or 47).
It’s this page where I am having issues getting the list to list correctly. The two tables connection_items and collection_item_comments posted on SQLFiddle are the two tables that need to be nested or arrays built or… to get the correct html table output to display the list and any users comments for each item in the list.
SQLFiddle http://sqlfiddle.com/#!9/b766e/2
collections_list
Id
category_id
collectionname
collections_list_comment
username_id
created_date
approved
collection_items
id
collection_list_id
collection_item_number
username_id
collection_item_title
collection_item_text
created_date
collection_item_comments
id
collections_list_id
collection_items_id
comment
username_id
commentdate
HTML Output:
List Number Item Title Comments Posted By
1 Who Who the best 23 Who is the best, I agree 100% 15 Yes this is fine 47 2 Beatles Beatles the best 23
Yes I agree with your choice for number 2 spot 15 3 Rolling Stones Rolling Stones the best 15 Not too sure about this on 47 4 Greatful Dead Greatful dead the best 23 5 Elton John EJ the best 23 6 Tommy Tommy the best 23 7 BTO BTO the best 23 8 Eagles Eagles the best 23 9 Tommy James Tommy James the best 23 10 Bangles Bangles the best 23