(09-18-2018, 07:05 AM)php_rocs Wrote: Yes, it is very fast.  I use it quite often.  My trick is to build simple views that I can used in complex views. This cuts down on queries that look like a DBA with a DR's degree made it and trouble shooting is easier too.

(09-18-2018, 01:32 AM)Pertti Wrote: This is what is generally called N+1 problem, where you create 1 query for every N items you need. This is usually bad approach, as each DB query, no matter how small, adds couple of milliseconds for checking the DB connection, sending query, waiting DB server response, etc.

You should be able to convert this into single query fairly quickly tho.

PHP Code:

$marks = [];
$q = $this->db
    ->select('studentID, subjectID, mark')
    ->where('classesID', $classID)
    ->get('mark');
if ($q->num_rows()) {
    foreach ($q->result() as $row) {
        if (!isset($marks[$row->studentID])) {
            $marks[$row->studentID] = [];
        }
       
        $marks[$row->studentID][$row->subjectID] = $row->mark;
    }
}

foreach($students as $student) {
    $studentID = $student->student;
    foreach($subjects as $subject) {
        $subjectID=$subject->subjectID;
        $mark = isset($marks[$studentID][$subjectID]) ? $marks[$studentID][$subjectID] : false;
    }
} 


So, basically, you get all the marks for one class in single query, then create reference array in PHP using array indexes as meaningful data, ie student ID and subject ID.

Then, final step, you loop through your data, and see if there's mark value available in using meaningful indexes.

(09-18-2018, 05:39 AM)php_rocs Wrote: @kayinja.denis,

...OR...you could create a view that does the work for you and all you would have to do is call the view.

(09-18-2018, 11:24 AM)php_rocs Wrote: @kayinja.denis,

Basically, what I'm saying is taking the foreach loop and converting it into your database view.  Then when you call the database view it would already have your results. Currently, you run a database view then run multiple foreach loops to populate the mark detail.  My way would be one database view which would already include your mark detail.

(09-18-2018, 01:32 AM)Pertti Wrote: This is what is generally called N+1 problem, where you create 1 query for every N items you need. This is usually bad approach, as each DB query, no matter how small, adds couple of milliseconds for checking the DB connection, sending query, waiting DB server response, etc.

You should be able to convert this into single query fairly quickly tho.

PHP Code:

$marks = [];
$q = $this->db
    ->select('studentID, subjectID, mark')
    ->where('classesID', $classID)
    ->get('mark');
if ($q->num_rows()) {
    foreach ($q->result() as $row) {
        if (!isset($marks[$row->studentID])) {
            $marks[$row->studentID] = [];
        }
       
        $marks[$row->studentID][$row->subjectID] = $row->mark;
    }
}

foreach($students as $student) {
    $studentID = $student->student;
    foreach($subjects as $subject) {
        $subjectID=$subject->subjectID;
        $mark = isset($marks[$studentID][$subjectID]) ? $marks[$studentID][$subjectID] : false;
    }
} 


So, basically, you get all the marks for one class in single query, then create reference array in PHP using array indexes as meaningful data, ie student ID and subject ID.

Then, final step, you loop through your data, and see if there's mark value available in using meaningful indexes.

(09-25-2018, 12:19 PM)kayinja.denis Wrote:

(09-18-2018, 01:32 AM)Pertti Wrote: This is what is generally called N+1 problem, where you create 1 query for every N items you need. This is usually bad approach, as each DB query, no matter how small, adds couple of milliseconds for checking the DB connection, sending query, waiting DB server response, etc.

You should be able to convert this into single query fairly quickly tho.

PHP Code:

$marks = [];
$q = $this->db
    ->select('studentID, subjectID, mark')
    ->where('classesID', $classID)
    ->get('mark');
if ($q->num_rows()) {
    foreach ($q->result() as $row) {
        if (!isset($marks[$row->studentID])) {
            $marks[$row->studentID] = [];
        }
       
        $marks[$row->studentID][$row->subjectID] = $row->mark;
    }
}

foreach($students as $student) {
    $studentID = $student->student;
    foreach($subjects as $subject) {
        $subjectID=$subject->subjectID;
        $mark = isset($marks[$studentID][$subjectID]) ? $marks[$studentID][$subjectID] : false;
    }
} 


So, basically, you get all the marks for one class in single query, then create reference array in PHP using array indexes as meaningful data, ie student ID and subject ID.

Then, final step, you loop through your data, and see if there's mark value available in using meaningful indexes.

Hello  @Pertti ,
I tried your solution and it works very well however I added another ID that's the examID, but am stack on referencing the array cos it changed the dimension 

PHP Code:

$marks = [];
$q = $this->db
    ->select('studentID, subjectID, examID, mark')
    ->where('classesID', $classID)
    ->get('mark');
if ($q->num_rows()) {
    foreach ($q->result() as $row) {
        if (!isset($marks[$row->studentID])) {
            $marks[$row->studentID] = [];
        }
       
        $marks[$row->studentID][$row->subjectID][$row->examID] = $row->mark;
    }
} 


when I dump the array $marks this is what i get,

PHP Code:

array
  90 => 
    array (size=2)
      14 => 
        array (size=4)
          18 => string '90' (length=2)
          25 => string '85' (length=2)
          30 => string '97' (length=2)
          31 => string '95' (length=2)
      17 => 
        array (size=4)
          18 => string '75' (length=2)
          25 => string '74' (length=2)
          30 => string '90' (length=2)
          31 => string '84' (length=2)
      
  91 => 
    array 
      14 => (size=2)
        array (size=4)
          18 => string '84' (length=2)
          25 => string '72' (length=2)
          30 => string '93' (length=2)
          31 => string '87' (length=2)
      17 => 
        array (size=4)
          18 => string '66' (length=2)
          25 => string '47' (length=2)
          30 => string '92' (length=2)
          31 => string '69' (length=2)       


so when i try to create a reference array i get false, It's maybe the dimension of an array has changed, how can I go around this.

PHP Code:

foreach($students as $student) {
$studentID = $student->student; //student ID
foreach($exams as $exam){
    $examID=$exam->examID; //exam ID    
    foreach($subjects as $subject) {
        $subjectID=$subject->subjectID; // subject ID
        $mark = isset($marks[$studentID][$subjectID][$examID]) ? $marks[$studentID][$subjectID][$examID] : false;
    }
} 


(09-27-2018, 12:09 AM)kayinja.denis Wrote: thanks @Pertti actually it worked I was wrongly selecting data before