I am trying to extract some data from a MYSQL table to build it into an array. Below is the code i wrote to do this (rather C++ based):
GetData($serverid, $medalhistorydateyear, $medalhistorydatemonth) {
// search through the list of medals in stats_consolidated and make a new entry for each medal.
// make medal name list
$wherequery = " WHERE NAME LIKE 'medal_%' AND SERVERID = '-1'";
$result = DB_Query("SELECT NAME FROM " . stats_consolidated . $wherequery);
$medalname_list = DB_GetAllRows($result, true);
$medal_count = DB_GetRowCount("SELECT NAME FROM " . stats_consolidated . $wherequery);
// make server id list
$wherequery = " WHERE NAME = '" . $medalname_list[0][NAME] . "'";
$result = DB_Query("SELECT SERVERID FROM " . stats_consolidated . $wherequery);
$serverid_list = DB_GetAllRows($result, true);
$server_count = DB_GetRowCount("SELECT SERVERID FROM " . stats_consolidated . $wherequery);
// make an entry for each server for each medal on the medalname_list
$medal_data_count = 0;
for($i=0;$i<$medal_count;$i++) {
for($j=0;$j<$server_count;$j++) {
$tempmedalname = $medalname_list[$i][NAME];
$tempserverid = $serverid_list[$j][SERVERID];
$wherequery = " WHERE NAME = '" . $tempmedalname . "' AND SERVERID = '" . $tempserverid . "'";
$result = DB_Query("SELECT NAME, SERVERID, SortID, DisplayName, DescriptionID, VALUE_INT, VALUE_TXT, PLAYER_ID FROM " . stats_consolidated . $wherequery);
$medal_data_temp = DB_GetAllRows($result, true);
$medal_data[$medal_data_count] = $medal_data_temp[0];
$medal_data_count++;
}
}
for($k=0;$k<$medal_data_count;$k++) {
PrintHTMLDebugInfo( DEBUG_DEBUG, "test", "test data: '" . $medal_data[$k][NAME] . "' : '" . $medal_data[$k][SERVERID] . "' : '" . $medal_data[$k][SortID] . "' : '" . $medal_data[$k][DisplayName] . "' : '" . $medal_data[$k][DescriptionID] . "' : '" . $medal_data[$k][VALUE_INT] . "' : '" . $medal_data[$k][VALUE_TXT] . "' : '" . $medal_data[$k][PLAYER_ID] . "'");
}
}
The called functions that related to the database calls are below (these where not made by me):
function DB_Query($query_string, $bProcessError = true, $bCritical = false)
{
global $link_id, $querycount;
$query_id = mysql_query($query_string,$link_id);
if (!$query_id && $bProcessError)
DB_PrintError("Invalid SQL: ".$query_string, $bCritical);
// For the Stats ;)
$querycount++;
return $query_id;
}
function DB_GetAllRows($query_id, $bClose)
{
if ($query_id != false && $query_id != 1 )
{
while ($row = mysql_fetch_array($query_id, MYSQL_ASSOC))
$var[] = $row;
if ( $bClose )
DB_FreeQuery ($query_id);
if ( isset($var) )
{
// Return array
return $var;
}
else
return;
}
}
function DB_FreeQuery($query_id)
{
if ($query_id != false && $query_id != 1 )
mysql_free_result($query_id);
}
function DB_GetRowCount($query)
{
// Init num rows
$num_rows = -1;
if ($result = mysql_query($query))
{
$num_rows = mysql_num_rows($result);
mysql_free_result ($result);
}
return $num_rows;
}
The table is shown below (again not made by me, im confused why there are so many primary keys, unless they are being combined to form one big primary key?):
[quote="table: stats_consolidated"]
-- Table structure for table stats_consolidated
CREATE TABLE IF NOT EXISTS stats_consolidated (
ID int(11) NOT NULL auto_increment,
NAME varchar(63) NOT NULL default '',
SERVERID tinyint(4) NOT NULL default '0',
Time_Year mediumint(9) NOT NULL default '0',
Time_Month tinyint(4) NOT NULL default '0',
SortID int(11) NOT NULL default '0',
DisplayName varchar(255) NOT NULL default '',
DescriptionID varchar(255) NOT NULL default '0',
VALUE_INT int(11) NOT NULL default '0',
VALUE_TXT varchar(255) NOT NULL default '',
PLAYER_ID int(11) unsigned NOT NULL default '0',
PRIMARY KEY (ID,NAME,SERVERID,Time_Year,Time_Month)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Table to hold total values and medals' AUTO_INCREMENT=204 ;
--
-- Dumping data for table stats_consolidated
INSERT INTO stats_consolidated (ID, NAME, SERVERID, Time_Year, Time_Month, SortID, DisplayName, DescriptionID, VALUE_INT, VALUE_TXT, PLAYER_ID) VALUES
(169, 'medal_pro_pistol', -1, 0, 0, 5, 'Pistol', 'medal_pro_pistol', 12, 'Kills', 1272946142),
(168, 'medal_pro_sniper', -1, 0, 0, 4, 'Sniper', 'medal_pro_sniper', 16, 'Kills', 1272946142),
(167, 'medal_pro_knifekills', -1, 0, 0, 3, 'Knife Kills', 'medal_pro_knifekills', 7, 'Kills', 2791352847),
(166, 'medal_pro_explosivekiller', -1, 0, 0, 2, 'Explosive Killer', 'medal_pro_explosivekiller', 9, 'Kills', 1092673307),
(196, 'medal_anti_whiner', 1, 0, 0, 4, 'Whiner', 'medal_anti_whiner', 2, 'Whining chats', 503140616),
(195, 'medal_anti_nademagnet', 1, 0, 0, 3, 'Nade Magnet', 'medal_anti_nademagnet', 8, 'Deaths', 1234669041),
(165, 'medal_pro_headshot', -1, 0, 0, 1, 'Headshot', 'medal_pro_headshot', 14, 'Kills', 1234669041),
(164, 'medal_pro_killer', -1, 0, 0, 0, 'Killer', 'medal_pro_killer', 89, 'Kills', 1234669041),
[/quote]
It returns all fields bar the PLAYER_ID field which just returns blank, but i dont know why. If i run the same query directly into the SQL box on PHP MyAdmin then it does return the PLAYER_ID field, so is something about the functions i've used or some other setting i guess 😕
Output in website from PrintHTMLDebugInfo function loop wrote:
test data: 'medal_pro_explosivekiller' : '-1' : '2' : 'Explosive Killer' : 'medal_pro_explosivekiller' : '9' : 'Kills' : ''
test data: 'medal_pro_explosivekiller' : '1' : '2' : 'Explosive Killer' : 'medal_pro_explosivekiller' : '9' : 'Kills' : ''
test data: 'medal_pro_knifekills' : '-1' : '3' : 'Knife Kills' : 'medal_pro_knifekills' : '7' : 'Kills' : ''
test data: 'medal_pro_knifekills' : '1' : '3' : 'Knife Kills' : 'medal_pro_knifekills' : '7' : 'Kills' : ''
