i am building a ranked user list using these fields in these tables:
wallet:
id INT NOT NULL PRIMARY KEY
overall_value decimal(12,2)
login_info:
id INT NOT NULL PRIMARY KEY auto_increment
username varchar(32)
weapon_stash:
id INT NOT NULL PRIMARY KEY
name varchar(32)
quantity INT
weapon_prices:
name varchar(32)
strength INT
the id's in weapon_stash and wallet are foreign keys the login_info table
ok, so here is how i do it now:
************
$result = mysql_query("SELECT id, overall_value FROM wallet ORDER BY overall_value DESC");
for($rank = $startnum; list($id, $overall_value) = mysql_fetch_row($result); $rank++) {
$result2 = mysql_query("SELECT username FROM login_info WHERE id='".$id."'");
list($username) = mysql_fetch_row($result2);
$result2 = mysql_query("SELECT sum(quantity * strength) FROM weapon_stash LEFT JOIN weapon_prices using (name) WHERE id='".$id."'");
list($weapon_strength) = mysql_fetch_row($result2);
$weapon_strength = ($weapon_strength) ? $weapon_strength : 0;
}
*********
i just cut the code out and cut out any of the stuff i didn't think was useful to answering my question, so if you see an error, ignore it, the code works.
NOW THE PROBLEM, every iteration i am joining the weapons table and calculating their strength (number of weapons times it strength)... this is a lot of overhead and i don't think it is neccessary.
i would like to do this all in *1* query, and then just iterate through the result set. is that possible with mysql?
i would like a ranked list by overall value with their username and weapon strength. people may have many entries in the weapon_stash table for the different weapons.
thanks!!
mike
wallet:
id INT NOT NULL PRIMARY KEY
overall_value decimal(12,2)
login_info:
id INT NOT NULL PRIMARY KEY auto_increment
username varchar(32)
weapon_stash:
id INT NOT NULL PRIMARY KEY
name varchar(32)
quantity INT
weapon_prices:
name varchar(32)
strength INT
the id's in weapon_stash and wallet are foreign keys the login_info table
ok, so here is how i do it now:
************
$result = mysql_query("SELECT id, overall_value FROM wallet ORDER BY overall_value DESC");
for($rank = $startnum; list($id, $overall_value) = mysql_fetch_row($result); $rank++) {
$result2 = mysql_query("SELECT username FROM login_info WHERE id='".$id."'");
list($username) = mysql_fetch_row($result2);
$result2 = mysql_query("SELECT sum(quantity * strength) FROM weapon_stash LEFT JOIN weapon_prices using (name) WHERE id='".$id."'");
list($weapon_strength) = mysql_fetch_row($result2);
$weapon_strength = ($weapon_strength) ? $weapon_strength : 0;
}
*********
i just cut the code out and cut out any of the stuff i didn't think was useful to answering my question, so if you see an error, ignore it, the code works.
NOW THE PROBLEM, every iteration i am joining the weapons table and calculating their strength (number of weapons times it strength)... this is a lot of overhead and i don't think it is neccessary.
i would like to do this all in *1* query, and then just iterate through the result set. is that possible with mysql?
i would like a ranked list by overall value with their username and weapon strength. people may have many entries in the weapon_stash table for the different weapons.
thanks!!
mike