I'm trying to create an mysql table with some data in it which are special items. For example we have item1(chanse: 1), item2(chanse: 1), item(chance: 20%) and item3 (chance: 20) etc.. etc... - Chances are in %
So I created a table with the following information:
CREATE TABLE `special_items` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`item_id` int(11) NOT NULL,
`item_name` varchar(255) DEFAULT NULL,
`item_type` enum('SPECIAL','SILVER','BRONZE','GOLD') NOT NULL DEFAULT 'BRONZE',
`item_ratio` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`) USING BTREE,
UNIQUE KEY `item` (`item_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of special_items
-- ----------------------------
INSERT INTO `special_items` VALUES ('1', '200', 'special_name1', 'BRONZE', '80');
INSERT INTO `special_items` VALUES ('2', '204', 'special_name2', 'BRONZE', '4');
INSERT INTO `special_items` VALUES ('3', '875', 'special_name3', 'BRONZE', '80');
INSERT INTO `special_items` VALUES ('4', '900', 'special_name4', 'BRONZE', '60');
INSERT INTO `special_items` VALUES ('5', '901', 'special_name5', 'SILVER', '90');
INSERT INTO `special_items` VALUES ('6', '968', 'special_name6', 'BRONZE', '65');
INSERT INTO `special_items` VALUES ('7', '777', 'special_name7', 'BRONZE', '30');
What we want to do now is select from 800 rows 5 random items by there ratio. So it needs to have the following requirements:
I also found this query which almost fits the solution but its don't know how I would do this for the random ratio (percentage)
SELECT item_name
FROM special_items AS r1 JOIN
(SELECT CEIL(RAND() *
(SELECT MAX(id)
FROM special_items)) AS id)
AS r2
WHERE r1.id >= r2.id
ORDER BY r1.id ASC
LIMIT 5
If this can be done through PHP it would be awesome.
I'm open to any and all suggestions. I'll also be trying to figure this out on my own in the meantime, but I'm still stuck.
Let me guess that by "chanse" [sic], you mean that each row has a weight, and you want this weight to contribute to the chance of a row being selected.
One method is to generate a random number for each row, multiply by the weight, and then return 5 rows with the highest generated number. It is unclear what you mean by the chance, so this might do what you want:
select si.*
from (select si.*, rand() * item_ratio as weight
from special_items si
) si
order by weight desc
limit 5;
Note: The subquery is needed so the weight is only calculated once per line. I think this does the same thing:
select si.*, rand() * item_ratio as weight
from special_items si
order by weight desc
limit 5;
But MySQL can be peculiar.