CODEWITHSUNDEEP
mysqlarrays
Swee Hong
Swee Hong

Reputation: 539

how to array_rand an array?

I have 1 table. I would like to create an small lottery game.

t1 have data as below.

+---------+------------------+-------------------+
| ID      | typeid           | createtime        |
+---------+------------------+-------------------+
| 1       | 1                | 123456            |
| 2       | 2                | 123123            |
| 3       | 1                | 12312312312       |
| 4       | 1                | 13123123          |
| 5       | 1                | 123dddddaasd123   |
+------------------------------------------------+

I would like to choose the winner when the typeid = 1.

My query like this (actually use query or fetch_all of fetch_first?)

$qsid = DB::fetch_all("SELECT * FROM ".DB::table('t1')." WHERE typeid = 1");

and I would like to use

array_rand to choose winner, so should be like array_rand(1,3,4,5);

then I can use the final result to set the winner. Or any suggestion for other better way?

Thank you.

Upvotes: 1

Views: 188

Answers (2)

Bhavik Hirani
Bhavik Hirani

Reputation: 2016

Try This New One

SELECT `ID` 
FROM `t1` 
WHERE `ID` IN (
               SELECT `ID` 
               FROM `t1` 
               WHERE `typeid` = 1)
ORDER BY RAND()
LIMIT 1

Upvotes: 3

sanjeevjha
sanjeevjha

Reputation: 1549

We can do it in various way, let's do it as below.

1st way: let's do it on mysql level

select ID from t1 where typeid =1 order by RAND() limit 1;

above query will select random row of mysql where typeid is 1.

2nd way:let's do it on coding level

we need to select ID from t1 where typeid =1 and keep it in any array and then select random element from the array

int array[]={1,3,4,5}
int finalResultId = array[java.util.new Random().nextInt(array.length)]

I hope it will help you out. Thanks

Upvotes: 2

Related Questions