PHP MySQL query (2 tables) output to HTML table

[mrrrl]

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

[Barand]

try

$mysqli = new mysqli(HOST,USERNAME,PASSWORD,DATABASE);

$id = 75;

$sql = "SELECT ci.collection_item_number
        , ci.collection_item_title
        , ci.collection_item_text
        , ci.username_id as ownerid
        , c.comment
        , c.username_id as userid
        FROM collection_items ci
            LEFT JOIN 
            collection_item_comments c 
                ON ci.collection_list_id = c.collections_list_id
                AND ci.collection_item_number = c.collection_items_id
        WHERE ci.collection_list_id = ?";
$data = [];
$stmt = $mysqli->prepare($sql);
$stmt->bind_param('i', $id);
$stmt->execute();
$stmt->bind_result($number,$title,$text,$ownerid,$comment,$userid);
while ($stmt->fetch()) {
    if (!isset($data[$number])) {
        $data[$number] = [  'title' => $title,
                            'text'  => $text,
                            'id'    => $ownerid,
                            'comments' => []
                            ];
        if ($comment) $data[$number]['comments'][] = [$comment, $userid];
    }
    else {
        if ($comment) $data[$number]['comments'][] = [$comment, $userid];
    }
}

?>
<html>
<head><title>Sample</title>
<style type="text/css">
table {
    border-collapse: collapse;
}
tr {
    vertical-align: top;
}
th,td {
    padding: 5px;
}
</style>
</head>
<body>
<table  border="1">
    <tr><th>List Number</th><th>Item Title</th><th>Comments</th><th>Posted By</th></tr>
    <?php
    foreach ($data as $number => $item) {
        $span = count($item['comments'])+1;
        echo "<tr><td rowspan='$span'>$number</td>
            <td rowspan='$span'>{$item['title']}</td>
            <td>{$item['text']}</td>
            <td>{$item['id']}</td></tr>";
        foreach ($item['comments'] as $comm) {
            echo "<tr><td>{$comm[0]}</td><td>{$comm[1]}</td></tr>";
        }
    }
    ?>                                               
</table>
</body>
</html>

[seandisanti]

I published an answer on your post on php-forum also. http://www.php-forum.com/phpforum/viewtopic.php?f=5&p=4411690#p4411690