select &amp; case

select & case

Desbrina

Hello, i'm currently trying to work out this sql query

SELECT c.ID, c.family, c.rarity
			FROM creatures AS c 
			RIGHT JOIN user_access AS a ON a.cid = c.ID
			WHERE c.type = 'Egg' 
			AND a.uid=1
			ORDER BY 
			  (RAND( ) * (CASE c.rarity 
							   WHEN 'Common' THEN 60
							   WHEN 'Uncommon' THEN 20
							   WHEN 'Rare' THEN 10
							   ELSE 4 END)) DESC
			LIMIT 1

i have most of it understood, but not the case part of it, can someone explain how the case part of it works?

Weedpacket

As a PHP function it would look like

function thingy($rarity)
{
    switch($rarity)
    {
    case 'Common': return 60;
    case 'Uncommon': return 20;
    case 'Rare': return 10;
    default: return 4;
    }
}

Desbrina

ok, so if i understand correctly then Common has a 60% chance of appearing, Uncommon 20%, Rare 10%, Ultra-Rare 4%

If thats so, it's not working correctly or i just have bad luck

I ran the query 10,000 seperate times (using a while loop) and Rare came up twice, Uncommon & Ultra-Rare not at all Common came 9,998 times

I know that the percentage is worked out every time so every query is different, but out of 10,000 goes, i'd have though I'd have more than just 2 not being common

Weedpacket

Uh, no; it's taking the number corresponding to the "rarity" of each record and multiplying it by a random number, which it uses to decide how to order the records it has already selected, then it picks the top one off that record.

I think what you're after would be a WHERE condition that randomly selects c.rarity='Common' 60% of the time, c.rarity='Uncommon' 20% and so on.

Not attempting to test or verify syntax (I'm certain it's wrong)....

WHERE c.rarity = (CASE (RAND()*100)
    WHEN <=60 THEN 'Common'
    WHEN <=60+20 THEN 'Uncommon'
    WHEN <=60+20+10 THEN 'Rare'
    ELSE 'Ultra-rare'
)

Desbrina

that didn't work but i tried doing it a different way, which also makes it easier to manage

I now have 3 tables

creatures

CREATE TABLE IF NOT EXISTS `creatures` (
  `ID` int(11) NOT NULL auto_increment,
  `name` varchar(255) NOT NULL,
  `type` enum('Egg','1','2','3') NOT NULL,
  `sdesc` varchar(255) NOT NULL,
  `desc` longtext NOT NULL,
  `clicks` int(3) NOT NULL,
  `family` varchar(255) NOT NULL,
  `evolve` int(11) NOT NULL COMMENT 'creature it evolves to',
  `evolvealt` int(11) NOT NULL COMMENT 'Alternate evolution',
  `chance` int(11) NOT NULL COMMENT 'Chance (Percentage) of getting the alternate',
  `rarity` int(1) NOT NULL COMMENT '1 = Common | 2 = Uncommon | 3 = Rare | 4 = Ultra-Rare',
  `planet` enum('Calona','Herosa','Viania') NOT NULL,
  `url` varchar(255) NOT NULL,
  PRIMARY KEY  (`ID`),
  KEY `evolvealt` (`evolvealt`),
  KEY `chance` (`chance`),
  KEY `family` (`family`,`planet`,`name`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=77 ;

--
-- Dumping data for table `creatures`
--

INSERT INTO `creatures` (`ID`, `name`, `type`, `sdesc`, `desc`, `clicks`, `family`, `evolve`, `evolvealt`, `chance`, `rarity`, `planet`, `url`) VALUES
(1, 'Egg', 'Egg', 'A cool and glossy bluish egg', 'These eggs are found in forests and woodland areas, normally in cooler air where snow falls. Their tough but glossy shell makes them well adapted to the colder environment; serving as camouflage against the snow and helping prevent it being eaten by predators.', 10, 'Hawn', 21, 21, 0, 2, 'Calona', 'http://calona.net/images/hawn-egg.png'),
(2, 'Egg', 'Egg', 'A strange bat like egg', 'Although Jone eggs have bat like wings, they can not fly. In cold nights the wings wrap themselves around the egg, keeping themselves warm, as well as acting as a defence against small creatures', 12, 'Jone', 16, 16, 0, 1, 'Calona', 'http://calona.net/images/jone-egg.png'),
(3, 'Egg', 'Egg', 'The egg is comfortably warm... and purring?', 'This brightly pink egg is a favourite amongst people, especially young children; who love the natural warmth it gives off and the gentle purr it often makes. They find it comforting and relaxing.', 9, 'Kitt', 14, 14, 0, 1, 'Calona', 'http://calona.net/images/kitt-egg.png'),
(4, 'Egg', 'Egg', 'An egg thats been partially eaten', 'It looks as though part of this egg has been eaten. The creature inside is slowly eating its way out. Mite eggs are often use as a source of food for many other creatures since they taste very much like cheese', 10, 'Mite', 18, 18, 0, 2, 'Calona', 'http://calona.net/images/mite_egg.png'),
(5, 'Egg', 'Egg', 'This egg is covered with a layer of slime', 'This egg is usually found in swampy areas. It is protected by a think layer of disgusting tasting and smelling slime. Because of this there are too many of these eggs about', 15, 'Swa', 20, 20, 0, 1, 'Calona', 'http://calona.net/images/swa_egg.png'),
(6, 'Egg', 'Egg', 'A floating egg with gullies', 'This egg can only be found near or just under the surface of pure clean water', 14, 'Aqua', 8, 8, 0, 3, 'Calona', 'http://calona.net/images/aqua_egg.png'),
(7, 'Egg', 'Egg', 'This egg is double to size of the others, and the time is wrong', 'Time eggs are the strangest type of egg. Though they have a clock face and hands on the outside of the shell, they are always the wrong time', 10, 'Time', 11, 11, 0, 4, 'Calona', 'http://calona.net/images/time_egg.png'),

creatures_chances

CREATE TABLE IF NOT EXISTS `creatures_chances` (
  `ID` int(1) NOT NULL,
  `rarity` varchar(10) NOT NULL COMMENT 'The rarity of the creature',
  `begin` int(2) NOT NULL,
  `end` int(3) NOT NULL,
  PRIMARY KEY  (`rarity`),
  KEY `chance` (`begin`),
  KEY `end` (`end`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `creatures_chances`
--

INSERT INTO `creatures_chances` (`ID`, `rarity`, `begin`, `end`) VALUES
(1, 'Common', 0, 60),
(3, 'Rare', 81, 94),
(4, 'Ultra-Rare', 95, 100),
(2, 'Uncommon', 61, 80);

user_access

CREATE TABLE IF NOT EXISTS `user_access` (
  `uid` int(11) NOT NULL COMMENT 'User ID',
  `cid` int(11) NOT NULL COMMENT 'Creature ID',
  PRIMARY KEY  (`uid`,`cid`),
  KEY `cid` (`cid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `user_access`
--

INSERT INTO `user_access` (`uid`, `cid`) VALUES
(1, 1),
(1, 2),
(1, 3),
(1, 4),
(1, 5),
(1, 6),
(1, 55);

--
-- Constraints for dumped tables
--

--
-- Constraints for table `user_access`
--
ALTER TABLE `user_access`
  ADD CONSTRAINT `user_access_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `users` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `user_access_ibfk_2` FOREIGN KEY (`cid`) REFERENCES `creatures` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE;

This is the sql i've gotten down to

SELECT c.ID
FROM creatures AS c
RIGHT JOIN user_access AS a ON a.cid = c.ID
LEFT JOIN creatures_chances AS cc ON cc.ID = c.rarity
WHERE c.type = 'Egg'
AND a.uid =u.ID
AND round(RAND( ) *100)
BETWEEN cc.begin
AND cc.end
LIMIT 1

It all works, except for the

AND round(RAND( ) *100)
BETWEEN cc.begin
AND cc.end

part since if i run it, no matter what r is, the creature is always common, and sometimes it comes back with 0 rows, even though theres 1 of each type available

It also won't allow me to create a foreign key for the rarity, it just gives this error

SQL query:

ALTER TABLE creatures ADD FOREIGN KEY ( rarity ) REFERENCES creatures_chances ( ID ) ON UPDATE CASCADE ON DELETE CASCADE

MySQL said: Documentation
#1005 - Can't create table './calona/#sql-16d0_f3e83.frm' (errno: 150)

Any ideas for either problems

Weedpacket

Well I did say the syntax was certainly wrong.

But to go with your new table:

CREATE TABLE IF NOT EXISTS `creatures_chances` (
  `ID` int(1) NOT NULL,
  `rarity` varchar(10) NOT NULL COMMENT 'The rarity of the creature',
  `begin` int(2) NOT NULL,
  `end` int(3) NOT NULL,
  PRIMARY KEY  (`rarity`),
  KEY `chance` (`begin`),
  KEY `end` (`end`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `creatures_chances`
--

INSERT INTO `creatures_chances` (`ID`, `rarity`, `begin`, `end`) VALUES
(1, 'Common', 0, 60),
(3, 'Rare', 81, 94),
(4, 'Ultra-Rare', 95, 100),
(2, 'Uncommon', 61, 80);

You could drop "begin" because it doesn't have any information in it that you can't get from "id" and "end", and then write

select rarity from creatures_chances where random()*100<=end order by id limit 1;

(BETWEEN won't work in cases where the random number happens to land exactly on one of the boundaries between rarities).

Desbrina

apparently according to the manual between does work on the boundaries

http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#operator_between

expr BETWEEN min AND max

If expr is greater than or equal to min and expr is less than or equal to max, BETWEEN returns 1, otherwise it returns 0. This is equivalent to the expression (min <= expr AND expr <= max) if all the arguments are of the same type.

Anyway, i'm still getting NULL values when i know there shouldn't

The new sql is

 SELECT u.ID, (
  SELECT c.ID
  FROM creatures AS c
  RIGHT JOIN user_access AS a ON a.cid = c.ID
  WHERE c.type = 'Egg'
  AND a.uid = u.ID
  AND c.rarity = (
   SELECT ID
   FROM creatures_chances
   WHERE rand( ) *100 <=
   END ORDER BY ID
   LIMIT 1 )
  LIMIT 1
  ) AS creature
FROM users u

The creature ids: 2,3,5 are common (rarity 1), 1,4 are uncommon (rarity 2), 6 is Rare (rarity 3) and 55 is Ultra-Rare (rarity 4)

There is a corresponding row in user_access for each of those creatures, so i don't know why its getting back NULL

Bjom

As a first step try enclosing all your field names and table names in ``, this way you'll make sure they are not taken for reserved words. And this makes sure your error reports are much more useful.

Also make sure your spelling takes into account upper case and lower case: FieldName is not the same as fieldname.

e.g.: END in your Query should be end

this could actually be the reason for your query failing.

Bjom

Desbrina

the new query doesn't have end in it. Can't really say much about error reports since its not throwing an error, just null values

Bjom

If the query below is the "new" query (as copied from your post) then there is a big fat END right in front of the ORDER BY statement.

Also: no matter what your query looks like and no matter if there are any "ends" in it: Do yourself a favor and ALWAYS enclose ALL of your table names and ALL of your field names in `` like so:

table1.field1

And when things don't go as planned it is advisable to be extra careful and attach the tablename in front of every fieldname (like in the example)

 SELECT u.ID, (
  SELECT c.ID
  FROM creatures AS c
  RIGHT JOIN user_access AS a ON a.cid = c.ID
  WHERE c.type = 'Egg'
  AND a.uid = u.ID
  AND c.rarity = (
   SELECT ID
   FROM creatures_chances
   WHERE rand( ) *100 <=
   END ORDER BY ID
   LIMIT 1 )
  LIMIT 1
  ) AS creature
FROM users u

hth

Bjom

Desbrina

whether the fieldnames are enclosed in `` or the table name on every field or not (and unless i'm mistaken, the only one that wasn't was on the third select) its not making any difference, i still get null values

Bjom

That's not good.

Next step would be to get rid of the WHERE statements, just to see what you get. Then if you get results at all have a good look at what the WHERE statement's parameters' values....

Desbrina

from my testing of previous taking wheres out, the problem lies with the

AND c.rarity = (
   SELECT ID
   FROM creatures_chances
   WHERE rand( ) *100 <=
   END ORDER BY ID
   LIMIT 1 )
  LIMIT 1

the type='egg' gives the correct results, as does the AND a.uid = u.ID

Bjom

SELECT ID
FROM creatures_chances
WHERE rand( ) *100 <=
END ORDER BY ID
LIMIT 1

perhaps it should be:

SELECT `ID`
FROM `creatures_chances`
WHERE rand( ) *100 <= `end`
ORDER BY `ID`
LIMIT 1

I can't believe you still refuse to get the typos out.

Desbrina

actually, i just copied it from the above post, when i tested it i had the typos out

Bjom

that's a relief 🙂

The problem is elsewhere. Inside the parentheses you reference u.ID, but that is a field from outside this query.

The bold purple part has to go.

  SELECT c.ID
  FROM creatures AS c
  RIGHT JOIN user_access AS a ON a.cid = c.ID
  WHERE c.type = 'Egg'
  [B][COLOR="Purple"]AND a.uid = u.ID[/COLOR][/B] 
   AND c.rarity = (
   SELECT `ID`
FROM `creatures_chances`
WHERE rand( ) *100 <= `end`
ORDER BY `ID`
LIMIT 1)
  LIMIT 1

something like this:

SELECT u.ID, (
  SELECT c.ID
  FROM creatures AS c
  RIGHT JOIN user_access AS a ON a.cid = c.ID
  WHERE c.type = 'Egg'
  AND c.rarity = (
   SELECT ID
   FROM creatures_chances
   WHERE rand( ) *100 <= `end`
   ORDER BY ID
   LIMIT 1 )
  LIMIT 1
  ) AS creature
FROM users u
WHERE u.ID = creature.ID

Do you use phpMyAdmin or even better MySQL Administrator for testing queries? It helps a lot.

Desbrina

thanks, i'll try it now, though i'm not 100% sure it'll work correctly in the end, since everything is part of an insert statement

i use phpMyAdmin for testing. i tried MySQL Administrator once but it was so slow i gave up in the end

The whole statement is the same select 5 times

INSERT INTO user_eggs (uid, id1, id2, id3, id4, id5)
SELECT `u`.`ID`, (
SELECT `c`.`ID`
FROM `creatures` AS `c`
RIGHT JOIN `user_access` AS `a` ON `a`.`cid` = `c`.`ID`
WHERE `c`.`type` = 'Egg'
AND `a`.`uid` = `u`.`ID`
AND `c`.`rarity` = (
SELECT `ID`
FROM `creatures_chances`
WHERE round(rand( ) *100) <=
`end` ORDER BY `ID`
LIMIT 1 )
LIMIT 1
) AS `id1`, (

SELECT `c`.`ID`
FROM `creatures` AS `c`
RIGHT JOIN `user_access` AS `a` ON `a`.`cid` = `c`.`ID`
WHERE `c`.`type` = 'Egg'
AND `a`.`uid` = `u`.`ID`
AND `c`.`rarity` = (
SELECT `ID`
FROM `creatures_chances`
WHERE round(rand( ) *100) <= `end`
ORDER BY `ID`
LIMIT 1 )
LIMIT 1
) AS `id2`,
 (

SELECT `c`.`ID`
FROM `creatures` AS `c`
RIGHT JOIN `user_access` AS `a` ON `a`.`cid` = `c`.`ID`
WHERE `c`.`type` = 'Egg'
AND `a`.`uid` = `u`.`ID`
AND `c`.`rarity` = (
SELECT `ID`
FROM `creatures_chances`
WHERE round(rand( ) *100) <= `end`
ORDER BY `ID`
LIMIT 1 )
LIMIT 1
) AS `id3`,
 (

SELECT `c`.`ID`
FROM `creatures` AS `c`
RIGHT JOIN `user_access` AS `a` ON `a`.`cid` = `c`.`ID`
WHERE `c`.`type` = 'Egg'
AND `a`.`uid` = `u`.`ID`
AND `c`.`rarity` = (
SELECT `ID`
FROM `creatures_chances`
WHERE round(rand( ) *100) <= `end`
ORDER BY `ID`
LIMIT 1 )
LIMIT 1
) AS `id4`,
 (

SELECT `c`.`ID`
FROM `creatures` AS `c`
RIGHT JOIN `user_access` AS `a` ON `a`.`cid` = `c`.`ID`
WHERE `c`.`type` = 'Egg'
AND `a`.`uid` = `u`.`ID`
AND `c`.`rarity` = (
SELECT `ID`
FROM `creatures_chances`
WHERE round(rand( ) *100) <= `end`
ORDER BY `ID`
LIMIT 1 )
LIMIT 1
) AS `id5`
FROM `users` `u`

Edit: tried the sql and it doesn't work

#1054 - Unknown column 'creature.ID' in 'where clause'

Bjom

I should have read your original query more carefully. I believe this whill do what you need:

  SELECT `user_access`.`uid` AS `ID`
  FROM `creatures`
      RIGHT JOIN `user_access` 
          ON `user_access`.`cid`  = `creatures`.`ID`
  WHERE `creatures`.`type` = 'Egg'
      AND `creatures`.`rarity` = (
         SELECT `ID`
         FROM `creatures_chances`
         WHERE rand( ) * 100 <= `end`
         ORDER BY `ID`
         LIMIT 1 
         )
  LIMIT 1

Desbrina

doesn't seem to

What i'm trying to do is for every user select 5 eggs from the creatures table that they have access to (user_access). The rarer the egg, the less likly it is to be picked

Yours is selecting the user id not the egg id

Bjom

Your "new sql" was ending up selecting a user id, so that's what i thought you needed. the query I provided can give you anything, just change the subject of the first SELECT.

SELECT creatures.ID

any better?