How to Insert or Update depending if values exist at database

Question

I'm trying to check if values school and lesson exist in Multiple_Choices table. If they exist an Update query stars, if not an Insert query. The purpose of this php file is to insert to mysql database questions for a multiple choice quiz or update the already existing ones. It posts id, question, 5 possible answers, a help for the user, school name and lesson name (school lesson).

Here is my php code (I also tried the get method):

<?php
 require('connect.php');

 $id = $_POST['id'];
 $erwtisi = $_POST['erwtisi'];
 $apantisi1 = $_POST['apantisi1'];
 $apantisi2 = $_POST['apantisi2'];
 $apantisi3 = $_POST['apantisi3'];
 $apantisi4 = $_POST['apantisi4'];
 $swstiapantisi = $_POST['swstiapantisi'];
 $voithia = $_POST['voithia'];
 $school = $_POST['school'];
 $lesson = $_POST['lesson'];


 function slugify($text){
    // replace non letter or digits by -
    $text = preg_replace('~[^\pL\d]+~u', '-', $text);
    return $text;
}
 $school=slugify($school);
 $lesson=slugify($lesson);


 $sql = "SELECT * FROM Multiple_Choices WHERE school = '$school' AND lesson = '$lesson'";
 $result = mysqli_query($conexao, $sql) or die ("Error: ") . mysqli_error($sql);
 $count = mysqli_num_rows($result);

 if ($count == 0) 
 {
    echo "insert";
    $sql3 = "INSERT INTO Multiple_Choices (id, Answer1, Answer2, Answer3,Answer4, Answer5, school, lesson) VALUES ('$id', '$apantisi1', '$apantisi2', '$apantisi3', '$apantisi4', '$swstiapantisi', '$school', '$lesson')";
    $sql4 = "INSERT INTO Questions (id, erwtiseis, apantiseis, help, school, lesson) VALUES ('$id', '$erwtisi', '$swstiapantisi', '$voithia', '$school', '$lesson')";
    $result3 = mysqli_query($conexao, $sql3) or die ("Error3: ") . mysqli_error($sql3);
    $result4 = mysqli_query($conexao, $sql4) or die ("Error4: ") . mysqli_error($sql4);      
    $count3 = mysqli_num_rows($result3);
    $count4 = mysqli_num_rows($result4);

    if (($count3 == 0) AND ($count4 == 0))
    {
        echo "error1";
    }
    else
    {
        echo "ok1";
    }
 }
 else
 {
    echo "update";
    $sql1 = "UPDATE Multiple_Choices SET Answer1 = '$apantisi1', Answer2 = '$apantisi2', Answer3 = '$apantisi3', Answer4 = '$apantisi4', Answer5 = '$swstiapantisi' WHERE school = '$school' AND id = '$id' AND lesson = '$lesson'";
    $sql2 = "UPDATE Questions SET erwtiseis = '$erwtisi', apantiseis = '$swstiapantisi', help = '$voithia' WHERE school = '$school' AND id = '$id' AND lesson = '$lesson'";
    $result1 = mysqli_query($conexao, $sql1) or die ("Error1: ") . mysqli_error($sql1);
    $result2 = mysqli_query($conexao, $sql2) or die ("Error2: ") . mysqli_error($sql2);
    $count1 = mysqli_num_rows($result1);
    $count2 = mysqli_num_rows($result2);

    if (($count1 == 0) AND ($count2 == 0))
    {
        echo "error2";
    } 
    else
    {
        echo "ok2";
    }
 }

?>

[Little Bobby](http://bobby-tables.com/) says **[you may be at risk for SQL Injection Attacks](https://stackoverflow.com/q/60174/)**. Learn about [Prepared Statements](https://en.wikipedia.org/wiki/Prepared_statement) with [parameterized queries](https://stackoverflow.com/a/4712113/5827005). I recommend `PDO`, which I [wrote a class for](https://github.com/GrumpyCrouton/GrumpyPDO) to make it extremely easy, clean, and more secure than using non-parameterized queries. Also, [This article](https://phpdelusions.net/pdo/mysqli_comparison) may help you choose between `MySQLi` and `PDO` — GrumpyCrouton, Oct 27 '17 at 18:29
The problem is that it doesn't work always. The first insert works, but then it echoes only ok2 even if I haven't inserted aany values, or wrong values (that doesn't exist in my database). With correct values it doesn't update, it only inserts. — Marvin001, Oct 31 '17 at 14:26

score 0 · Accepted Answer · answered Nov 01 '17 at 15:44

Ok I found my mistake. I changed the code to this and it works!

<?php
 require('connect.php');

 $id = $_POST['id'];
 $erwtisi = $_POST['erwtisi'];
 $apantisi1 = $_POST['apantisi1'];
 $apantisi2 = $_POST['apantisi2'];
 $apantisi3 = $_POST['apantisi3'];
 $apantisi4 = $_POST['apantisi4'];
 $swstiapantisi = $_POST['swstiapantisi'];
 $voithia = $_POST['voithia'];
 $school = $_POST['school'];
 $lesson = $_POST['lesson'];

 function slugify($text){
    // replace non letter or digits by -
    $text = preg_replace('/\s+/u', '_', $text);
    return $text;
 }
 $school=slugify($school);
 $lesson=slugify($lesson);

 $sql1 = "UPDATE Multiple_Choices SET Answer1 = '$apantisi1', Answer2 = '$apantisi2', Answer3 = '$apantisi3', Answer4 = '$apantisi4', Answer5 = '$swstiapantisi' WHERE school = '$school' AND id = '$id' AND lesson = '$lesson'";
 $sql2 = "UPDATE Questions SET erwtiseis = '$erwtisi', apantiseis = '$swstiapantisi', help = '$voithia' WHERE school = '$school' AND id = '$id' AND lesson = '$lesson'";
 $result1 = mysqli_query($conexao, $sql1) or die ("Error1: ") . mysqli_error($sql1);
 $result2 = mysqli_query($conexao, $sql2) or die ("Error2: ") . mysqli_error($sql2);

    if (mysqli_affected_rows($conexao)==0)
    {
        echo "error1";
        $sql3 = "INSERT INTO Multiple_Choices (id, Answer1, Answer2, Answer3, Answer4, Answer5, school, lesson) VALUES ('$id', '$apantisi1', '$apantisi2', '$apantisi3', '$apantisi4', '$swstiapantisi', '$school', '$lesson')";
        $sql4 = "INSERT INTO Questions (id, erwtiseis, apantiseis, help, school, lesson) VALUES ('$id', '$erwtisi', '$swstiapantisi', '$voithia', '$school', '$lesson')";
        $result3 = mysqli_query($conexao, $sql3) or die ("Error3: ") . mysqli_error($sql3);
        $result4 = mysqli_query($conexao, $sql4) or die ("Error4: ") . mysqli_error($sql4);

        if (mysqli_affected_rows($conexao)==0)
        {
            echo "error2";
        }
        else
        {
            echo "ok2";
        }
    }
    else
    {
        echo "ok1";
    }



?>

How to Insert or Update depending if values exist at database

1 Answers1