Comparing columns from Database through PHP

Question

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

This is what I mean:

I’m comparing both tables using a VARCHAR (YES or NO). If the user has changed the difficulty of the exercise, the cell will be 'YES', if it has not been changed, 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 rows of the ANSWERS table don’t exist until the user presses the SUBMIT button in the program. So I have only managed to compare the columns that are in the table using the commands below in Mysql-Workbench.

What I need is to compare the columns in difficulty_change when the user presses SUBMIT. Can you help me do it? I can 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, it might 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);

    }
}
?>

This code contains the same SQL injection vulnerabilities as your recently deleted question. Do not go live with this - this exposes you and your users to a potential security risk. — halfer
– halfer, Commented Aug 17, 2018 at 14:15

galep · Accepted Answer · 2018-08-16 11:59:03Z

1

Try:

$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 Aug 16, 2018 at 11:59

galep

887 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user2876341 · Accepted Answer · 2018-08-16 10:41:26Z

0

Use your submit step to only update the db. Add code to query the db again to retrieve the data. Add a line to call your display page again (your display page calls itself) to display your updated table.

answered Aug 16, 2018 at 10:41

user2876341

11 bronze badge

1 Comment

ana Over a year ago

Can you help me do it? I dont know how to

Collectives™ on Stack Overflow

Comparing columns from Database through PHP

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related