Welcome Guest, Not a member yet? Register   Sign In
Display Loading Image While Page Loads
#11

(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.

Cool, so that confirms it from two sources Cool Next time tho say database view, saves me being smartass and telling people how views and HTML work Big Grin
Reply
#12

@Pertti,

Sometimes I type slower then I'm thinking. It happens. No worries.
Reply
#13

(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.
 thank you sir,
am going to go through your solution and I'll give you the feedback, currently I've been busy
Reply
#14

(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.

Hello
thanks but that query is already in view.... I run it from it i didn't use either the model or controller to query the marks, rather view.
Maybe I've not understood you better, or please show me what u are trying to mean here.
otherwise i'll try to first consider @Pertti  solution.
Reply
#15

@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.
Reply
#16

(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.

in other words i create views in a database? uhmm....sound great I wanna try it too...
otherwise I've not used views before I guess I'll to get....
Reply
#17

@kayinja.denis,

Maybe when I get an extra few moments I will take the information that you provided here and show you what I mean.
Reply
#18

(This post was last modified: 09-27-2018, 12:08 AM by kayinja.denis.)

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

Reply
#19

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


thanks @Pertti actually it worked I was wrongly selecting data before
Reply
#20

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

Excellent Smile
Reply




Theme © iAndrew 2016 - Forum software by © MyBB