MYSQL Selecting a rand value from the max value in a column?

[Monkuar]

I have a row in items_equip table:

 

 

short sword | 3 | 10

 

 

3 is min_dmg

 

10 is max_dmg

 

Let's a say a person kills a mob (to get exp), I need mysql to select a random row in my items_equip table and insert it into items_user_equip.  But while doing the insertion function i need MYSQL to randomize the values of min_dmg and max_dmg no higher than 3, 10 respectively.  

 

So, maybe it could do 2 | 7   or something (2min, 7max)

 

possible?

 

3 | 10 are fixed values in the row column already....

Edited May 26, 2013 by Monkuar

[Monkuar]

$getequip = $db->query("SELECT max(min_dmg) as min,max(max_dmg) as max from items_equip where name = 'Sword' ");
 
while($equipdata = $db->fetch_assoc($getequip)){
$mindamage = rand(1, $equipdata['min']);
 
$maxdamage = rand(1, $equipdata['max']);
}
echo ' Min Dmg Possibilites: '.$mindamage.' || MAX Damage: '.$maxdamage.'';

 

 

I think this works right? If so I marked as solved for now ty

Edited May 26, 2013 by Monkuar

[kicken]

Based on your description of the problem, it sounds like your query should only be returning one row which means there is no need for the MIN/MAX functions. If it does in fact return several rows though, you would need them.

 

You can do the random bit inside the query using mysql's RAND function. For instance:

 

SELECT FLOOR(1+(RAND()*min_dmg)) as min_dmg, FLOOR(1+(RAND()*max_dmg)) as max_dmg

That would give you a random number such that 1 <= x <= min_dmg (likewise for max_dmg).

 

Note that for both of this solution and your solution, it is possible for you to get a max_dmg number that is lower than (or equal to) your min_dmg number. Ie, you might end up with min_dmg=3 max_dmg=1

 

If you want to ensure that max_dmg is larger than min_dmg, you need to make min_dmg the lower bound when finding a random max_dmg. For instance:

SELECT FLOOR(1+(RAND()*min_dmg)) as min_dmg, FLOOR(min_dmg+(RAND()*(max_dmg-(min_dmg-1)))) as max_dmg

That would still leave the possibility of min_dmg = max_dmg but you can adjust for that also if you want to prevent it. Edited May 26, 2013 by kicken