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"