php - MySQL query with multiple random values but sum always within a range -

php - MySQL query with multiple random values but sum always within a range -

i have table of store items price. i'm trying write mysql query pulls number of items (between 3 , 6) @ random, total value of items within $20 of value chosen user.

any idea's on how accomplish this? in advance!

edit*** far here have. big issue sum(price) takes sum of items. secondary issue having "limit" random, can have php pick random number between 3 , 6 prior running query.

select item,price,sum(price) items sum(price) between ($value-10) , ($value+10) order rand() limit 6

i can't think of way in sql query without joining items table on multiple times, lead combinatorial explosion number of items in table grows.

i've worked solution in php breaks items cost groups. consider next table:

+----+--------------------+-------+ | id | item | cost | +----+--------------------+-------+ | 1 | apple | 10.5 | | 2 | banana | 1.85 | | 3 | carrot | 16.22 | | 4 | donut | 13.33 | | 5 | eclair | 18.85 | | 6 | froyo | 26.99 | | 7 | gingerbread | 12.15 | | 8 | honecomb | 50.68 | | 9 | ice-cream-sandwich | 2.44 | | 10 | jellybean | 2.45 | | 11 | kitkat | 2.46 | | 12 | lollipop | 42.42 | +----+--------------------+-------+

http://sqlfiddle.com/#!9/0d815

first, break items cost groups based on random number of items (between 3 , 6 in case). cost grouping increment determined variance in cost ($20.00) divided number of items beingness selected. ensures not go outside of variance. here illustration grouping of 4 items:

price_group_increment = variance / number_items

price_group_increment = 20 / 4 = 5

select count(`id`) `item_count`, round(`price` / 5) `price_group` `items` `price` <= 35 grouping `price_group` order `price_group` asc;

result set:

+------------+-------------+ | item_count | price_group | +------------+-------------+ | 4 | 0 | | 2 | 2 | | 2 | 3 | | 1 | 4 | +------------+-------------+

next, can search through result set find combination of cost groups equal target cost group. target cost grouping determined target cost divided cost grouping increment. using our illustration above, let's seek find 4 items add together $35.00 $20.00 variance.

target_price_group = round(target_price / price_group_increment)

target_price_group = round($35.00 / $5.00) = 7

searching through result set, have can target cost grouping of 7 these groups of 4 items:

select `items`.* `items` round(`price`/5) = 0 order rand() asc limit 2; select `items`.* `items` round(`price`/5) = 4 order rand() asc limit 1; select `items`.* `items` round(`price`/5) = 3 order rand() asc limit 1; or select `items`.* `items` round(`price`/5) = 0 order rand() asc limit 1; select `items`.* `items` round(`price`/5) = 3 order rand() asc limit 1; select `items`.* `items` round(`price`/5) = 2 order rand() asc limit 2;

to speed finding random, suitable combination of queries, wrote recursive function randomly weights each cost grouping based on number of items in it, sorts it. speeds things because function returns finds first solution. here's total php script:

<?php function rand_weighted($weight, $total){ homecoming (float)mt_rand()*(float)$weight/((float)mt_getrandmax()*(float)$total); }; //you can alter these $targetprice = 35.00; $numproducts = rand(3,6); $maxvariance = 20.00; $pricegroupincrement = $maxvariance / $numproducts; $targetpricegroupsum = (int)round($targetprice/$pricegroupincrement, 0); $select = "select count(`id`) `item_count`, round(`price`/{$pricegroupincrement}) `price_group`"; $from = "from `items`"; $where = "where `price` <= {$targetprice}"; $groupby = "group `price_group`"; $orderby = "order `price_group` asc"; //for readability of result set, not necessary $sql = "{$select} {$from} {$where} {$groupby} {$orderby}"; echo "sql cost groups:\n{$sql};\n\n"; //run query here , result set //here sample result set //this assumes $targetprice = 35.00, $numproducts=4, , $maxvariance=20.00 $numproducts = 4; $pricegroupincrement = 5; $targetpricegroupsum = 7; $resultset = array( array('item_count'=>4, 'price_group'=>0), array('item_count'=>2, 'price_group'=>2), array('item_count'=>2, 'price_group'=>3), array('item_count'=>1, 'price_group'=>4), ); //end sample result set $pricegroupitemcount = array(); $pricegroupweight = array(); $total = 0; //randomly weight cost grouping based on how many items in grouping foreach ($resultset $result){ $pricegroupitemcount[$result['price_group']] = $result['item_count']; $total += $result['item_count']; } foreach ($resultset $result){ $pricegroupweight[$result['price_group']] = rand_weighted($result['item_count'], $total); } //recursive anonymous function find match $recurse = function($pricegroupweight, $selection=array(), $pricegroupsum=0) utilize ($pricegroupitemcount, $total, $numproducts, $targetpricegroupsum, &$recurse){ //sort random weighted value arsort($pricegroupweight); //iterate through each item in $pricegroupweight associative array foreach ($pricegroupweight $pricegroup => $weight){ //copy variables can seek cost grouping $pricegroupweightcopy = $pricegroupweight; $selectioncopy = $selection; $pricegroupsumcopy = $pricegroupsum + $pricegroup; //try find combination adds target cost grouping if (isset($selectioncopy[$pricegroup])){ $selectioncopy[$pricegroup]++; } else { $selectioncopy[$pricegroup] = 1; } $selectioncount = array_sum($selectioncopy); if ($pricegroupsumcopy == $targetpricegroupsum && $selectioncount == $numproducts) { //we found working solution! homecoming $selectioncopy; } else if ($pricegroupsumcopy < $targetpricegroupsum && $selectioncount < $numproducts) { //remove item cost grouping unset($pricegroupweightcopy[$pricegroup]); //if there still remaining items in group, add together adjusted weight cost grouping $remaininginpricegroup = $pricegroupitemcount[$pricegroup] - $selectioncopy[$pricegroup]; if ($remaininginpricegroup > 0){ $remainingtotal = $total - count($selection); $pricegroupweightcopy[$pricegroup] = rand_weighted($remaininginpricegroup, $remainingtotal); } //try find solution recursing $tryrecursion = $recurse($pricegroupweightcopy, $selectioncopy, $pricegroupsumcopy); if ($tryrecursion !== null){ homecoming $tryrecursion; } } } homecoming null; }; $selection = $recurse($pricegroupweight); if ($selection===null){ echo "there no possible solutions\n"; } else { echo "sql items:\n"; foreach ($selection $pricegroup => $numberfrompricegroup){ $select = "select `items`.*"; $from = "from `items`"; $where = "where round(`price`/{$pricegroupincrement}) = {$pricegroup}"; $orderby = "order rand() asc"; $limit = "limit {$numberfrompricegroup}"; $sql = "{$select} {$from} {$where} {$orderby} {$limit}"; echo "$sql;\n"; } }

this algorithmic approach should perform much improve pure sql query-based solution, 1 time items table grows.

php mysql random