Export username data based on a tag

Digeratimvp

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

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.

Digeratimvp

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?

Digeratimvp

luceos My posts do not have numbered ID's in the url.

Digeratimvp

luceos I tried your query and it did not work for me.

Said the table doesnt exist

clarkwinkelmann

Digeratimvp if you are using a database table prefix, you will have to adjust the code.

Digeratimvp

clarkwinkelmann I am trying to do that. My database F00 is weak.

Digeratimvp

clarkwinkelmann I cant seem to get it to work. Can you help?

clarkwinkelmann

Digeratimvp what's your database prefix?

Digeratimvp

clarkwinkelmann
I believe my db prefix is
fldp_

luceos

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;

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 = 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'

SkakashiS

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

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

luceos

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.