Sql problem

software development

#1

My site, Calona, is a virtual adoptables site

In order to gain more access to pets, users complete tasks. When the task is complete, a new record is added into users_access which has the users id and the creatures id.

I have a cron script ran hourly which updates the eggs for that hour. Each user can pick from 5 eggs an hour. The eggs for each user are stored in user_access (uid, id1, id2, id3, id4, id5 - uid being the users id, id1-5 being the ids of the eggs)

Each egg has a chance of being chosen. The higher the rarity, the lower the chance. Common: 60%, Uncommon: 20%, Rare: 14%, Ultra-Rare: 6%
The percentages, ID and name of the rarity are kept in the creature_chances (ID 1 = Common, 2 = Uncommon, 3 = Rare, 4 = Ultra-Rare)

Each creature has a field called rarity, which is a foreign key of creature_chances ID

I’m trying to make the sql pick 5 eggs (can be duplicate) for every user based on whether the user has access to the egg (the egg has a row in user_access if they have access to it, otherwise there’s no row) and the actual chance of getting the egg

The problem seems to be to do with the rand() * 100 bit, since if i swap that for actual numbers, its works fine. (ie. if i swap it for 55 (which would be common) i always get eggs 2,3,5, if i used 69 (uncommon) i always get eggs 1,4, if i use 85 (rare) i get egg 6, if i use 100 (ultra-rare) i get egg 55

I’ve gotten the select down to this

[quote]select u.cid
from user_access as u
left join creatures as c on c.ID = u.cid
where u.uid = 1
and c.rarity =
(SELECT ID
FROM creatures_chances
WHERE rand() * 100 <= end
LIMIT 1)[/quote]
The other thing i’ve noticed with using rand() * 100 is that the ids it brings up are not of the same rarity, ie: i get some from common and uncommon so i have no idea what the creatures_chances subquery is actually returning. It should return a number, 1,2,3 or 4
Each creature can only be 1 rarity

i know at the moment it brings up all rows not 1


#2

From what I can tell you’re not using the MySQL rand() correctly. To select a random row from a table use the following:

[quote]SELECT column FROM table
ORDER BY rand()
LIMIT 1[/quote]
So to incorporate that into your SQL, I would try the following:

[quote]
select u.cid
from user_access as u
left join creatures as c on c.ID = u.cid
where u.uid = 1
and c.rarity =
(SELECT ID
FROM creatures_chances
ORDER BY rand()
LIMIT 1)[/quote]
But then you also have that * 100 <= ‘end’ statement which I couldn’t tell by your explanation exactly what that was doing. Either way, you’re using the rand() incorrectly. What you might have to do is generate a random number using PHP and then include that number in your query.

I hope that helps.

Take the time.