2

I want to get 3 random records from my table to 90 times.

Scenario

  user_id     number_of_bids 
    12              20
     8               40
     6               30

what i want is...Get above 3 rows in random order to a specific number In fact it is sum(number_of_bids)...

And every row should not repeated greater than its number of bids..

I have created a query where I am getting sum of number_of_bids.Now second query required where these 3 records should be in random order to sum(number_of_bids) times and every record should not greater repeated greater than its number_of_bids.

Not sure it can be achieved in one query or not.But you people are experts I am sure you can help me.It will save my execution time.. Thanks..

Jay Harris
  • 4,201
  • 17
  • 21
Asif Asghar
  • 826
  • 8
  • 12
  • Do you really need that many rows? If you just wanted to get the user_id in a random weighted order so they are more likely to appear at the top based on number_of_bids then you can check out weighting results like this post: http://stackoverflow.com/questions/2417621/mysql-select-random-entry-but-weight-towards-certain-entries – Jonathan Kuhn Dec 09 '14 at 06:55
  • Yes Because I am trying to write a cron job which will place bids in random order.So I don't want any user miss his bid. – Asif Asghar Dec 09 '14 at 06:59

1 Answers1

1

I would just build an array out of the rows and shuffle it:

$stmt = $db->query('SELECT user_id, number_of_bids FROM table_name', PDO::FETCH_KEY_PAIR);
$results = array(); $startIndex = 0;
foreach ($stmt as $userId => $numberOfBids) {
    $results += array_fill($startIndex, $numberOfBids, $userId);
    $startIndex += $numberOfBids;
}
shuffle($results);

Then, you can iterate $results however you'd like.

Ja͢ck
  • 170,779
  • 38
  • 263
  • 309