PDO and prepared statements on dynamic sized queries

Question

I am developing a small gaming website for college fest where users attend few contests and based on their ranks in result table, points are updated in their user table. Then the result table is truncated for the next event. The schemas are as follows:

user

-------------------------------------------------------------
user_id   |   name    |    college    |    points    |   
-------------------------------------------------------------

result

---------------------------
user_id   |    score
---------------------------

Now, the first 3 students are given 100 points, next 15 given 50 points and others are given 10 points each.

Now, I am having problem in developing queries because I don't know how many users will attempt the contest, so I have to append that many ? in the query. Secondly, I also need to put ) at the end.

My queries are like

$query_top3=update user set points =points+100 where id in(?,?,?);
$query_next5=update user set points = points +50 where id in(?,?,?,?,?);
$query_others=update user set points=points+50 where id in (?,?...........,?);

How can I prepare those queries dynamically? Or, is there any better approach?

EDIT

Though its similar to this question,but in my scenario I have 3 different dynamic queries.

Are you storing `points` in result table for one event only or multiple events at a time? — , Feb 01 '14 at 05:17
@sam:I mentioned it already that `score` is only for one event and for the next event the `result` table is truncated.The `user` table exists forever and it will decide the winners when fest is over. — Naveen, Feb 01 '14 at 05:20
Maybe this can help you out: http://stackoverflow.com/questions/920353/php-pdo-can-i-bind-an-array-to-an-in-condition — apoq, Feb 01 '14 at 05:50

score 1 · Accepted Answer · answered Feb 01 '14 at 07:20

If I understand correctly your requirements you can rank results and update users table (adding points) all in one query

UPDATE users u JOIN
(
  SELECT user_id,
  (
    SELECT 1 + COUNT(*)
      FROM result
     WHERE score >= r.score
       AND user_id <> r.user_id
  ) rank
    FROM result r
) q 
    ON u.user_id = q.user_id
   SET points = points + 
       CASE 
         WHEN q.rank BETWEEN 1 AND  3 THEN 100
         WHEN q.rank BETWEEN 4 AND 18 THEN  50
         ELSE 10 
       END;

It totally dynamic based on the contents in of result table. You no longer need to deal with each user_id individually.

Here is SQLFiddle demo

PDO and prepared statements on dynamic sized queries

1 Answers1