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);
}
}
?>