How to use Laravel to promote students from one grade to another-CodePudding

I have a raw php code which promote students from one grade to another upon a submission and it works fine. I want to use Laravel to do that and I seem not to get it. I need assistance and to learn from it.

public function updateClass() {

    $QueryStudent  = "
    UPDATE students
    SET CurrentClass = (
    CASE CurrentClass
      WHEN 'Grade 1' THEN 'Grade 2'
      WHEN 'Grade 2' THEN 'Grade 3'                                               
      WHEN 'Grade 3' THEN 'Grade 4'                                                  
      WHEN 'Grade 4' THEN 'Grade 5'                                                     
      WHEN 'Grade 5' THEN 'Grade 6'                                                                                                                                            
      WHEN 'Grade 6' THEN 'Graduate'                                         
    END
    )
    WHERE CurrentClass IN ('Grade 1', 'Grade 2', 'Grade 3', 'Grade 4', 'Grade 5', 'Grade 6')";

    $StudentStmt = $Connection->query($QueryStudent);
}

CodePudding user response：

The easiest way is to use statement method:

    DB::statement("    
        UPDATE students
        SET CurrentClass = (
        CASE CurrentClass
          WHEN 'Grade 1' THEN 'Grade 2'
          WHEN 'Grade 2' THEN 'Grade 3'
          WHEN 'Grade 3' THEN 'Grade 4'
          WHEN 'Grade 4' THEN 'Grade 5'
          WHEN 'Grade 5' THEN 'Grade 6'
          WHEN 'Grade 6' THEN 'Graduate'
        END
        )
        WHERE CurrentClass IN ('Grade 1', 'Grade 2', 'Grade 3', 'Grade 4', 'Grade 5', 'Grade 6')
     ");

This will update all the records in the table students. Don't forget to add this line before the class definition :

use Illuminate\Support\Facades\DB;

if you want to update particular record by id you need to combine the statements method with the raw method:

    DB::statement(
        DB::raw("
            UPDATE students
            SET CurrentClass = (
            CASE CurrentClass
              WHEN 'Grade 1' THEN 'Grade 2'
              WHEN 'Grade 2' THEN 'Grade 3'
              WHEN 'Grade 3' THEN 'Grade 4'
              WHEN 'Grade 4' THEN 'Grade 5'
              WHEN 'Grade 5' THEN 'Grade 6'
              WHEN 'Grade 6' THEN 'Graduate'
            END
            )
            WHERE 
                  id = ? AND
                  CurrentClass IN ('Grade 1', 'Grade 2', 'Grade 3', 'Grade 4', 'Grade 5', 'Grade 6')
            "),
        array($user_id)
    );

CodePudding user response：

It's questionable to store that as VARCHAR ...just book each graduation into a table and then SUM() up. Laravel Eloquent won't make a flawed business logic any less flawed, but probably only more obvious. There are no test scores and graduation events being recorded. The above function may even run at any given time, without any chance to undo the result of the operation - and without any chance to know that it even happened. In short: This won't survive in the real world.

I'd suggest to start with a few Model; eg. Student (or User and Role), TestScore and Grade. Alike this one could also establish the conditions, when a student is even eligible for an upgrade ... Better read about Eloquent, to begin with and ask yourself how grades do work in the real world?