Index: char_list.php =================================================================== --- char_list.php (revision 490) +++ char_list.php (working copy) @@ -65,10 +65,8 @@ $where_out .= ") "; unset($result); - $sql_query = "SELECT guid,name,account,race,class,zone,map, - CAST( SUBSTRING_INDEX(SUBSTRING_INDEX(`data`, ' ', ".(CHAR_DATA_OFFSET_HONOR_KILL+1)."), ' ', -1) AS UNSIGNED) AS highest_rank, - online, level, gender, logout_time, - CAST( SUBSTRING_INDEX(SUBSTRING_INDEX(`data`, ' ', ".(CHAR_DATA_OFFSET_GUILD_ID+1)."), ' ', -1) AS UNSIGNED) as gname FROM `characters` + $sql_query = "SELECT guid,name,account,race,class,zone,map,totalKills AS highest_rank, + online, level, gender, logout_time FROM `characters` WHERE $where_out ORDER BY $order_by $order_dir LIMIT $start, $itemperpage"; break; @@ -76,10 +74,8 @@ if (is_numeric($search_value)); else $search_value = 1; $where_out ="level = $search_value"; - $sql_query = "SELECT guid,name,account,race,class,zone,map, - CAST( SUBSTRING_INDEX(SUBSTRING_INDEX(`data`, ' ', ".(CHAR_DATA_OFFSET_HONOR_KILL+1)."), ' ', -1) AS UNSIGNED) AS highest_rank, - online, level, gender, logout_time, - CAST( SUBSTRING_INDEX(SUBSTRING_INDEX(`data`, ' ', ".(CHAR_DATA_OFFSET_GUILD_ID+1)."), ' ', -1) AS UNSIGNED) as gname FROM `characters` + $sql_query = "SELECT guid,name,account,race,class,zone,map,totalKills AS highest_rank, + online, level, gender, logout_time FROM `characters` WHERE $where_out ORDER BY $order_by $order_dir LIMIT $start, $itemperpage"; break; @@ -87,10 +83,8 @@ if (is_numeric($search_value)); else $search_value = 1; $where_out ="level > $search_value"; - $sql_query = "SELECT guid,name,account,race,class,zone,map, - CAST( SUBSTRING_INDEX(SUBSTRING_INDEX(`data`, ' ', ".(CHAR_DATA_OFFSET_HONOR_KILL+1)."), ' ', -1) AS UNSIGNED) AS highest_rank, - online, level, gender, logout_time, - CAST( SUBSTRING_INDEX(SUBSTRING_INDEX(`data`, ' ', ".(CHAR_DATA_OFFSET_GUILD_ID+1)."), ' ', -1) AS UNSIGNED) as gname FROM `characters` + $sql_query = "SELECT guid,name,account,race,class,zone,map,totalKills AS highest_rank, + online, level, gender, logout_time FROM `characters` WHERE $where_out ORDER BY 'level' $order_dir LIMIT $start, $itemperpage"; break; @@ -98,10 +92,8 @@ if (is_numeric($search_value)); else $search_value = 1; $where_out ="money > $search_value"; - $sql_query = "SELECT guid,name,account,race,class,zone,map, - CAST( SUBSTRING_INDEX(SUBSTRING_INDEX(`data`, ' ', ".(CHAR_DATA_OFFSET_HONOR_KILL+1)."), ' ', -1) AS UNSIGNED) AS highest_rank, - online, level, gender, logout_time, - CAST( SUBSTRING_INDEX(SUBSTRING_INDEX(`data`, ' ', ".(CHAR_DATA_OFFSET_GUILD_ID+1)."), ' ', -1) AS UNSIGNED) as gname FROM `characters` + $sql_query = "SELECT guid,name,account,race,class,zone,map,totalKills AS highest_rank, + online, level, gender, logout_time FROM `characters` WHERE $where_out ORDER BY $order_by $order_dir LIMIT $start, $itemperpage"; break; @@ -127,10 +119,8 @@ $where_out .= ") "; unset($result); - $sql_query = "SELECT guid,name,account,race,class,zone,map, - CAST( SUBSTRING_INDEX(SUBSTRING_INDEX(`data`, ' ', ".(CHAR_DATA_OFFSET_HONOR_KILL+1)."), ' ', -1) AS UNSIGNED) AS highest_rank, - online, level, gender, logout_time, - CAST( SUBSTRING_INDEX(SUBSTRING_INDEX(`data`, ' ', ".(CHAR_DATA_OFFSET_GUILD_ID+1)."), ' ', -1) AS UNSIGNED) as gname FROM `characters` + $sql_query = "SELECT guid,name,account,race,class,zone,map,totalKills AS highest_rank, + online, level, gender, logout_time FROM `characters` WHERE $where_out ORDER BY $order_by $order_dir LIMIT $start, $itemperpage"; break; @@ -147,32 +137,26 @@ $where_out .= ") "; unset($result); - $sql_query = "SELECT guid,name,account,race,class,zone,map, - CAST( SUBSTRING_INDEX(SUBSTRING_INDEX(`data`, ' ', ".(CHAR_DATA_OFFSET_HONOR_KILL+1)."), ' ', -1) AS UNSIGNED) AS highest_rank, - online, level, gender, logout_time, - CAST( SUBSTRING_INDEX(SUBSTRING_INDEX(`data`, ' ', ".(CHAR_DATA_OFFSET_GUILD_ID+1)."), ' ', -1) AS UNSIGNED) as gname FROM `characters` + $sql_query = "SELECT guid,name,account,race,class,zone,map,totalKills AS highest_rank, + online, level, gender, logout_time FROM `characters` WHERE $where_out ORDER BY $order_by $order_dir LIMIT $start, $itemperpage"; break; case "greater_rank": if (is_numeric($search_value)); else $search_value = 0; - $where_out ="SUBSTRING_INDEX(SUBSTRING_INDEX(`data`, ' ', ".(CHAR_DATA_OFFSET_HONOR_KILL+1)."), ' ', -1) > $search_value"; + $where_out ="totalKills > $search_value"; - $sql_query = "SELECT guid,name,account,race,class,zone,map, - CAST( SUBSTRING_INDEX(SUBSTRING_INDEX(`data`, ' ', ".(CHAR_DATA_OFFSET_HONOR_KILL+1)."), ' ', -1) AS UNSIGNED) AS highest_rank, - online, level, gender, logout_time, - CAST( SUBSTRING_INDEX(SUBSTRING_INDEX(`data`, ' ', ".(CHAR_DATA_OFFSET_GUILD_ID+1)."), ' ', -1) AS UNSIGNED) as gname FROM `characters` + $sql_query = "SELECT guid,name,account,race,class,zone,map,totalKills AS highest_rank, + online, level, gender, logout_time FROM `characters` WHERE $where_out ORDER BY 'highest_rank' $order_dir LIMIT $start, $itemperpage"; break; case "highest_rank": if (is_numeric($search_value)); else $search_value = 0; - $where_out ="SUBSTRING_INDEX(SUBSTRING_INDEX(`data`, ' ', ".(CHAR_DATA_OFFSET_HONOR_KILL+1)."), ' ', -1) = $search_value"; + $where_out ="totalKills = $search_value"; - $sql_query = "SELECT guid,name,account,race,class,zone,map, - CAST( SUBSTRING_INDEX(SUBSTRING_INDEX(`data`, ' ', ".(CHAR_DATA_OFFSET_HONOR_KILL+1)."), ' ', -1) AS UNSIGNED) AS highest_rank, - online, level, gender, logout_time, - CAST( SUBSTRING_INDEX(SUBSTRING_INDEX(`data`, ' ', ".(CHAR_DATA_OFFSET_GUILD_ID+1)."), ' ', -1) AS UNSIGNED) as gname FROM `characters` + $sql_query = "SELECT guid,name,account,race,class,zone,map,totalKills AS highest_rank, + online, level, gender, logout_time FROM `characters` WHERE $where_out ORDER BY $order_by $order_dir LIMIT $start, $itemperpage"; break; @@ -180,10 +164,8 @@ if (preg_match('/^[\t\v\b\f\a\n\r\\\"\'\? <>[](){}_=+-|!@#$%^&*~`.,0123456789\0]{1,30}$/', $search_value)) redirect("charlist.php?error=2"); $where_out ="$search_by LIKE '%$search_value%'"; - $sql_query = "SELECT guid,name,account,race,class,zone,map, - CAST( SUBSTRING_INDEX(SUBSTRING_INDEX(`data`, ' ', ".(CHAR_DATA_OFFSET_HONOR_KILL+1)."), ' ', -1) AS UNSIGNED) AS highest_rank, - online, level, gender, logout_time, - CAST( SUBSTRING_INDEX(SUBSTRING_INDEX(`data`, ' ', ".(CHAR_DATA_OFFSET_GUILD_ID+1)."), ' ', -1) AS UNSIGNED) as gname FROM `characters` + $sql_query = "SELECT guid,name,account,race,class,zone,map,totalKills AS highest_rank, + online, level, gender, logout_time FROM `characters` WHERE $where_out ORDER BY $order_by $order_dir LIMIT $start, $itemperpage"; } @@ -193,11 +175,8 @@ else { $query_1 = $sqlc->query("SELECT count(*) FROM `characters`"); - $query = $sqlc->query("SELECT guid,name,account,race,class,zone,map, - CAST( SUBSTRING_INDEX(SUBSTRING_INDEX(`data`, ' ', ".(CHAR_DATA_OFFSET_HONOR_KILL+1)."), ' ', -1) AS UNSIGNED) AS highest_rank, - online,level, gender, logout_time, - CAST( SUBSTRING_INDEX(SUBSTRING_INDEX(`data`, ' ', ".(CHAR_DATA_OFFSET_GUILD_ID+1)."), ' ', -1) AS UNSIGNED) as gname - FROM `characters` ORDER BY $order_by $order_dir LIMIT $start, $itemperpage"); + $query = $sqlc->query("SELECT guid,name,account,race,class,zone,map,totalKills AS highest_rank, + online,level, gender, logout_time FROM `characters` ORDER BY $order_by $order_dir LIMIT $start, $itemperpage"); } $all_record = $sqlc->result($query_1,0); @@ -303,7 +282,7 @@ $owner_acc_name = $sqlr->result($result, 0, 'username'); $lastseen = date('Y-m-d G:i:s', $char[11]); - $guild_name = $sqlc->fetch_row($sqlc->query('SELECT name FROM guild WHERE guildid = '.$char[12].'')); + $guild_name = $sqlc->fetch_row($sqlc->query('SELECT G.name FROM characters C, guild_member GM, guild G WHERE C.guid = GM.guid AND GM.guildid = G.guildid AND C.guid='.$char[0].'')); if (($user_lvl >= $owner_gmlvl)||($owner_acc_name == $user_name)) { @@ -316,15 +295,15 @@ $output .= " $char[0] - ".htmlentities($char[1])." - ".htmlentities($owner_acc_name)." + ".$char[1]." + ".$owner_acc_name." \"\" \"\" ".char_get_level_color($char[9])." ".get_map_name($char[6], $sqlm)." ".get_zone_name($char[5], $sqlm)." $char[7] - ".htmlentities($guild_name[0])." + ".$guild_name[0]." $lastseen ".(($char[8]) ? "\"\"" : "-").""; if ($showcountryflag) Index: index.php =================================================================== --- index.php (revision 490) +++ index.php (working copy) @@ -189,8 +189,10 @@ if($order_by == 'ip') $result = $sqlr->query('select id, last_ip from account where active_realm_id != 1 order by last_ip '.$order_dir.' LIMIT '.$start.', '.$itemperpage.''); else - $result = $sqlc->query('SELECT guid, name, race, class, zone, map, level, account, gender, totalHonorPoints, - CAST( SUBSTRING_INDEX(SUBSTRING_INDEX(data, " ", '.(CHAR_DATA_OFFSET_GUILD_ID+1).'), " ", -1) AS UNSIGNED) as gname +// $result = $sqlc->query('SELECT guid, name, race, class, zone, map, level, account, gender, totalHonorPoints, +// CAST( SUBSTRING_INDEX(SUBSTRING_INDEX(data, " ", '.(CHAR_DATA_OFFSET_GUILD_ID+1).'), " ", -1) AS UNSIGNED) as gname +// FROM characters WHERE online= 1 '.($gm_online == '0' ? 'AND extra_flags &1 = 0 ' : '').$order_side.' ORDER BY '.$order_by.' '.$order_dir.' LIMIT '.$start.', '.$itemperpage.''); + $result = $sqlc->query('SELECT guid, name, race, class, zone, map, level, account, gender, totalHonorPoints FROM characters WHERE online= 1 '.($gm_online == '0' ? 'AND extra_flags &1 = 0 ' : '').$order_side.' ORDER BY '.$order_by.' '.$order_dir.' LIMIT '.$start.', '.$itemperpage.''); $total_online = $sqlc->result($sqlc->query('SELECT count(*) FROM characters WHERE online= 1'.(($gm_online_count == '0') ? ' AND extra_flags &1 = 0' : '')), 0); $replace = ' @@ -234,15 +236,18 @@ { if($order_by == 'ip') { +// $temp = $sqlc->fetch_assoc($sqlc->query(' +// SELECT guid, name, race, class, zone, map, level, account, gender, totalHonorPoints, +// CAST( SUBSTRING_INDEX(SUBSTRING_INDEX(data, " ", '.(CHAR_DATA_OFFSET_GUILD_ID+1).'), " ", -1) AS UNSIGNED) as gname +// FROM characters WHERE online= 1 '.($gm_online == '0' ? 'AND extra_flags &1 = 0 ' : '').$order_side.' and account = '.$char['id'].'')); $temp = $sqlc->fetch_assoc($sqlc->query(' - SELECT guid, name, race, class, zone, map, level, account, gender, totalHonorPoints, - CAST( SUBSTRING_INDEX(SUBSTRING_INDEX(data, " ", '.(CHAR_DATA_OFFSET_GUILD_ID+1).'), " ", -1) AS UNSIGNED) as gname + SELECT guid, name, race, class, zone, map, level, account, gender, totalHonorPoints FROM characters WHERE online= 1 '.($gm_online == '0' ? 'AND extra_flags &1 = 0 ' : '').$order_side.' and account = '.$char['id'].'')); $char = $temp; } $gm = $sqlr->result($sqlr->query('SELECT gmlevel FROM account WHERE id='.$char['account'].''), 0); - $guild_name = $sqlc->result($sqlc->query('SELECT name FROM guild WHERE guildid='.$char['gname'].'')); + $guild_name = $sqlc->result($sqlc->query('SELECT G.name FROM characters C, guild_member GM, guild G WHERE C.guid = GM.guid AND GM.guildid = G.guildid AND C.guid='.$char['guid'].'')); $output .= '