我构建了这段代码，从mysql中的两个表中选择250多个值。
我想得到每个名字对应的10个数值。每个表中的5个值（表1和表2）
从表1中，我选择与名称对应的5个值，并将它们放入关联数组中。如果值为"-50"，我将取消设置该值。
从表2中，对应于名称的5个值也对应于对应于表1的一个值。我将它们放入关联数组中，如果值<0.35，我取消设置表1的相应值。
我执行所有这些操作是为了将表1中的5列中的每一列从高到低排序（删除-50和<0.35值）
从数据库中选择数据：
//Names that correspond to 5 values in table1 and 5 in tab
$names = array(
'aatrox', 'ahri', 'akali', 'alistar', 'amumu', 'anivia', 'annie',
'ashe', 'azir', 'bard', 'blitzcrank', 'brand', 'braum', 'caitlyn',
'cassiopeia', 'chogath', 'corki', 'darius', 'diana', 'drmundo',
'draven', 'ekko', 'elise', 'evelynn', 'ezreal', 'fiddlesticks', 'fiora',
'fizz', 'galio', 'gangplank', 'garen', 'gnar', 'gragas', 'graves',
'hecarim', 'heimerdinger', 'irelia', 'janna', 'jarvaniv', 'jax',
'jayce', 'jinx', 'kalista', 'karma', 'karthus', 'kassadin', 'katarina',
'kayle', 'kennen', 'khazix', 'kogmaw', 'leblanc', 'leesin', 'leona',
'lissandra', 'lucian', 'lulu', 'lux', 'malphite', 'malzahar', 'maokai',
'masteryi', 'missfortune', 'mordekaiser', 'morgana', 'nami', 'nasus',
'nautilus', 'nidalee', 'nocturne', 'nunu', 'olaf', 'orianna',
'pantheon', 'poppy', 'quinn', 'rammus', 'reksai', 'renekton', 'rengar',
'riven', 'rumble', 'ryze', 'sejuani', 'shaco', 'shen', 'shyvana',
'singed', 'sion', 'sivir', 'skarner', 'sona', 'soraka', 'swain',
'syndra', 'tahmkench', 'talon', 'taric', 'teemo', 'thresh', 'tristana',
'trundle', 'tryndamere', 'twistedfate', 'twitch', 'udyr', 'urgot',
'varus', 'vayne', 'veigar', 'velkoz', 'vi', 'viktor', 'vladimir',
'volibear', 'warwick', 'wukong', 'xerath', 'xinzhao', 'yasuo', 'yorick',
'zac', 'zed', 'ziggs', 'zilean', 'zyra'
);
//Array for table1
$table1 = array();
//Array for table2
$table2 = array();
$numNames = count($names);
//Selecting 5 values (topratio, jungleratio, midratio, adcratio, supportratio) from table1 and corresponding them to their name.
for($i = 0; $i < $numNames; $i++){
$query = $dbconn->query("SELECT topratio, jungleratio, midratio,
adcratio, supportratio
FROM table1
WHERE name = '".$names[$i]."'
ORDER BY Id DESC LIMIT 1");
$table1[$names[$i]] = $query->fetch_assoc();
};
//Selecting 5 values (topdiapr, junglediapr, middiapr, adcdiapr, supportdiapr) from table2 and corresponding them to their name.
for($i = 0; $i < $numNames; $i++){
$query = $dbconn->query("SELECT topdiapr, junglediapr, middiapr,
adcdiapr, supportdiapr
FROM table2
WHERE name = '".$names[$i]."'
ORDER BY Id DESC LIMIT 1");
$table2[$names[$i]] = $query->fetch_assoc();
};
print_r($table1)返回以下内容：
Array (
[Aatrox] => Array (
[topratio] => 7.59
[jungleratio] => 0.55
[midratio] => -50
[adcratio] => -50
[supportratio] => -50
)
[Ahri] => Array (
[topratio] => -50
[jungleratio] => -50
[midratio] => 6.85
[adcratio] => -50
[supportratio] => -50
)
...
This continues until last name
)
删除-50个值和<0.35
//I run 5 times this code because there are 5 columns in table1
for($b = 0; $b < 5; $b++){
//If value corresponding to a name in table1 is -50, unset
for($a = 0; $a < $numNames; $a++){
if(($key = array_search(-50, $table1[$names[$a]])) !== false) {
unset($table1[$names[$a]][$key]);
}
}
}
//If value corresponding to a name in table2 (topdiapr) is < than 0.35, unset corresponding value in table1 (topratio)
for($a = 0; $a < $numNames; $a++){
if(($table2[$names[$a]][topdiapr])<0.35) {
unset($table1[$names[$a]][topratio]);
}
}
//Same that previous one but with junglediapr and jungleratio
for($a = 0; $a < $numNames; $a++){
if(($table2[$names[$a]][junglediapr])<0.35) {
unset($table1[$names[$a]][jungleratio]);
}
}
//Same that previous one but with middiapr and midratio
for($a = 0; $a < $numNames; $a++){
if(($table2[$names[$a]][middiapr])<0.35) {
unset($table1[$names[$a]][midratio]);
}
}
//Same that previous one but with adcdiapr and adcratio
for($a = 0; $a < $numNames; $a++){
if(($table2[$names[$a]][adcdiapr])<0.35) {
unset($table1[$names[$a]][adcratio]);
}
}
//Same that previous one but with supportdiapr and supportratio
for($a = 0; $a < $numNames; $a++){
if(($table2[$names[$a]][supportdiapr])<0.35) {
unset($table1[$names[$a]][supportratio]);
}
}
最后，我得到每个列的数组，并从高到低对它们进行排序：
//topratios list. This applies too to jungleratios, midratios, adcratios and supportratios.
$topratios = array();
for($c = 0; $c < $numNames; $c++){
$topratios[$names[$c]] = $table1[$names[$c]][topratio];
}
$topratios = array_filter($topratios);
arsort($topratios);
运行这个，
foreach ($supportratios as $key => $val) {
echo 'Support: '.$key.'> '.$val.'<br>';
}
正确返回我想要得到的：
Support: Brand > 11.54
Support: Blitzcrank > 9.23
Support: Janna > 6.58
Support: Leona > 5.44
Support: Nami > 5.43
Support: Nautilus > 5.23
Support: Zyra > 4.66
Support: Soraka > 3.84
Support: Zilean > 3.71
Support: VelKoz > 2.22
Support: Braum > 2.22
Support: Bard > 2.14
Support: Sona > 1.46
Support: Thresh > 0.59
Support: Taric > 0.45
Support: Morgana > 0.37
Support: Shen > -0.77
Support: Alistar > -1.24
Support: Lulu > -1.7
Support: Kennen > -2.45
Support: Karma > -2.97
Support: Annie > -3.5
Support: Fiddlesticks > -5.38
Support: Tahm Kench > -7.98
我想知道是否有更快、更简单的方法。此外，我想知道是否有任何替代执行250多个查询的方法。
我真的不知道这是否有帮助，但也许。。。
$names=array('aatrox', 'ahri', 'akali', 'alistar', 'amumu', 'anivia', 'annie', 'ashe', 'azir', 'bard',
'blitzcrank', 'brand', 'braum', 'caitlyn', 'cassiopeia', 'chogath', 'corki', 'darius', 'diana', 'drmundo',
'draven', 'ekko', 'elise', 'evelynn', 'ezreal', 'fiddlesticks', 'fiora', 'fizz', 'galio', 'gangplank',
'garen', 'gnar', 'gragas', 'graves', 'hecarim', 'heimerdinger', 'irelia', 'janna', 'jarvaniv', 'jax',
'jayce', 'jinx', 'kalista', 'karma', 'karthus', 'kassadin', 'katarina', 'kayle', 'kennen', 'khazix',
'kogmaw', 'leblanc', 'leesin', 'leona', 'lissandra', 'lucian', 'lulu', 'lux', 'malphite', 'malzahar',
'maokai', 'masteryi', 'missfortune', 'mordekaiser', 'morgana', 'nami', 'nasus', 'nautilus', 'nidalee',
'nocturne', 'nunu', 'olaf', 'orianna', 'pantheon', 'poppy', 'quinn', 'rammus', 'reksai', 'renekton',
'rengar', 'riven', 'rumble', 'ryze', 'sejuani', 'shaco', 'shen', 'shyvana', 'singed', 'sion', 'sivir',
'skarner', 'sona', 'soraka', 'swain', 'syndra', 'tahmkench', 'talon', 'taric', 'teemo', 'thresh',
'tristana', 'trundle', 'tryndamere', 'twistedfate', 'twitch', 'udyr', 'urgot', 'varus', 'vayne',
'veigar', 'velkoz', 'vi', 'viktor', 'vladimir', 'volibear', 'warwick', 'wukong', 'xerath', 'xinzhao',
'yasuo', 'yorick', 'zac', 'zed', 'ziggs', 'zilean', 'zyra');
$sql1="select `name`, `topratio`, `jungleratio`, `midratio`, `adcratio`, `supportratio`
from `table1`
where `name` in ('". implode( "','", $names ) ."')
and ( `topratio` != -50 and `topratio` >= 0.35 )
and ( `jungleratio` != -50 and `jungleratio` >= 0.35 )
and ( `midratio` != -50 and `midratio` >= 0.35 )
and ( `adcratio` != -50 and `adcratio` >= 0.35 )
and ( `supportratio` != -50 and `supportratio` >= 0.35 );";
$sql2="select `name`, `topdiapr`, `junglediapr`, `middiapr`, `adcdiapr`, `supportdiapr`
from `table2`
where `name` in ('". implode( "','", $names ) ."')
and ( `topratio` != -50 and `topratio` >= 0.35 )
and ( `junglediapr` != -50 and `junglediapr` >= 0.35 )
and ( `middiapr` != -50 and `middiapr` >= 0.35 )
and ( `adcdiapr` != -50 and `adcdiapr` >= 0.35 )
and ( `supportdiapr` != -50 and `supportdiapr` >= 0.35 )";
我建议您添加一些规范化，然后使用一些简单的联接，这将大大简化您的生活。
表结构
CREATE TABLE `champs` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`title` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT
CREATE TABLE `ratios_a` (
`champ_id` int(11) unsigned NOT NULL,
`top_r` int(11) DEFAULT NULL,
`jungle_r` int(11) DEFAULT NULL,
`mid_r` int(11) DEFAULT NULL,
`adc_r` int(11) DEFAULT NULL,
`support_r` int(11) DEFAULT NULL,
PRIMARY KEY (`champ_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
CREATE TABLE `ratios_b` (
`champ_id` int(11) unsigned NOT NULL,
`top_diapr` int(11) DEFAULT NULL,
`jungle_diapr` int(11) DEFAULT NULL,
`mid_diapr` int(11) DEFAULT NULL,
`adc_diapr` int(11) DEFAULT NULL,
`support_diapr` int(11) DEFAULT NULL,
PRIMARY KEY (`champ_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
根据条件选择全部，使用PDO。
您可以在任何查询中添加ORDER BY，以实现所需的任何排序
$dsn = 'mysql:dbname=lol;host=127.0.0.1';
$user = 'root';
$password = '';
$dbh = new PDO($dsn, $user, $password);
$sql = 'SELECT c.title, a.top_r, a.jungle_r, a.mid_r, a.adc_r, a.support_r,
b.top_diapr, b.jungle_diapr, b.mid_diapr, b.adc_diapr, b.support_diapr
FROM champs c
LEFT JOIN ratios_a a ON c.id=a.champ_id
LEFT JOIN ratios_b b ON c.id=b.champ_id
WHERE a.top_r != -50 AND a.jungle_r != -50 AND
a.mid_r != -50 AND a.adc_r !=-50 AND a.support_r != -50 AND
a.top_r >= 0.35 AND a.jungle_r >= 0.35 AND
a.mid_r >= 0.35 AND a.adc_r >= 0.35 AND a.support_r >= 0.35
AND
b.top_diapr != -50 AND b.jungle_diapr != -50 AND
b.mid_diapr != -50 AND b.adc_diapr !=-50 AND b.support_diapr != -50 AND
b.top_diapr >= 0.35 AND b.jungle_diapr >= 0.35 AND
b.mid_diapr >= 0.35 AND b.adc_diapr >= 0.35 AND b.support_diapr >= 0.35';
foreach($dbh->query($sql, PDO::FETCH_ASSOC) as $result){
print_r($result);
}
/* will output something like this
Array
(
[title] => yasuo
[top_r] => 5
[jungle_r] => 10
[mid_r] => 17
[adc_r] => 13
[support_r] => 1
[top_diapr] => 19
[jungle_diapr] => 0
[mid_diapr] => 12
[adc_diapr] => 10
[support_diapr] => 20
)
Array
(
[title] => yorick
[top_r] => 3
[jungle_r] => 13
[mid_r] => 9
[adc_r] => 9
[support_r] => 2
[top_diapr] => 13
[jungle_diapr] => 15
[mid_diapr] => 8
[adc_diapr] => 8
[support_diapr] => 20
)
*/
如果您只需要一些特定的比率，或者您想要特定冠军的统计数据，例如support_ratio和support_diapr，只需运行不同的查询。
$sql = 'SELECT c.title, a.support_r, b.support_diapr
FROM champs c
LEFT JOIN ratios_a a ON c.id=a.champ_id
LEFT JOIN ratios_b b ON c.id=b.champ_id
WHERE c.id = 62 AND
a.support_r != -50 AND a.support_r >= 0.35 AND
b.support_diapr != -50 AND b.support_diapr >= 0.35';
foreach($dbh->query($sql, PDO::FETCH_ASSOC) as $result){
print_r($result);
}
/* will output this
Array
(
[title] => masteryi
[support_r] => 5
[support_diapr] => 8
)
*/