randomly pick from 4 tables

Question

I had a bit of a problem to come up with a good title, but I'll explain now.

I'm building an online game. I'm trying to build option to destroy weapons.

I have 4 kinds of weapons - attack,defence,patrol and spy. patrol and spy weapons have 3 ranks of weapons, and attack and defence have 15 ranks.

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

I gave each rank points, so w15 for example worth 15 points, and w2 worth 2 points, and I built a function who calculate how much points the attacker destroyed to the defender.

Where I'm stuck is how to pick randomly weapons?

let's say the attacker destroyed 100 points worth of weapons. so it can be 100 weapons of rank 1 of patrol, or 25 weapons rank 1 of each category, or 10 weapons ranked 10. I need it to be randomly between categories (attack,defence,patrol and spy) and between weapons (w1,w2,w3..). In addition I need it to be in the limit of the number of weapons the defender have, he can't lose more then he have.

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

Answer 1

First UNION your four tables 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 do a random pick

... ORDER BY RAND() LIMIT 5;

Answer 2

// 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 3

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

Basically you first randomize all waepons with their weights of a user. If you have NULLvalues here you should de-select them. The huge LIMIT after the ORDER BY RAND() is necessary because I found that the MySQL Optimizer will remove the randomization if you dont specify a LIMIT here. Just make it bigger than the whole tables will ever get.

Second you sum the weapons weight consecutive by a running variable.

Third you pick from the whole consecutive list the amount of damage done. You should adapt that to a range of hitpoints if the required damage is not matched 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 4

I think this is more of a logic question than it is a technical question about dealing with tables. If you handle the logic first then you can easily perform needed actions on the table however you'd like. We just need to know how many of each item would be used (destroyed) with a random selection. Here is a random selection method in part sudo-code / part php:

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

Because the array was randomly shuffled, our results are random no matter how many points we have. If we have 100 points and the first item in our randomized array cost 100 points, or the first 4 cost 25 points; randomness will have done its job either way.

This is just the concept. The code can be improved a bunch, for instance the variables we saved should actually be in an array so we can loop through them when its time to perform actions on the table.

Answer 5

Okay... I tested with one table logical table. Just combine (union) all the things you want to include. (question is tagged with 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;
   }
}

?>

---

sample output, which can easily be re-arranged. the index value refers to the original $weapons array index.

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 goofy, but works for me.