php - 简化php代码按列和排序结果从mysql数据库中选择数据

Question

我构建了这段代码以从 mysql 中的 2 个表中选择 250 多个值。

我想得到每个名字对应的10个数值。每个表(table1 和 table2)的 5 个值

从表 1 中，我选择了 5 个与名称对应的值并将它们放入关联数组中。如果该值为“-50”，我将取消设置该值。

从表 2 中，与名称对应的 5 个值也对应于表 1 的一个值。我将它们放入一个关联数组中，如果该值小于 0.35，我将取消设置 table1 的相应值。

我执行所有这些操作以从高到低对 table1 中的 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 多个查询。

Answer 1

我真的不知道这是否会有所帮助，但也许它可能...

$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 )";