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] => 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
)
)

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, Aug 08 '18 at 08:51
It's very hard to follow the question without knowing the database schema. Could you please replicate the database for example to SQL Fiddle (http://sqlfiddle.com/) ? — drodil, Aug 08 '18 at 08:54
...or better yet, add the schema here on SO (so future visitors also can see it). — M. Eriksson, Aug 08 '18 at 08:55
@MagnusEriksson the actual code is pretty extensive. Is it possible to show parts of it? Thanks so far. — Per76, Aug 08 '18 at 09:11
You only need to show the code that's relevant to the issue. You can read [How to create a Minimal, Complete, and Verifiable example](http://stackoverflow.com/help/mcve) to get some information what you should add. — M. Eriksson, Aug 08 '18 at 09:45
@MagnusEriksson thank you for your help so far. I have updated the question. See edit 2. — Per76, Aug 08 '18 at 10:12
1) errrm, none of your MySQL Schema tables seem to have indexes...... — Martin, Aug 08 '18 at 10:14
@Martin Thank you! It made a lot to add index on elev_id in one of the tables. I feel like a NOOB. Have a nice day! — Per76, Aug 08 '18 at 10:27

Martin · Accepted Answer · 2018-08-08T11:56:10.493

1

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 
)

edited Aug 08 '18 at 11:56

answered Aug 08 '18 at 10:33

Martin

22,212
11
70
132

Just out of curiosity, is there any reference for the "efficiency" gain (that you mentioned in the comment under the OP) for using `switch/case` over `if/else`? In this case (no pun intended), using `switch/case` does make the code way more readable, but all I've read about the subject have basically ended with the conclusion that there's no actual performance difference. – M. Eriksson Aug 08 '18 at 11:13
@MagnusEriksson `switch` only reads the value of the variable once- and checks it against each *case*. the Manual states "In a switch statement, the condition is evaluated only once and the result is compared to each case statement. In an elseif statement, the condition is evaluated again". Therefore a switch would be very slightly more efficient. But I must state that all my points except the MySQL Index are going to be only tiny improvements. `:-)` – Martin Aug 08 '18 at 11:33
Sure, but I seriously doubt that simply reading a variable one or 50 time will make any (noticeable) difference at all. Seems like the mother of all micro optimizations. :-) It's a bit different if you can a function on each though. Then there can be a significant difference. – M. Eriksson Aug 08 '18 at 11:35
@MagnusEriksson yeah I can accept that; as actually reading the manaul entry on `switch` is that it is only a *significant* improvement on complex comparisons rather than simple comparisons. But I've got into the habit of preferring `switch` over a block of `elseif`s `''\_(;-)_/''` – Martin Aug 08 '18 at 11:38
Nothing wrong with that. If anything, it does often make the code more readable. I'm just afraid mentioning it in an answer about performance might start a new "double quotes vs single quotes"-type rampage. Unless there is a complex expression in use, that is. There are so many things in PHP that people still apply and recommends that _use_ to make noticeable differences in some situations but basically is ani-issues today :-p – M. Eriksson Aug 08 '18 at 11:42
1

@MagnusEriksson I added a (*dis*)qualifier to my answer, above – Martin Aug 08 '18 at 11:56
@Martin Thank you! Very kind of you to answer and give really good pointers. One (noob) question: how do I output the named column data in the best way? Or maybe: how do I avoid getting the numeric indices data too? – Per76 Aug 08 '18 at 14:27
1

@Per76 use something like this: http://php.net/manual/en/mysqli-result.fetch-assoc.php – Martin Aug 08 '18 at 14:31
The default fetch mode is FETCH_BOTH. I changed my mode to FETCH_ASSOC to only get the non-numeric keys. $pdo->fetchAll(PDO::FETCH_ASSOC); – Per76 Aug 08 '18 at 15:02
@Per76 yes that should work exactly as you need. `:-)` – Martin Aug 08 '18 at 15:03

Effective PHP array and select

1 Answers1