Randomly choose from 4 tables

Question

I am having a problem with a good headline, but I will explain now.

I am creating an online game. I am trying to build an option to destroy weapons.

I have 4 types of weapons - attack, defense, patrol and spy. patrol and spy weapons have 3 ranks of weapons, and attack and defense - 15 ranks.

I have a table for each of the categories with col. with the names w1, w2, w3, w4 ... and user ID.

I gave each rank points, so w15, for example, costs 15 points, and w2 costs 2 points, and I built a function that calculates how many points the attacker destroyed for the defender.

Where am I stuck - how to choose a random weapon?

let's say the attacker destroyed 100 weapon points. so it can be 100 weapons of the 1st rank of a patrol, or 25 weapons of the 1st rank of each category or 10 weapons of 10. I need it to be random between the categories (attack, defense, patrol and spy) and between weapons (w1, w2, w3 ..). In addition, I need him to be in the limit of the quantity of weapons that the defender has, he cannot lose more than he.

Thank you very much! I know I wrote a long question

Answer 1

I think this is more a logical question than a technical question about working with tables. If you first deal with logic, you can easily perform the necessary actions on the table, no matter how you want. We just need to know how many of each element will be used (destroyed) by random selection. Here is a random selection method in the sudo-code / part php part:

1. Query database for available items and their relative values. 

 2. Store information as a multi-dimensional Array 

 3. Shuffle the Array 

  //in php bool shuffle ( array $itemsArray() ) 

 4. Iterate through each item in the array and add 1 to a variable for that item if, we have not reached our limiting factors (amount available and cost vs remaining points). Do this until all available points are allotted to a variable. 

  //in php $i = 0; do { if ($itemsArray[$i][numAvail] > 0 && ($availiblePoints - $itemsArray[$i][cost] >= $itemsArray[$i][cost]){ $$itemsArray[$i]++ //use of '$$' for variable variable $availiblePoints-=$itemsArray[$i][cost]; } else { countSkips++ //need to keep track of how many items we skip //if $availiblePoints is not zero yet but skips is size of array then //we are done and have leftover points that cant be used. } $i++; if ($i > count($itemsArray)) { $i=0; }; //start over if we have gone past the end of our Array } while ($availiblePoints > 0 && $countSkips < count($itemsArray) ); 

 5. Logic Done. Now use the new variables to perform action on tables 

Since the array was randomly shuffled, our results are random, regardless of how many points we have. If we have 100 points, and the first element in our randomized array is worth 100 points, or the first 4 are worth 25 points; chance will do its job anyway.

This is just a concept. The code can be improved by a bunch, for example, the variables that we saved should actually be in the array so that we can scroll through them when it is time to perform actions in the table.

Answer 2

JOIN your four tables first in this way

 SELECT * FROM ( SELECT * FROM w1 UNION SELECT * FROM w2 UNION SELECT * FROM w3 UNION SELECT * FROM w4 ) 

then calculate your weight function and make a random choice

 ... ORDER BY RAND() LIMIT 5; 

Answer 3

 // randomly picks a number between 1 and 4 $randomWeapon = rand(1,4); // Creates for ex. SELECT * FROM w1 $selectWeapon = mysqli($con, "SELECT * FROM w$randomWeapon" 

Answer 4

Based on my other hint, here is a solution that should do the trick.

Basically, you first randomized all waepons with their user weights. If you have NULL values ​​here, you must cancel them. The huge LIMIT after ORDER BY RAND() necessary because I found that MySQL Optimizer will remove randomization unless you specify LIMIT here. Just make it bigger than all the tables will ever appear.

Secondly, you summarize the weight of the weapon sequentially using a variable.

Thirdly, you select the amount of damage caused from the entire sequential list. You must adapt this to the range of hitpoints if the required damage does not match exactly.

 -- 3. limit your sum to hitpoints SELECT * FROM ( -- 2. now sum weaponWeight SELECT weaponWeight, @prevWeight, @prevWeight:= weaponWeight + @prevWeight as cumulSum FROM ( -- 1. shuffle all waepons of a user to do the random pick SELECT weaponWeight FROM ( -- attack SELECT w1 as weaponWeight FROM attack WHERE ID = 'myUserID' UNION SELECT w2 as weaponWeight FROM attack WHERE ID = 'myUserID' UNION ... UNION SELECT w15 as weaponWeight FROM attack WHERE ID = 'myUserID' -- defence UNION SELECT w1 as weaponWeight FROM defence WHERE ID = 'myUserID' UNION ... UNION SELECT w15 as weaponWeight FROM defence WHERE ID = 'myUserID' -- patrol UNION SELECT w1 as weaponWeight FROM patrol WHERE ID = 'myUserID' UNION SELECT w2 as weaponWeight FROM patrol WHERE ID = 'myUserID' UNION SELECT w3 as weaponWeight FROM patrol WHERE ID = 'myUserID' -- spy UNION SELECT w1 as weaponWeight FROM spy WHERE ID = 'myUserID' UNION SELECT w2 as weaponWeight FROM spy WHERE ID = 'myUserID' UNION SELECT w3 as weaponWeight FROM spy WHERE ID = 'myUserID' ) ORDER BY RAND() LIMIT 1000000000000 -- huge number here because Optimizer will remove random order otherwise ) as randomizedData, (SELECT @prevWeight := 0) as a ) as sums WHERE round(sums.cumulSum) = 100 

Answer 5

Ok ... I checked with one logical table table. Just connect (merge) everything you want to include. (the question is tagged php, so this is a php solution ....)

 <?php $weapons = array( array('name'=>'knife', 'type'=>'A', 'weight'=>5), array('name'=>'sword', 'type'=>'A', 'weight'=>6), array('name'=>'axe', 'type'=>'A', 'weight'=>3), array('name'=>'handgun', 'type'=>'B', 'weight'=>7), array('name'=>'rifle', 'type'=>'B', 'weight'=>5), array('name'=>'cannon', 'type'=>'B', 'weight'=>2), array('name'=>'mustard gas', 'type'=>'C', 'weight'=>7), array('name'=>'agent orange', 'type'=>'C', 'weight'=>10), array('name'=>'lewisite', 'type'=>'C', 'weight'=>5), array('name'=>'mind', 'type'=>'D', 'weight'=>8), // must have at least one thing with one... for this to work. // i can definitely work on a solution that doesn't require this // but it would take me a minute to think about it... array('name'=>'words', 'type'=>'D', 'weight'=>1), array('name'=>'hands', 'type'=>'D', 'weight'=>2), array('name'=>'silent treatment','type'=>'D', 'weight'=>5), ); $total_destroyed = 100; $return = get_weapons($weapons, $weapons, $total_destroyed); print_r($return); function get_weapons($orig_weapons, $in_weapons, $n) { // filter for only weapons w/ weight less than $n $in_weapons = array_filter($in_weapons, array(new LowerThanFilter($n), 'isLowerOrEq')); $return = array(); if ($n > 0) { if (empty($in_weapons)) { $return = get_weapons($orig_weapons, $orig_weapons, $n); } else { $found_it = array(); for ($i = 0; $i < count($in_weapons); $i++) { $rand_index = array_rand($in_weapons); $rand_weapon = $in_weapons[$rand_index]; if ($rand_weapon['weight'] <= $n) { break; } } $max_ct = floor($n/$rand_weapon['weight']); $weapon_ct = rand(1,$max_ct); $amount = $weapon_ct * $rand_weapon['weight']; unset($in_weapons[$rand_index]); $get_more = get_weapons($orig_weapons, $in_weapons, $n-$amount); $return = $get_more; $return[] = array_merge($rand_weapon, array( 'count' =>$count, 'amount'=>$amount)); } } return $return; } class LowerThanFilter { // http://stackoverflow.com/a/5483168/623952 private $num; function __construct($num) { $this->num = $num; } function isLowerOrEq($i) { return $i['weight'] <= $this->num; } } ?> 

---

which can be easily reinstalled. the index value refers to the source index of the $weapons array.

 Array ( [0] => Array ( [name] => words [type] => D [weight] => 1 [count] => 1 [amount] => 1 ) [1] => Array ( [name] => knife [type] => A [weight] => 5 [count] => 2 [amount] => 10 ) [2] => Array ( [name] => sword [type] => A [weight] => 6 [count] => 1 [amount] => 6 ) [3] => Array ( [name] => agent orange [type] => C [weight] => 10 [count] => 2 [amount] => 20 ) [4] => Array ( [name] => mustard gas [type] => C [weight] => 7 [count] => 9 [amount] => 63 ) ) 

Answer 6

Try something like:

  $attackWeapon = array( /* weapon info comes here */ ); $defenseWeapon = array( /* weapon info comes here */); $patrolWeapon = array( /* weapon info comes here */); $spyWeapon = array( /* weapon info comes here */); $rdmWeapon = mt_rand(1,4); $attackLenght = count($attackWeapon); $rdmAttackWeapon = mt_rand(0, $attackLenght-1); $defenseLenght = count($defenseWeapon); $rdmDefenseWeapon = mt_rand(0, $defenseLenght-1); $patrolLenght = count($patrolWeapon); $rdmPatrolWeapon = mt_rand(0, $patrolLenght-1); $spyLenght = count($spyWeapon); $rdmSpyLenght = mt_rand(0, $spyLenght-1); If($rdmWeapon=1){ $dropWeapon = "attack"; echo $rdmAttackWeapon; } else if($rdmWeapon=2){ $dropWeapon = "defense"; echo $rdmDefenseWeapon; } else if($rdmWeapon=3){ $dropWeapon = "patrol"; echo $rdmPatrolWeapon } else($rdmWeapon=4){ $dropWeapon = "spy"; echo $rdmSpyWeapon; } 

It's simple and stupid, but it works for me.