specific rows

[Brian W]

Context: I am trying to set up an online based RPG, Players have characters, characters have skills. Each skill has 5 levels. Not all character types have the same skills. The *Skill* table looks like this (Example is the skill "Sneak")

|ID|NAME |Rank1                        |Rank2|Rank3              |Rank4                        |Rank4                        |Rank5                      |

|23|Sneak|50% to not be detected|55% to not be detected|60% to not be detected|65% to not be detected|70% to not be detected|

 

The *Character* table looks like this (example is a Thief)

|ID|Player    |Character        |Type|Skill1|Skill2|Skill3|

|3 |John Doe|Smooth Criminal|Thief|23,1  |5,1 |5,1  |

 

His skill1 is Sneak, I want it to refer over to my Skill table and display Rank1 column of that ability without making a quarry for each individual Skill. I know this isn't going to work, but below is kinda the logic I am on (which is why I'm asking for help)

<?php //my quarry of characters WHERE ID = 3, so far so good
//my quarry of my skills table.
//my table to display the info in below
|Player name     | John Doe         |
|Character Name| Smooth Criminal|
//ect
<?php $Skill = explode(",",$row_character['Skill 1']) ?>
//Some how get that or something like that to this...
|Slill1| Sneak | 50% to not be detected |

[Adam]

23,1  |5,1 |5,1

 

I assume the ",1" after each is the rank achieved? Would make it easier if you perhaps used:

 

Skill 1 | Skill 1 Rank | Skill 2 | Skill 2 Rank | etc.

 

..as the fields?

 

Then you could go like...

 

SELECT * FROM Skill, Character AS b
WHERE ID = b.Skill1 || ID = b.Skill2 || ID = b.Skill3

 

(not tested) .. but that should return their 3 skills...

[Brian W]

Yes, splitting them up is fine too... I can make them separate fields.

Sorry, I'm new, I don't understand this

SELECT * FROM Skill, Character AS b
WHERE ID = b.Skill1 || ID = b.Skill2 || ID = b.Skill3

what is b? how is the . after b work? That just blew my mind. lol

So I quarry both tables? how does that relate the character's Skill1 with the proper skill? Thanks.

[Adam]

Ah sorry, misread what you said. I got the impression you were wanting the skill definitions for a certain character's skills.. like all 3 of them..

 

If you just wank Skill1's details, could use something like:

 

SELECT * FROM Skill, Character
WHERE Skill.ID = Character.Skill1

 

The previous query I showed you before is actually quite simple:

 

SELECT * FROM Skill, Character AS b

 

That just means select everything from Skill, and basically set "b" as a kind of variable for the Character table, so instead of having to go:

 

WHERE Skill.ID = Character.Skill1 || Skill.ID = Character.Skill2 || Skill.ID = Character.Skill3

 

can just use:

 

WHERE ID = b.Skill1 || ID = b.Skill2 || ID = b.Skill3

 

And obviouslly that's just finding skill records where the skill ID matches that in either skill slot 1, 2 or 3 of the character...

 

 

Did I describe that well :S

 

Adam

[Adam]

Oh need to use "OR" not "||"

 

Also I tested the query and it fails, I reckon it's because "character" has a meaning within mysql and it's causing an error in the SQL syntax using it as a table name. I renamed it to "players" and the messed with the query a little bit and came up with the following...

 

This will return the 3 skill records for character #'1'..

SELECT * FROM Skill AS a, Players AS b
WHERE (a.ID = b.Skill1 OR a.ID = b.Skill2 OR a.ID = b.Skill3)
AND b.ID = '1'

 

A typical return may look like:

id name id player character         type skill1 skill2 skill3

1 Sneak 1 John Doe Smooth Criminal Thief 1 2 3

2 Evade        1 John Doe Smooth Criminal Thief 1 2 3

3 Fight         1 John Doe Smooth Criminal Thief 1 2 3

 

 

 

This will return the skill info in skill slot 1 for character #'1'..

SELECT * FROM Skill AS a, Players AS b
WHERE a.ID = b.Skill1
AND b.ID = '1'

 

A typical return may look like:

id name id player character type skill1 skill2 skill3

1 Sneak 1 John Doe Smooth Criminal Thief 1 2 3

 

--------------

 

The bottom line in each query sets the character in question in the query to '1'. You could very easily replace the 1 with a PHP variable such as the user's session id or a GET value sent in the URL like.. "skillInfo.php?id=##" ..

 

Does that all make sense?

 

Adam