1

Using mysql and PHP, I'd like to setup a query which pulls a problem from the problems table which a particular user has not seen yet.

I have two tables, 1) problems table and 2) records table.

The problems table has two fields: p_id, prob. This table contains a list of problems to display to the user.

The records table has 4 fields: record_id, user_id, p_id, num_seen. Anytime a user sees a particular problem the user's id gets recorded (user_id), the problem he/she saw gets recorded (p_id) and the number of times that user has seen it gets recorded (num_seen).

The query I'm trying to write would select a p_id from the problems table, under the condition that this p_id has not yet been viewed by a particular user (user_id).

This seems like it should be simple but my attempts to use "WHERE problems.p_id != records.p_id AND user_id=$id" haven't worked. Any advice on the correct query or a better way to get the desired result would be awesome.

john k
  • 1,086
  • 4
  • 14
  • 19

1 Answers1

1
SELECT p.* 
FROM   problems p 
       LEFT JOIN records r 
         ON r.user_id = 100 
            AND r.p_id = p.p_id 
WHERE  p.p_id IS NULL 
       AND p.p_id >= Rand() * (SELECT Max(p_id) 
                               FROM   problems) 
LIMIT  1

Where 100 is the current user_id.

you need indexes on

  • (problems.p_id)
  • (records.user_id,records.p_id)
Pentium10
  • 204,586
  • 122
  • 423
  • 502
  • 1
    The LEFT OUTER JOIN WHERE x IS NULL part is correct, but the ORDER BY RAND() [is a real performance killer](http://stackoverflow.com/questions/19412/how-to-request-a-random-row-in-sql) – Konerak Mar 21 '11 at 20:42
  • This is much better. Last sidenote: if your p_id's are not uniformly distributed, the randomness will be skewed and some rows (those just following a gap) will occur more frequently than others, this is the best solution without adding a random-number column. – Konerak Mar 22 '11 at 05:12