Compare 2 tables in the database using PHP

1

I'm doing a program where I compare in a database (Mysql-Workbench) 2 columns (difficulty, difficulty_student) of 2 different tables (EXERCISES, ANSWERS) in a column difficulty_choice of the table < strong> ANSWERS .

I mean this:

I compare both tables using a VARCHAR (YES or NO). If the user has changed the difficulty of the exercise, the cell will be 'YES', if he has not changed it, the cell will be 'NO'.

These are my tables:

CREATE TABLE exercises (
    exercise_id INT,
    difficulty VARCHAR(30),
    PRIMARY KEY(exercise_id)
);

CREATE TABLE answers(
    exercise_id_fk INT,
    student_id INT,
    difficulty_change VARCHAR(3),
    difficulty_student VARCHAR(30),
    FOREIGN KEY(exercise_id_fk) REFERENCES exercises(exercise_id)
);

My problem is that the lines of the ANSWERS table do not exist until the user press SUBMIT in the program. So I have only managed to compare the columns that are in the table by the commands below in Mysql-Workbench.

What I need is for the columns to be compared in difficulty_change when the user press SUBMIT . Can you help me do it? I do not get it.

I have managed to compare the columns using the following codes but I want them to be called from the program so that I do not have to go to Mysql-Workbench every time to execute them.

SELECT e.difficulty, a.difficulty_student, 
case when e.difficulty = a.difficulty_student then 'NO' else 'YES' 
         END  as difficulty_change
FROM exercises e 
INNER JOIN answers a  on e.exercise_id=a.exercise_id_fk;


UPDATE answers a 
INNER JOIN exercises e  on e.exercise_id=a.exercise_id_fk 
set a.difficulty_change = case 
     when e.difficulty = a.difficulty_student then 'NO' else 'YES' END
where e.exercise_id=a.exercise_id_fk;

This is my php that may help:

<?php

if (isset($_POST['submit'])) {
    $user_id = $_SESSION['user_id'];
    $user_check_query = "SELECT * FROM users WHERE id='$user_id'";
    if(isset($_POST['choice'], $_POST['choose'])){
            $choice_answer=$_POST['choice'];
            $difficulty=$_POST['choose'];
//      */$user_id = $_SESSION['user_id'];*/
            $query = "INSERT INTO answers (exercise_id_fk, student_id, difficulty_student, choice_answer) VALUES ('$id','$user_id', '$difficulty', '$choice_answer')";
            $sql=mysqli_query($conn,$query);

    }
}
?>
    
asked by ana 16.08.2018 в 13:03
source

1 answer

0

a subquery

$query = "INSERT INTO answers (exercise_id_fk, student_id, difficulty_change, difficulty_student, choice_answer) VALUES ('$id','$user_id', (SELECT IF(difficulty='$difficulty','NO','YES') FROM exercises WHERE exercise_id=$id), '$difficulty', '$choice_answer')";
    
answered by 16.08.2018 / 13:48
source