I have two table, Table 1 holds the collections item number, title, posted comments, posted id, etc. and table 2 holds any comments related to the collection item number in table 1 by other users.
I need to output to html table via php MySQL query so the output looks like this:
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
I was working on the query:
collection_items.*, group_concat(collection_item_comments.comment) as subitems
from collection_items
left join collection_item_comments on collection_item_comments.collection_items_id = collection_items.id
group by collection_items.id
But this is not getting the results I need to then use PHP to put it into a HTML table. I tried to put the two tables in to two arrays and then loop through but no joy in that either.
[php]
$listnumber = $_GET[‘list’]; //75 in this example
try {
$query =“SELECT * FROM WTTL_Main_DB.collection_items WHERE collection_list_id = ‘$listnumber’”;
$list_array = $link->query($query);
$query1 =“SELECT * FROM WTTL_Main_DB.collection_item_comments WHERE collections_list_id = ‘$listnumber’”;
$comment_array = $link->query($query1);
if($list_array !== true) {
// Create the beginning of HTML table, and the first row with columns title
$html_table = ‘
echo $html_table; // display the HTML table
}
catch(PDOException $e) {
echo $e->getMessage();
}[/php]