PHP, MySQL, and resources

[Salvatos]

Hello
As some may remember, I am making a PHP RPG.
I am currently about to add a complete skill system to allow special attacks during battles. While I had already implemented a few special attacks, I need to make a standard and complete system to manage them all.

I know pretty much how I want to do this, but I am concerned about resource usage. I'll try to explain simply.

1. Players can buy skills depending on their level and the number of skill points they have.
2. Each skill can be used once per battle (excluding permanent abilities, which are simply always active).
3. Some skills allow additional uses to be purchased/learn.

So the part of my system that leaves me pondering is the part where I check
1. If the skill has already been used and
2. If it can still be used (if the player has purchased more than one usage per battle).

I have thought of two possibilities to do this, but neither seems efficient to me.
The first is to use my battle SQL table and add to it a column for each skill, telling how many times it has been used in that particular battle. The problem is that if I end up with, say, 20 skills in total, I will need to add 40 columns to a table which is already about that size (20 for each player taking part in the battle). As far as I know (but hopefully I could be wrong), that will slow the queries made to that table a lot, which should always be avoided.

My second option was thought of to avoid overloading the database, but seems awkward to me. I thought I could set variables for each player and each skill, and send them from page to page (my battles are turn-based and offer an attack option to the player every turn) with hidden form inputs. But, using my previous example, I wonder if sending 40 hidden inputs on each page load actually makes sense.

So my question to you experts is in two parts:
1. If either of these options is efficient enough to be used, which would the best be?
2. Can you figure out a better way to create, store/send and use that information? I thought an array could make it easier to use the second option, but I don't know enough about them yet to tell if it would solve the problem.

Thanks in advance! And as a side note, I'm not looking for code, I really just want your advice on how to better use my resource.

 

[Nahid_hossain]

I think second option is not very secured. Try never to use it. Users of your script may messes everything up, if they have some knowledge about web programming.

I think first option is more reliable. If you think the performance will be slower then you can use the following trick I use often:

store skill in one column

example:

id...................... name ....................... value

1....................... skills ........................ skill1-skill2-skill3-skill4-skill5


How will you retrieve the values?

after sql query let your variable $var[0]= skill1-skill2-skill3-skill4-skill5

then

Now

$skl=explode("-", $var[0]);

Now your skill values are separated:

$skl[0]=skill1;
$skl[1]=skill2;
$skl[2]=skill3;
$skl[3]=skill4;
$skl[4]=skill5;

It will take lesser time than before

 

[Salvatos]

Your method may make things a bit complicated because of the way I work, but I'll definitely take it into consideration; it's a clever trick that can turn in useful later even if I do not use it for this in particular. Thanks!

If others have ideas, please go ahead...

 

[xmakina]

PHP Sessions are your friend here.

As for storing the skills information, just use a Weak Link table that joins skill IDs to user IDs

 

[Salvatos]

PHP sessions... Why didn't I just think about that way earlier ^^'

With this I won't need to store any information, the basics are already in place (skill info, which skills each player has, etc.). Though you got me curious and Google doesn't seem to have an easy answer - what are weak link tables?

 

[natsuki]

a foreign key that is a primary key from another table. Make a separate skills table and then add a foreign key to it that references to your players. Or the other way around can be done too.

SELECT u.userID, u.username, s.skill, s.use FROM users u INNER JOIN skills s 
ON u.userID = s.userID GROUP BY u.userid ORDER BY u.username ASC;

something like that or whatever..

As for temporary battle data, yeah sessions will be useful.

 

[xmakina]

No. That's a relationship.

It's only valid when 1-to-1.

Player 1 has sheild 3 in his hand.

Many-to-many is a table made from foreign keys.

Player 1 has skill 1, skill 2 and skill 3.
Player 2 has skill 3 and 4.


It would hold the columns PlayerID and SkillID

|PlayerID | SkillID |
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 3 |
| 2 | 4 |


A quick google returned this: http://www.tomjewett.com/dbdesign/dbdesign.php?page=manymany.php

 

[Salvatos]

Oh it actually looks a lot like what I'm doing. I got the idea from phpBB's database. They have a poll table holding which user has voted for which poll with which option, looks like this:
user_id | poll_id | vote
......5...... | .....2..... | ...1...

So I use the same for my inventory and (learnt) skills table, probably a couple others too. I don't know if I'm doing it like I should though, I just set the user_id as a no-auto-increment key and I fetch my player's id from the users table to set the user_id in the other tables.