MYSQL/PHP - Random row with %

Question

I'm trying to make a random encounter with a monster based on %.

I got in my MySQL database a column called "monster_chancespawn" :

• Monster 1 got 50%
• Monster 2 got 30%
• Monster 3 got 20%

What I want to do is to do a random MySQL SELECT in my "monster" DATABASE based on these %.

I already got this

    $monsterspawn=$db->prepare('SELECT * FROM monster');
    $monsterspawn->execute();
    $monsterspawn->CloseCursor();


    foreach ($monsterspawn as $infospawn)
      { 
      echo . $infospawn["monster_chanceloot"] . ;
      }

With this, it normally echo "50 30 20", but nothing really useful. What I want to do is :

• Getting each monster's chances of loot.
• Maybe doing something like "If rand is between 0 and 50, spawn Monster 1. If it is between 50 and 80, spawn Monster 3. If it is between 80 and 100, spawn Monster 3. BUT I want this to be flexible, and to change when I change the monster_chanceloot value

I could do something like this I guess

$randomspawn = rand(0, 100);
if ($randomspawn >= 0 && $randomspawn <= $infospawn["monster_chanceloot"])
{ $monstername = "Monster1" }

   elseif ($randomspawn >= $infospawn["monster_chanceloot"] && $randomspawn <= $infospawn["monster_chanceloot"](Monster 2's one))
    { $monstername = "Monster2" }

And same thing for Monster 3. The main problem would be that is does not let me change the number of monsters available, and the other problem is that I don't know how to get the monster_chanceloot of each monster outside of the foreach. And I don't want to make 30 MYSQL connection, so avoid using it each time.

Thanks !

EDIT : Here is a possibility given by "German Drulyk" :

I got Monster1, Monster2, Monster3. I duplicate Monster1 2times in my database and Monster2 1time.

I have now 3 Monster1, 2 Monster2 and 1 Monster3. So : 50% of chance to pick a Monster1, 33,3 for Monster 2 and 16,6 for Monster 3.

To get more accurate results, you can copy them to 100 to have 1 monster = 1%. To get a monster, simply do :

    $query=$db->prepare('SELECT * FROM MONSTERDDB ORDER BY RAND() LIMIT 1');
    $query->execute();

Answer 1

IF you will never exceed 100 monsters in a zone then you need a table with 3 columns; zone_id, chance, monster_id

Per zone, insert 100 records and set the chance to 1 through 100 inclusively.

Per record, add a monster_id respective to it's common-ness; all 100 records per zone should have a monster_id

For example, zone1 needs 50 records with monster1, 30 records for monster2, and 20 records for monster3

In PHP, you can write your query like so

$sql = "select
          monster_id
        from
          table_name
        where
          zone_id = $zoneId and
          chance = ".rand(1, 100);

---

Additionally, I have not tried this but I guess native MySQL can achieve the same effect:

$sql = "select
          monster_id
        from
          table_name
        where
          zone_id = $zoneId and
          chance = FLOOR( 1 + RAND() * 100 )";