MySQL Result - "Group By" removing incorrect duplicates

Question

Will do my best to describe the problem Im having :)

Each thread/topic in my forum represents one disc. Registered members of the forum use a series of checkboxes (one displayed next to each disc) to tick each disc that they have in their collection. When the form is $_POST'ed it stores the information in a table like so:

| user_id - disc_id |
+--------------------+
| 2 - 571 |
| 2 - 603 |
| 2 - 4532 |

When the user next views the forum I have the checkboxes ticked and disabled on discs that the user owns. This is done using:

$sql = 'SELECT id, poster, subject, posted, last_post, last_post_id,
last_poster, num_views, num_replies, closed, sticky, moved_to, topicimage,
c.user_id, c.disc_id FROM topics LEFT JOIN collections AS c ON c.disc_id=id
WHERE forum_id='.$id.' ORDER BY sticky DESC;

The above grabs all of the discs, which I then display using the following (stripped down) code:

$result = $db->query($sql) or error('Unable to fetch topic list '.$sql.'', __FILE__, __LINE__, $db->error());

// If there are topics in this forum
if ($db->num_rows($result))
{

while ($cur_topic = $db->fetch_assoc($result)) { // If logged in users ID matches the current discs user_id (i.e if this user owns this disc) if ($cur_topic['user_id']==$pun_user['id']) { $read = '
I own this!'; } else { $read = '
I own this!'; } } }

This works great, until a second user adds the same disc ID to his collection, eg:

| user_id - disc_id |
+--------------------+
| 2 - 571 |
| 2 - 603 |
| 6 - 571 |

This causes a duplicate thread to appear in the forum. One is correctly ticked (because I own it), the other is not ticked, though it shares all of the same information such as topic id and image.

My first thought was to try adding GROUP BY c.disc_id to the SQL, which does successfully remove the duplicate topic - However, it is removing the wrong one. The disc that I have ticked is no longer shown, leaving only the unticked version.

Hope that makes sense. Can anyone offer any insight or ideas? Many Thanks.

Answer 1

This is a guess, since I don't know your schema, but I don't see you specifying the user's ID in your WHERE clause.

What about something like the following?

  SELECT t.id, t.poster, t.subject, t.posted, t.last_post, t.last_post_id,
         t.last_poster, t.num_views, t.num_replies, t.closed, t.sticky,
         t.moved_to, t.topicimage, c.user_id, c.disc_id
    FROM topics AS t LEFT JOIN collections AS c ON c.disc_id = t.id
   WHERE forum_id = '.$id.'
     AND c.user_id = '.$user_id.'
ORDER BY t.sticky DESC;

Also, you're joining on Topic ID = Disc ID. Is that intentional?

Answer 2

I can see two easy way for solving this:

first:

with two query, you group query and a second to fetch all the disc_id owned by the user

second:

with your first query:

if ($db->num_rows($result)) {
  $array = Array();
  while ($cur_topic = $db->fetch_assoc($result)) {
    $id = $cur_topic['disc_id'];
    if (!array_key_exists ($id, $array)) { // allow only result per disc_id
      $array[$id] = $cur_topic;
      $array[$id]['owned'] = false;
    }
    // If logged in users ID matches the current discs user_id (i.e if this user owns this disc)
    if ($cur_topic['user_id']==$pun_user['id']) // check if one is owned by the user
      $array['owned'] = true;
  }
  foreach ($array as $cur_topic) {
    if ($cur_topic['owned']) { 
      $read = '<br /><input type="checkbox" disabled="disabled" checked="checked" /> <span style="color:#999">I own this!</span>';
    } else {
      $read = '<br /><input type="checkbox" name="discs[]" value="'.$cur_topic['id'].'" /> I own this!';
    }
  }
}