Help with calculations.

-Karl-

Okay.

I have a database called firemaking for a MMORPG called RuneScape.

This holds the information for the log type, ID, experience, and level required.

What I'm trying to create is a calculator which will tell the user how many logs they require of each time to achieve their goal.

The script will get their current experience by using their username and grabbing the data from http://hiscore.runescape.com/index_lite.ws?player=

After the player= is the players username.

What I want to do is grab the experience, then using that, subtract it from their desired experience or level. Then divide that information by the experience per log (this information is stored in the database).

The problem is, I'm not quite sure the best route to do this as there's a lot of records in the database. I won't just be doing this for firemaking, but for each of the skills in the game.

Any ideas would be welcome.

Also, you can view an example of what I want to create here:
http://www.zybez.net/calcs.php?calc=Firemaking
http://www.runehq.com/guide.php?type=calculator&id=0370
http://www.runehq.com/runekit/skillscalculator_site.php?s=Firemaking

Here's what I have done so far, I'm just not quite sure how to make it work with the database, rather than masses of variables.

<html>
<head>
<title>Experience</title>
</head>
<body>

<form method="post" action="test.php">
  Current Exp: <input name="current" type="text" /><br />
  Wanted Exp: <input name="wanted" type="text" /><br />
  <input type="submit" name="submit"/>
</form>


</body>
</html>

<?php
if (isset($_POST['submit'])) {

  $current = $_REQUEST['current'];
  $wanted = $_REQUEST['wanted'];
  $maple = 45;
  $yew = 60;
  $magic = 75;

     echo <<<HTML
<table border="1" cellpadding="5">
<th>Log Type</th><th>Experience Per Log</th><th>Logs Needed</th>
<tr><td>Maple</td><td>$maple</td><td>
HTML;
echo ceil("$wanted - $current" / $maple);
         echo <<<HTML
</td></tr>
<tr><td>Yew</td><td>$yew</td><td>
HTML;
echo ceil("$wanted - $current" / $yew);
         echo <<<HTML
</td></tr>
<tr><td>Magic</td><td>$magic</td><td>
HTML;
echo ceil("$wanted - $current" / $magic);
         echo <<<HTML
</td></tr>
</table>
<br />
Thank you for using our Firemaking Calculator
HTML;

}
?>

johanafm

I'd keep three tables. player, skills and playerSkills. playerSkills is just a link table with (player_id, skill_id, exp), primary key (player_id, skill_id).
That way you can easily grab all skills beloning to one player in one single query. It's equally easy to get all players with a certain skill for top lists etc. With proper indexes, there should be no performance issues with this query. And since primary keys are always indexed, you are all set in this regard (assuming ofc that id in player and skill are also primary key in the respective tables.

-Karl-

The players information isn't in the database.

The information for the player is here:
http://hiscore.runescape.com/index_lite.ws&player=

The information on the skills is in the database.

johanafm

Well, in that case, are player names unique? If so, drop the player table from my suggestion and you're set. The playerSkills table remain and player_id is in fact the character's name.

-Karl-

johanafm;10929872 wrote:
Well, in that case, are player names unique? If so, drop the player table from my suggestion and you're set. The playerSkills table remain and player_id is in fact the character's name.

I don't think you quite understand what I'm trying to create, take a look at the examples I've posted.

johanafm

I actually think I do, but let's find out.

All skills have the same experience required per level.
Any player can learn any skills, and they can have 0 or more experience in any given skill.
Doing stuff gives experience, regardless of how much you currently have.

However, since you claimed that for example exp/log is stored in the db, I left that part out previously.

Table: skill
id	name
1	Firemaking
2	Attack
3	Bunny domestication


Table stuff
id	name						exp	skill
1	twig						1	1
2	redwood tree					100	1
3	cuddly bunny					25	3
4	hitchhiking zombie bunny from outer space	300	3
5	dog						2	2

table: player
id	name
1	Aargle
2	Bargle

table: playerSkill
player	skill		exp
1	1		30
1	2		40
1	3		25
2	1		10
2	2		0
2	3		5

table: level
id	exp
1	0
2	10
3	25
4	100

And now, let's say player 1, Aargle, wants to see info for raising bunny domestication to level 4.

SELECT exp FROM level WHERE id = 4;
-- Now we know it's 100. If these values are unlikely to change,
-- you could also just stick them in your php code: array(0, 10, 25, 100);

SELECT name FROM skill where name = 'Bunny domestication';
-- Now we know that skill id is 3

SELECT exp
FROM playerSkill
WHERE player = 1 AND skill = 3;
-- Now we know Aargle has 25 exp in bunny domestication

SELECT name, exp FROM stuff WHERE skill = 3;
-- We now know that there are two things that increase bunny domestication:
-- cuddly bunny and hitchhiking zombie bunny from outer space.
-- They give 25 and 300 exp respectively

100 - 25 = 75
75 / 25 => 3
75 / 300 => 1

Aargle requires 75 exp to reach level 4.
Cuddly bunny	3
Longname bunny	1

-Karl-

table: player
id	name
1	Aargle
2	Bargle

table: playerSkill
player	skill		exp
1	1		30
1	2		40
1	3		25
2	1		10
2	2		0
2	3		5

There is no table for that information, the information is dragged from the games hiscores. It's not stored in a database at my end, hence I showed you the link.

For example, if the players name is "Zezima", you'd take this URL.
http://hiscore.runescape.com/index_lite.ws?player=
and change it to
http://hiscore.runescape.com/index_lite.ws?player=zezima

If you try loading that, it works and shows "381,2372,1224767936 413,99,59476460 235,99,56616722 1173,99,39565202 198,99,61287324 224,99,37119213 448,99,14155051 24,99,63827235 5,99,200000000 1915,99,22203111 55,99,113793712 491,99,27239316 25,99,117368919 235,99,18094553 160,99,17565654 585,99,15789879 7,99,57473823 1266,99,13592655 5,99,200000000 555,99,18641464 365,99,19458833 678,99,16113397 6866,99,13187289 606,99,13229510 5172,95,8968614 2539,1944 -1,-1 -1,-1 -1,-1 -1,-1 "

That's the data for the character, their current levels and experience. But of course, I don't expect you to know which one is for firemaking.
The 117368919 is the persons current firemaking experience.

johanafm

Then you just skip those two tables and parse the string at that url. It doesn't change anything when it comes to program logic.