Page 1 of 1

[SOLVED] sort related content

Posted: Fri Apr 13, 2007 11:06 pm
by tarja311
Hi all.

I am building a website where items are displayed and my users can leave comments about them. This part is fine. The problem is inside my administrator panel ( ACP ). I have a page that displays to me all the comments for all the items that are displayed. Unfortunately i am getting duplicate and beyond results for the same item because more than one user has left a comment for it. My layout with this dup problem looks like so :

item 1
--------
- a user comment

item 2
--------
- a user comment

item 2
--------
- another user comment


What i am trying to achieve here is to have all user-made comments to be related to that one item they're commenting on. My current code isn't made to do this, so it's not like i coded it but it does not work, i am just looking for some pointers as to how it can be done.

Thanks :)

-- tarja

Posted: Fri Apr 13, 2007 11:21 pm
by John Cartwright
So whats your question?

Posting your code usually helps too.

Posted: Fri Apr 13, 2007 11:43 pm
by tarja311
Well, my code just grabs the data from the database and displays it in its most basic form, the item and the comments.

The problem is that if there is more than one comment to an item, it outputs the item according to how many comments it has, as i illustrated above. It basically dups the item name.

item1
-----
comment 1

item1
-----
comment 2

...

anyway my code is like so :


TABLE1 = item table.
TABLE2 = comments table.

( not actual table names )

Code: Select all

$query = mysql_query("SELECT DISTINCT ".
			"`TABLE1`.FILENAME, ".
			"`TABLE1`.ID ".
		"FROM ".
			"`TABLE1`, ".
			"`TABLE2` ".
		"WHERE ".
			"`TABLE1`.USER = '$username' AND ".
			"`TABLE1`.ID = `TABLE2`.CID ".
		"ORDER BY ".
			"`TABLE2`.CTIME DESC")
	or die(mysql_error());


while($rw = mysql_fetch_array($query))
{
	$id	= $rw['ID'];
	$filename = $rw['FILENAME'];
	$date = date("d-M-y", strtotime($rw['CTIME']));

	$BUFFER[$i] = "<a href='/#/'>$filename</a>"; ?>
		    
 		<tr>
		<td><? echo $BUFFER[$i]; ?></td>
		<td><? echo $date; ?></td>
		</tr>
	
	<? $i++;
		
} // end while-loop.

Posted: Fri Apr 13, 2007 11:47 pm
by John Cartwright
The easiest way to do this is to store the last processed row into a temporary variable, then on the next iteration check if the current item is the same as the last item, and if they are different display the item.

A simple version would look like:

Code: Select all

$last = '';
while ( .. ) 
{
   if ($last != $row['item']) {
      echo $row['item'];
   }


   $last = $row['item'];
}

Posted: Sat Apr 14, 2007 1:51 am
by tarja311
Thank you Jcart, that helps a lot.