MySQL Query to get items list with user comments via PHP

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 = ‘

’;
// Parse the result set, and adds each row and columns in HTML table
foreach($list_array as $row) {
  $html_table .= '<tr><td>' .$row['collection_item_number']. '</td><td>' .$row['collection_item_title']. '</td><td>' .$row['collection_item_text']. '</td></tr>';
	$itemnumber=$row['collection_item_number'];
	
	foreach($comment_array as $commentrow){
		If ($commentrow['collection_items_id'] == $itemnumber ){
			
			$html_table .= '<tr><td>' .$commentrow['comment']. '</td></tr>';
			
			}
		}
}

}
$link = null; // Disconnect

$html_table .= ‘

’; // ends the HTML table

echo $html_table; // display the HTML table
}
catch(PDOException $e) {
echo $e->getMessage();
}[/php]

“etc” doesn’t tell us anything. Post your exact DB schema with some sample data.

I have place DB scheme and data here: http://sqlfiddle.com/#!9/b065e

It appears your DB schema is wrong for what it seems you are doing. You are going to end up repeating data which violates DB normalization.

The tables you need are

TABLE bands
band_id
band_name

TABLE albums
album_id
band_id
album_name

comments - How you tie comments depends on whether comments are based on a particular band or a particular album. Also, if the comments are selected from a list you will need another table with the comment choices.

TABLE comment_choices
comment_choice_id
comment_description

The output you want grouping by bands is easily handled in php. Same type of thing with a question/Answer app. One question, many answers to your one band/album, many comments.

Thanks for getting back to me. Let me explain a bit further so to understand what I am trying to do.

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 posted on SQLFiddle are the two tables that need to be nested or arrays built or… to get the correct html table ouput to display the list and any users comments for each item in the list. Does this help?

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

You do realize that your fiddle only has TWO tables right? Now I see why I had so much trouble with it.

OK I have added all three tables here http://sqlfiddle.com/#!9/b766e/2
I had planned to do a separate MySQL query for the page heading for the collection name and original posters name against table 1 and then the list information/comments from the other 2 tables in separate MySQL query, rather then trying to combine all three into a single query which maybe more efficient but for me much more confusing to work with. So the reason I originally had just the two tables.

[php]$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];
}
}

?>

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

Just an FYI, this worked pretty good, I made a few changes but got me going in the right direction.
Thanks Kevin.

Sponsor our Newsletter | Privacy Policy | Terms of Service