php - 有效的 PHP 数组和选择

Question

我可以用几个 foreach 语句解决这个问题，但是当每个类(class)有 26 名学生并且某些科目最多有 13 个“radnummer”时，加载页面需要很长时间。请参阅下面的示例。

这是我更喜欢的输出(行和列):

问题:加载需要很长时间。

问题:如何加快速度并提高效率？

表一，我选择类(class)中的所有学生 (SELECT 1):

SELECT `intressenter_alla`.id, `intressenter_alla`.fornamn, `intressenter_alla`.efternamn
FROM `intressenter_alla` 
INNER JOIN `klass_elev` 
ON `intressenter_alla`.id = `klass_elev`.id
WHERE (`klass_elev`.klass = :klassen)
ORDER BY `intressenter_alla`.efternamn, `intressenter_alla`.fornamn ASC

PRINT_R 给出:

Array ( 

      [0] => Array ( 
      [id] => 1226 
      [0] => 1226 
      [lid] => 0 
      [1] => 0 
      [fornamn] => Peter
      [2] => Peter 
      [efternamn] => Strobe 
      [3] => Strobe 
      [personnr] => 070920-8690 
      [4] => 070920-8690 
      [mejl] => peter.strobe@mail.com  
      [5] => peter.strobe@mail.com  
      [6] => 1226 
      [klass] => 6B 
      [7] => 6B
)

      [1] => Array ( 
      [id] => 1227 
      [0] => 1227 
      [lid] => 0 
      [1] => 0 
      [fornamn] => Victor 
      [2] => Victor 
      [efternamn] =>Gand 
      [3] => Gand 
      [personnr] => 070518-8995 
      [4] => 070518-8995
      [mejl] => victor.gand@mail.com 
      [5] => victor.gand@mail.com
      [6] => 1227 
      [klass] => 6B 
      [7] => 6B
)
)

表二我为每个学生选择了相应的数据。这里有一个问题。在这个例子中，每个学生对于这个特定的科目应该有五行“radnummer”(见下文)。只有最后一个出现。每行都会显示一个特定值“kunsk_klick”。请参阅下面的示例(选择 2):

SELECT * 
FROM `iup_kunskapskrav_klick` 
WHERE elev_id = ? AND radnummer = ? AND amne_id = ? AND arskurs = ?
ORDER BY radnummer, datum DESC

PRINT_R 给出:

Array ( 
   [kunsk_id] => 138557
   [0] => 138557 
   [amne_id] => 1 
   [1] => 1 
   [radnummer] => 5 
   [2] => 5 
   [elev_id] => 1226 
   [3] => 1226 
   [arskurs] => 5 
   [4] => 5 
   [lid] => 1 
   [5] => 1 
   [kunsk_klick] => E 
   [6] => E 
   [datum] => 2018-05-29 
   [7] => 2018-05-29 
) 

Array ( 

   [kunsk_id] => 138561 
   [0] => 138561 
   [amne_id] => 1 
   [1] => 1 
   [radnummer] => 5 
   [2] => 5 
   [elev_id] => 1227 
   [3] => 1227 
   [arskurs] => 5 
   [4] => 5 
   [lid] => 1 
   [5] => 1 
   [kunsk_klick] => E 
   [6] => E 
   [datum] => 2018-05-29 
   [7] => 2018-05-29 
)

编辑1:表的结构。

表intressenter_alla

CREATE TABLE `intressenter_alla` (
  `id` int(4) NOT NULL,
  `lid` int(3) NOT NULL,
  `fornamn` varchar(255) NOT NULL,
  `efternamn` varchar(255) NOT NULL,
  `personnr` varchar(12) NOT NULL,
  `mejl` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

表格iup_kunskapskrav_klick

CREATE TABLE `iup_kunskapskrav_klick` (
  `kunsk_id` int(11) UNSIGNED NOT NULL,
  `amne_id` int(3) NOT NULL,
  `radnummer` int(10) NOT NULL,
  `elev_id` int(3) NOT NULL,
  `arskurs` int(2) NOT NULL,
  `lid` int(3) NOT NULL,
  `kunsk_klick` varchar(3) COLLATE utf8_swedish_ci NOT NULL,
  `datum` date NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci;

表klass_elev

CREATE TABLE `klass_elev` (
  `id` int(5) NOT NULL,
  `klass` varchar(2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Elevid och klass.';

编辑 2:示例代码(我知道，PHP 和 HTML 混合不好，缩进也不好......)。

<?php

// All students (See SELECT number 1)
$hamta_hela_klassen = $anvandare->klasslista($_GET['klass']);

// Count how many radnummer there is in the subject
$count = 0;
$count = $abb->rakna_kunskapskrav($_GET['amne_id'], $arskursens);

echo "<table>";
echo "<thead>";
echo "<tr><th>Nr.</th><th>Picture</th><th>Name</th>";
if ($count<=1){
    echo "<th scope='row' style='vertical-align:middle;text-align:center'>Kunskapskrav</th>";
} else {
    for($i=1;$i<=$count;$i++)
    {
        // This outputs R1 - R2 and so on in the header of the columns (see the picture above)
        echo "<th width='1%' scope='row' style='vertical-align:middle;text-align:center'>R".$i."</th>";
    }
}

echo "<th>Date</th><th>Button</th>";

$raknare = 0;

// Loop out each student. Not the most effective way...                                                               
foreach ($hamta_hela_klassen as $klassuppgifter){
    $raknare++;

    // Profile picture
    $profilbild = '/home/jo/public_html/no/students/profilbilder/'.$klassuppgifter['id'].'.jpg';

    echo "<td>";
    echo AKTIV_URL."students/profilbilder/".$klassuppgifter['id'].".jpg";
    echo "</td>";

    // NAME
    echo "<td>";
    echo $klassuppgifter['fornamn']." ".$klassuppgifter['efternamn'];
    echo "</td>";

    if ($count<=1){
        echo "<td>Empty.</td>";
    } else {for($i=1;$i<=$count;$i++)
    {

        // Fetching each corresponding RADNUMMER for each student (see SELECT 2 above)                                      

        $iup_info_k1 = $iup->hamta_ett_kunskapskrav_klick_klass($klassuppgifter['id'], $i,$_GET['amne_id'], $arskursen);

        if (empty($iup_info_k1) && ($arskursen == '5' || $arskursen =='6' || $arskursen =='8' || $arskursen =='9')){
            $arskursen_lager = $arskursen -1;
            $iup_info = $iup->hamta_ett_kunskapskrav_klick_klass($klassuppgifter['id'], $i,$_GET['amne_id'], $arskursen_lager);
        } else {
            $iup_info = $iup_info_k1;
        }

        //Special for students in 4 - 5
        if ($arskursen=='4' || $arskursen=='5') {
            if ($iup_info['kunsk_klick'] == "G"){$betyget = 'P';}
            if ($iup_info['kunsk_klick'] == "F"){$betyget = 'O';}
            elseif ($iup_info['kunsk_klick'] == "E"){$betyget = '1';}
            elseif ($iup_info['kunsk_klick'] == "C"){$betyget = '2';}
            elseif ($iup_info['kunsk_klick'] == "A"){$betyget = '3';}
            elseif (empty($iup_info['kunsk_klick'])) {$betyget = "P"; }
        }

        //Special for students in 6 - 9
        if ($arskursen=='6' || $arskursen=='7' || $arskursen=='8' || $arskursen=='9') {
            if ($iup_info['kunsk_klick'] == "G"){$betyget = 'P';}
            elseif (empty($iup_info['kunsk_klick'])) {$betyget = "P"; }
            else { $betyget = $iup_info['kunsk_klick']; }
        }
        echo "<td>".$betyget."</td>";
    }
    }

    // DATE
    echo "<td>";
    echo $iup_info['datum'];
    echo "</td>";

    // Edit button
    echo "<td>";
    echo "EDIT";
    echo "</td>";


}

echo "</tr>";

echo "</tbody>";
echo "</table>";

?>

编辑3:

为了仅输出命名列数据而不是数字索引数据，我更改了获取语句:

$pdo->fetchAll(PDO::FETCH_ASSOC);

现在看起来像这样:

    Array ( 
       [kunsk_id] => 138561 
       [amne_id] => 1 
       [radnummer] => 5 
       [elev_id] => 1227 
       [arskurs] => 5 
       [lid] => 1 
       [kunsk_klick] => E 
       [datum] => 2018-05-29 
    )

Answer 1

从 Per76 的回复来看，此评论似乎是一个有效的解决方案:

None of your MySQL Schema tables seem to have indexes.

所以...将索引添加到您的 SQL 表中。理想情况下，您应该对在任何 WHERE 子句、任何 JOIN 子句或任何 ORDER BY 中引用的所有列建立索引。索引是 MySQL 速度的关键。

Maximizing Query Performance through Column Indexing in MySQL

How do MySQL indexes work?

How do I add indices to MySQL tables?

<小时/>

注意:此处列出的建议是微优化，只会对页面加载时间和/或效率产生最小的影响。这些只是调整，与在 MySQL 中实现适当的列索引所获得的改进相比，它们的改进显得黯然失色，如上所述。

• 使用 switch PHP 语句而不是重复的 elseif。

      if ($iup_info['kunsk_klick'] == "F"){$betyget = 'O';}
      elseif ($iup_info['kunsk_klick'] == "E"){$betyget = '1';}
      elseif ($iup_info['kunsk_klick'] == "C"){$betyget = '2';}
      elseif ($iup_info['kunsk_klick'] == "A"){$betyget = '3';}
      elseif (empty($iup_info['kunsk_klick'])) {$betyget = "P"; }
  

  变成

      switch($iup_info['kunsk_klick']) {
           case "F":
               $betyget = 'O';
               break;
           case "E":
               $betyget = '1';
               break;
           case "C":
               $betyget = '2';
               break;
           case "A":
               $betyget = '3';
               break;
           case "":
               $betyget = 'P';
               break;
      }
  

• 使用正确的MySQL Integer type用于列定义。许多只有 3、4 或 5 列长度的 INT 列可以是 UNSIGNED SMALLINT。

• 统一 MySQL 列数据类型，以便 INT(4) 列与 JOIN 和 中的其他 INT(4) 列进行比较>WHERE 子句。
• 使用 utf8mb4 字符集和排序规则，而不是蹩脚的 3 字节 UTF-8 ( huh? Wtf? )
• if 语句中的类型转换效率:(int)$arskursen===6 比 $arskursen 更快、更高效(略有提升) =='6'

• 将 PHP 修复为仅输出命名列数据，而不是数字索引数据(您使用的数据量是PHP)。示例:

  Array ( 
     [kunsk_id] => 138561 
     [amne_id] => 1 
     [radnummer] => 5 
     [elev_id] => 1227 
     [arskurs] => 5 
     [lid] => 1 
     [kunsk_klick] => E 
     [datum] => 2018-05-29 
  )