Which SQL request should i use?

Question

I have tables Skills and Result

___ Matieres ___             ___ Resultats ___
id (int auto increment)      ID (int auto_increment)
Nom (varchar)            TIME_OF_INSERTION (datetime current_timestamp)
Categorie (varchar)          ID_USER (int)
Active (boolean)             ID_MATIERE (int)
                             RESULTAT (int)

And I have this code to create a form based on the data of the query:

$req = $bdd->query(SELECT * FROM Matieres LEFT JOIN Resultats ON Matieres.id = Resultats.ID_MATIERE WHERE Active = TRUE);

$skills = [];

while( $data = $req->fetch()) {

    array_push( $skills, [ $data['id'], $data['Nom'], $data['RESULTAT'] ] );

}

function setWidgetValue( $skill ) {
    
    if(is_null($skill[2])) {
        $value = 0;
    } else {
        $value = $skill[2];
    }

    $widget = "<div class='m-5'><p>".$skill[1]."</p><input type='range'  value='" . $value ."' class='form-control-range' min='0' step='1' max='10' id='".$skill[0]."' name='valSkill' onchange=\"MAJ_Value( ".$skill[0].", this.value, " . $_SESSION['id'] . " )\" ></div>";
    return $widget;

}


function setAllWidgetValue( $skills  ) {

    $widget = "<div id='valSkills' >\n";
    foreach( $skills as $skill )
        $widget .= setWidgetValue( $skill );
    $widget .= "</div>";
    return $widget;

}

The problem is that the value of my inputs should be 0 if the user didn't already send them and it works fine if one user does it. But if another user tries to access the form, the inputs will have the values that the previous user submitted.

I think the problem is coming from the SQL request but I can't find the one working perfectly :( I know I have to pass the id of the user but don't know where write it in the request

Answer 1

A little bit on SQL

You seem to have a one-many relationship between your Matieres table and your Resultat table?

When you use a SELECT...JOIN statement in this scenario you effectively return the Matieres for as many times as there are matches in Resultat.

For example:

1. Assume you have a skill/matiere can touch toes
2. Assume there are 5 records in Resultat for users who have that skill

With your current query structure you'd do something like:

__SQL:

SELECT Resultat.ID_USER, Matieres.Nom, Result.RESULTAT
FROM Matieres
    LEFT JOIN Resultat ON Matieres.id = Result.ID_SKILL


__RESULTS:

Resultat.ID_USER     Matieres.Nom        Resultat.RESULTAT
1                    can touch toes      50
2                    can touch toes      71
3                    can touch toes      23
4                    can touch toes      88
5                    can touch toes      20

To change this behaviour you need to specify additional conditions in your ON clause:

__SQL:

SELECT Resultat.ID_USER, Matieres.Nom, Result.RESULTAT
FROM Matieres
    LEFT JOIN Resultat
        ON Matieres.id = Result.ID_SKILL
            AND Resultat.ID_USER = 1

__RESULTS:

Resultat.ID_USER     Matieres.Nom        Resultat.RESULTAT
1                    can touch toes      50

PHP while statement echoes duplicates : take a look at answers here for further information and examples

ON not WHERE

The reason the additional condition has to go into the ON clause and not the WHERE clause is that you want to get every Active skill/matiere regardless whether someone has already got a Resultat for it.

If we were to put it into the WHERE clause instead we'd get:

__SQL: 

SELECT Resultat.ID_USER, Matieres.Nom, Result.RESULTAT
FROM Matieres
    LEFT JOIN Resultat
        ON Matieres.id = Result.ID_SKILL
WHERE Resultat.ID_USER = 1

__RESULTS:

Resultat.ID_USER     Matieres.Nom        Resultat.RESULTAT
1                    can touch toes      50

Which seems okay until you factor in that you have multiple skills/matieres and the user doesn't have a record for each of them... For example:

__TABLES

Matieres                         Resultat
id           Nom                 ID_USER     ID_MATIERE      RESULTAT
111          can touch toes      1           111             54
112          can handstand       1           112             60
113          can cartwheel       2           113             72

.

__SQL[1]: {For ID_USER == 1; using ON}

SELECT Resultat.ID_USER, Matieres.Nom, Result.RESULTAT
FROM Matieres
    LEFT JOIN Resultat
        ON Matieres.id = Result.ID_SKILL
            AND Resultat.ID_USER = 1

__RESULT[1]: 

Matieres                         Resultat
id           Nom                 ID_USER     ID_MATIERE      RESULTAT
111          can touch toes      1           111             54
112          can handstand       1           112             60
113          can cartwheel       NULL        NULL            NULL


__SQL[2]: {For ID_USER == 2; using ON}

SELECT Resultat.ID_USER, Matieres.Nom, Result.RESULTAT
FROM Matieres
    LEFT JOIN Resultat
        ON Matieres.id = Result.ID_SKILL
            AND Resultat.ID_USER = 2

__RESULT [2]:

Matieres                         Resultat
id           Nom                 ID_USER     ID_MATIERE      RESULTAT
111          can touch toes      NULL        NULL            NULL
112          can handstand       NULL        NULL            NULL
113          can cartwheel       2           113             72


__SQL[3]: {For ID_USER == 1; using WHERE}

SELECT Resultat.ID_USER, Matieres.Nom, Result.RESULTAT
FROM Matieres
    LEFT JOIN Resultat
        ON Matieres.id = Result.ID_SKILL
WHERE Resultat.ID_USER = 1

__RESULT[3]: 

Matieres                         Resultat
id           Nom                 ID_USER     ID_MATIERE      RESULTAT
111          can touch toes      1           111             54
112          can handstand       1           112             60


__SQL[4]: {For ID_USER == 2; using WHERE}

SELECT Resultat.ID_USER, Matieres.Nom, Result.RESULTAT
FROM Matieres
    LEFT JOIN Resultat
        ON Matieres.id = Result.ID_SKILL
WHERE Resultat.ID_USER = 2

__RESULT[4]: 

Matieres                         Resultat
id           Nom                 ID_USER     ID_MATIERE      RESULTAT
113          can cartwheel       2           113             72

Your question

Assumptions

1. You have a user logged on to the website where $_SESSION["id"] == Resultat.ID_USER
2. You want to SELECT all Matieres.id && Matieres.Nom AND Resultat.RESULTAT for any skill already logged for that Resultat.ID_USER
3. You're using PDO and $bdd is your connection to the DataBase

Example Code

// SQL statement using `?` as a placeholder for the users id (from $_SESSION["id"]
// Add: "AND Resultats.ID_USER = ?" to ON clause to limit joined records to currently logged in user
$sql = "    
    SELECT Matieres.id, Matieres.nom, Resultats.RESULTAT
    FROM Skills
        LEFT JOIN Resultats ON Matieres.id = Resultats.ID_MATIERE AND Resultats.ID_USER = ?
    WHERE Matieres.Active = TRUE
";

$req = $bdd->prepare($sql);         // Prepare query
$req->execute([ $_SESSION["id"] ]); // Assign value to place holder and execute query

$skills = [];
while( $data = $req->fetch() ) {
    $skills[] = [
        "id"=>$data["id"],
        "Nom"=>$data["Nom"],
        "RESULTAT"=>$data["RESULTAT"]
    ];
}

function setWidgetValue( $skill ) {

    // Set values outside of "widget" string for ease of reading
    $value    = ( is_null($skill["RESULTAT"]) ) ? 0 : $skill["RESULTAT"]; // Shorthand if statement using the ternary operator
    $nom      = $skill["Nom"];   
    $skill_id = $skill["id"];  
    $user_id  = $_SESSION["id"];

    // I've separated the code over several lines to make 
    // it easier to read you can of course change it back!
    $widget   = "
        <div class='m-5'>
            <p>{$nom}</p>
            <input
                type='range' 
                value='{$value}'
                class='form-control-range'
                min='0' step='1' max='10'
                id='{$skill_id}'
                name='valSkill'
                onchange=\"MAJ_Value( {$skill_id}, this.value, {$user_id} 
                )\" >
        </div>
    ";
    return $widget;
}

function setAllWidgetValue( $skills ) {
    $widget = "<div id='valSkills' >\n";
    foreach( $skills as $skill )
        $widget .= setWidgetValue( $skill );
    $widget .= "</div>";
    return $widget;
}

Answer 2

You'll have to put the user id in the join, not in the where clause:

SELECT * FROM Matieres 
LEFT JOIN Resultats 
  ON Matieres.id = Resultats.ID_MATIERE 
  AND Resultats.ID_USER = :ID_USER
WHERE Active = TRUE

:ID_USER would be your input parameter. If you enter a non-existent user id or an id that doesn't exist yet in Resultats, you'll still get all the Skills listed, but with NULLs for the user's values.