I am hosting a giveaway and the stipulations are you must post in a particular thread in the forum.
After a while I will take the members that posted into that area of the forum, and put the names in a randomizer.

Is there an easy way to get this data vs me looking in the thread and copying each member name to a spreadsheet?

  • luceos replied to this.

  • Digeratimvp
    Just a little mistake with the table, the column was without the prefix:

    SELECT DISTINCT p.user_id
FROM fldp_posts AS p
JOIN fldp_discussions AS d ON p.discussion_id = d.id
WHERE d.id = 611671 AND p.user_id != d.user_id
ORDER BY RAND()
LIMIT 1;

    That should work.
    Follow @luceos 1st post instructions

      Digeratimvp we have had this question for one of our clients before, here's an adapted version of it:

      SELECT DISTINCT p.user_id
FROM posts p
JOIN discussions d ON p.discussion_id = d.id
WHERE d.id = 611671 AND p.user_id != d.user_id
ORDER BY RAND()
LIMIT 1;

      replace d.id with the discussion id, see your url, this discussion has id 36209. Change LIMIT 1 if you need more winners (LIMIT 5).

      This will randomly pick one user randomly from all replying users, except the OP.

      Thanks to @BartVB for pulling that out of the history/preparing this for your use.

          luceos Ok how would I run that?
          Also is there another way that I can just get the list of names from that thread that have posted so that I can video me putting the names in a randomizer for trusts sake?

            4 days later

            Digeratimvp

            SELECT DISTINCT p.user_id
FROM fldp_posts AS p
JOIN fldp_discussions AS d ON p.fldp_discussion_id = d.id
WHERE d.id = 611671 AND p.user_id != d.user_id
ORDER BY RAND()
LIMIT 1;

              SELECT DISTINCT p.user_id
              FROM fldp_posts AS p
              JOIN fldp_discussions AS d ON p.fldp_discussion_id = d.id
              WHERE d.id = 26 AND p.user_id != d.user_id
              ORDER BY RAND()
              LIMIT 1;
              MySQL said: Documentation

              #1054 - Unknown column 'p.fldp_discussion_id' in 'on clause'

                Digeratimvp
                Just a little mistake with the table, the column was without the prefix:

                SELECT DISTINCT p.user_id
FROM fldp_posts AS p
JOIN fldp_discussions AS d ON p.discussion_id = d.id
WHERE d.id = 611671 AND p.user_id != d.user_id
ORDER BY RAND()
LIMIT 1;

                That should work.
                Follow @luceos 1st post instructions

                  That did work, however each time I try it the results are the same, and not random.

                    Digeratimvp it seems mysql only creates a randomized number once unless the seed is updated, see https://www.tek-tips.com/threads/order-by-rand-same-every-time.1224219/. So perhaps this would make it more random:

                    RAND(CURDATE()+CURTIME());
SELECT DISTINCT p.user_id
FROM fldp_posts AS p
JOIN fldp_discussions AS d ON p.discussion_id = d.id
WHERE d.id = 611671 AND p.user_id != d.user_id
ORDER BY RAND()
LIMIT 1;

                    Not tested.