Effective PHP array and select

Question

I can solve this with several foreach-statements but it takes forever to load the page when it’s 26 students in every class and some subjects have up to 13 ’radnummer’. See examples below.

This what I prefer the output to be (rows and columns):

The problem: it takes forever to load.

Question: how do I speed it up and make more efficient?

Table one where I select all the students in a 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 gives:

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] => [email protected]  
      [5] => [email protected]  
      [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] => [email protected] 
      [5] => [email protected]
      [6] => 1227 
      [klass] => 6B 
      [7] => 6B
)
)

Table two where I select corresponding data to each student. Here there is a problem. In this example each student should have five rows of ’radnummer’ (se below) for this particular subject. Only the last one shows up. On each row a specific value ’kunsk_klick’ are supposed to show up. See example below (SELECT 2):

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

PRINT_R gives:

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 
)

EDIT 1: structure of tables.

Table 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;

Table 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;

Table 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.';

EDIT 2: example code (I know, no good mixing PHP and HTML, bad indenting as well..).

<?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>";

?>

Edit 3:

To ONLY output the named column data rather than also the numeric indicies data I change the fetch statement:

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

Now it looks like this:

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

Can you show us your actual code (not just the queries)? I'm guessing that there's queries in loops? Also, have you made sure that your tables are properly indexed? That alone will speed up your queries a lot. — M. Eriksson
It's very hard to follow the question without knowing the database schema. Could you please replicate the database for example to SQL Fiddle (sqlfiddle.com) ? — drodil
...or better yet, add the schema here on SO (so future visitors also can see it). — M. Eriksson
@MagnusEriksson the actual code is pretty extensive. Is it possible to show parts of it? Thanks so far. — Per76
1) errrm, none of your MySQL Schema tables seem to have indexes...... — Martin

Martin Martin · Accepted Answer · 2018-08-08T10:33:35

Judging from the response by Per76, this comment appears to be an effective solution:

None of your MySQL Schema tables seem to have indexes.

So... add Indexes to your SQL tables. You ideally should index all columns you reference in any WHERE clause or any JOIN clause or any ORDER BY . Indexing is the key to MySQL speed.

Maximizing Query Performance through Column Indexing in MySQL

How do MySQL indexes work?

How do I add indices to MySQL tables?

NOTE: The suggestions listed here are micro-optimisations and will only make the smallest difference to page load times and/or efficiency. These are tweaks only and their improvements will pale in comparison to the improvements gained from implementing proper column indexing in the MySQL, as stated above.

Using switch PHP statements instead of repetative elseifs.

    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"; }

becomes

    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;
    }

Use the correct MySQL Integer type for column definitions. Many of your INT columns with only 3, 4 or 5 column lengths can be UNSIGNED SMALLINT.
Unifying your MySQL column data types so INT(4) columns are comparing with other INT(4) columns in JOIN and WHERE clause.
Use utf8mb4 character sets and collations rather than the crappy 3-byte UTF-8 (huh? Wtf?)
Typecasting efficiency in if statements: (int)$arskursen===6 is faster and more efficient (by a tiny mark up) than $arskursen=='6'

Fix your PHP to ONLY output the named column data rather than also the numeric indicies data (you're playing with twice as much data as needed in the PHP). Example:

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

Effective PHP array and select

1 Answers