I am working on a little browsergame project written in PHP and using PostgreSQL as DBMS. Now I'm not really lucky with the process started after a userlogin was successful.
Some info:
- There are 3 different kinds of properties a game character can have:
- Attributes
- Skills
- Talents
- Each of these properties is a table in my database
- Each of these properties is related to the character table in an extra table
- After the login was successful I want to store both general information about these properties and the character-related values of them in the session (the first in 'game' and the second in 'user').
How I currently get the data:
[...]
$this->getIngameInfo();
//one account can have up to 4 characters
//each of the characters can have different values
foreach($_SESSION['user']['character'] as $key => $data){
$_SESSION['user']['character'][$key]['attribute'] = $this->getAttributes($data['id']);
$_SESSION['user']['character'][$key]['skill'] = $this->getSkills($data['id']);
$_SESSION['user']['character'][$key]['talent'] = $this->getTalents($data['id']);
}
[...]
private function getIngameInfo(){
$sql = "SELECT id,
name,
tag,
description
FROM attribute";
if($this->db->query($sql, array())){
while($row = $this->db->fetchAssoc()){
$_SESSION['game']['attribute'][] = $row;
}
}
$sql = "SELECT id,
name,
tag,
description
FROM skill";
if($this->db->query($sql, array())){
while($row = $this->db->fetchAssoc()){
$_SESSION['game']['skill'][] = $row;
}
}
$sql = "SELECT id,
name,
description
FROM talent";
if($this->db->query($sql, array())){
while($row = $this->db->fetchAssoc()){
$_SESSION['game']['talent'][] = $row;
}
}
}
private function getAttributes($charid){
$sql = "
SELECT attributeid,
value
FROM character_attribute
WHERE characterid = $1
ORDER BY attributeid ASC
";
$attributes = array();
if($this->db->query($sql, array($charid))){
while($row = $this->db->fetchAssoc()){
$attributes[] = $row;
}
}
return $attributes;
}
private function getSkills($charid){
$sql = "
SELECT skillid,
value
FROM character_skill
WHERE characterid = $1
ORDER BY skillid ASC
";
$skills = array();
if($this->db->query($sql, array($charid))){
while($row = $this->db->fetchAssoc()){
$skills[] = $row;
}
}
return $skills;
}
private function getTalents($charid){
$sql = "
SELECT talentid,
value
FROM character_talent
WHERE characterid = $1
ORDER BY talentid ASC
";
$talents = array();
if($this->db->query($sql, array($charid))){
while($row = $this->db->fetchAssoc()){
$talents[] = $row;
}
}
return $talents;
}
I now wonder how I could merge these quite similar queries, because I'll need to fetch more information after that and I don't like firing so much queries in one process.
I thought about using prepared statements (I use a self-written pgsql-PDO-class), but I am not calling the same table multiple times (and table 'talent' does not have exactly the same columns as the other both).
I also mentioned creating one or two stored procedures which return all the needed data. But in this case I would not know how to assign such a bunch of data to the different named sessionarrays.
The methods shown belong to a loginmodel and are called only one time. I used the sessionarray because the properties of a character should be shown in different ways (which would lead to caching) and used for calculations in different ways. As I don't like firing queries against the db to calculate with values that maybe did not change I didn't see a real alternative to sessions.
Think about that:
- Fetch character properties once after login
- Depending on user's interactions, show (cached if not changed) or calculate (? if not changed) with these properties
- Depending on user's interactions, change these properties, update db and update session
TODO:
- Encapsulate sessiondata in another model
- Use prepared queries for
getAttributes,getSkillsandgetTalents - Sum them to one method
- Move it to another model, as it will be not only needed when logging in, but when chars interact with other chars (wasn't away of)
I would like to know how I can reduce the queries and simplify the code/improve performance of the script.
