CODEWITHSUNDEEP
phpmysql
Dro Gonzalez
Dro Gonzalez

Reputation: 19

Pick random entry in MySQL database based on certain criteria

Alright, let's say my MySQL table is set up with entries similar to:

id  code            sold
1   JKDA983J1KZMN49  0
2   JZMA093KANZB481  1
3   KZLMMA98309Z874  0

I'd like it to pick a random ID within the ranges already in the database (or just go from 1-X) and then just assign it to a variable for my own action to be taken. So let's say we want to pick a code that isn't sold (marked as 0 and not 1), then we'd pick it.

Now, it doesn't have to be 100% random, it could check if the first one is sold, if not, keep going. But I'm not 100% sure on how to go by this. Snippets would be appreciated because I can work out things easily on my own, I just need an example to see where I am going.

Upvotes: 1

Views: 139

Answers (5)

Ja͢ck
Ja͢ck

Reputation: 173562

It seems that your codes are already random, so why not just take the first item; if you have many unsold records in your database, doing the typical ORDER BY RAND() will hurt the database performance.

SELECT *
FROM codes
WHERE sold = 0
LIMIT 1
FOR UPDATE;

I've also added FOR UPDATE to avoid race conditions, assuming that you're using transactions, as you update the record later (to actually sell it).

Upvotes: 1

tjcertified
tjcertified

Reputation: 704

If you don't need the random, then don't use it. It can affect performance very negatively. Since you mentioned in your post that it wasn't necessary, I would recomment using Ezequiel's answer above and dropping the rand. See Most Efficient Way To Retrieve MYSQL data in random order PHP for more info.

Upvotes: 1

hd1
hd1

Reputation: 34657

Adding ORDER BY RAND() to the rest of your SELECT query is the most straightforward way to accomplish this.

Upvotes: 0

mga
mga

Reputation: 1970

Have you tried

SELECT * FROM myTable WHERE sold = 0 ORDER BY RAND() LIMIT 1

Upvotes: 0

Ezequiel Muns
Ezequiel Muns

Reputation: 7752

How about using a WHERE and ORDER BY RAND()

SELECT id, code
FROM tablename
WHERE sold = 0
ORDER BY RAND()
LIMIT 1

Upvotes: 2

Related Questions