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 succesful.
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 similiar 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.
So I would like to know:
- how to reduce the queries
- simplify the php-code/improve performance of the script
If there are other things I could improve in the code (oop-related or general), please let me know.
Thanks.
edit:
Sorry that I was very focused on the particular problem I've seen and didn't mention/point out some more facts. 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
Maybe someone has an idea for the ?.
Related to the very useful answers I will change my code to match the named principles.
TODO:
- encapsulate sessiondata in another model
- use prepared queries for "getAttributes", "getSkills" and "getTalents"
- & 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)
