PHP MySQL Update not working when using variable in WHERE clause

Question

I've checked dozens of threads on here and on other sites, and I cannot figure out why my code is not working. I am trying to use PHP to update MySQL using a variable to identify WHERE. The code I have works if I swap the variable for a number, and the variable works everywhere else in my script. It's just this one line that does not.

The line in question is:

$change = "UPDATE reg_info SET fname='$fname', lname='$lname', email='$email', explevel='$experience', addinfo='$additional', event='$regEvent' where id='$id'";

I've also tried the following:

$change = mysqli_query("UPDATE reg_info SET fname='$fname', lname='$lname', email='$email', explevel='$experience', addinfo='$additional', event='$regEvent' where id='$id'");

$change = "UPDATE reg_info SET fname='$fname', lname='$lname', email='$email', explevel='$experience', addinfo='$additional', event='$regEvent' where id=".$id;

$change = 'UPDATE reg_info SET fname="'.$fname.'", lname="'.$lname.'", email="'.$email.'", explevel="'.$experience.'", addinfo="'.$additional.'", event="'.$regEvent.'" where id='.$id;

From what I've seen on other threads, at least one of these should worked for me.

Can anyone point me in the right direction, please?

If it helps the entire string of PHP code is:

    <?php
$fnameErr = $lnameErr = $emailErr = $experienceErr = $regEventErr = "";
$fname = $lname = $email = $experience = $regEvent = "";
$id = $_GET["id"];
$errors = "yes";
$servername = "localhost";
$username = "root";
$password = "5tTtFzaz6dIO";
$database = "project2db";
$conn = new mysqli($servername, $username, $password, $database);
$query = mysqli_query($conn, "SELECT * FROM reg_info where id=".$id);
$row = mysqli_fetch_array($query, MYSQLI_NUM);

    if ($_SERVER["REQUEST_METHOD"] == "POST") {
     if (empty($_POST["fname"])) {
       $fnameErr = "First name is required";
       $errors = "yes";
     } else {
       $fname = test_input($_POST["fname"]);
       if (!preg_match("/^[a-zA-Z ]*$/",$fname)) {
         $fnameErr = "Only letters and white space allowed";
         $errors = "yes";
       }
       else {
        $errors = "no";
       }
     }

     if (empty($_POST["lname"])) {
       $lnameErr = "Last name is required";
       $errors = "yes";
     } else {
       $lname = test_input($_POST["lname"]);
       if (!preg_match("/^[a-zA-Z ]*$/",$lname)) {
        $lnameErr = "Only letters and white space allowed";
        $errors = "yes";
       }
       else {
        $errors = "no";
       }
     }

     if (empty($_POST["email"])) {
       $emailErr = "Email is required";
       $errors = "yes";
     } else {
       $email = test_input($_POST["email"]);
       if (!filter_var($email, FILTER_VALIDATE_EMAIL)) {
        $emailErr = "Invalid email address";
        $errors = "yes";
       }
       else {
        $errors = "no";
       }
     }

     if (empty($_POST["experience"])) {
       $experienceErr = "Experience level is required";
       $errors = "yes";
     } else {
       $experience = test_input($_POST["experience"]);
       $errors = "no";
     }

     if (empty($_POST["additional"])) {
       $regEvent = "";
     } else {
       $additional = test_input($_POST["additional"]);
     }

     if (empty($_POST["regEvent"])) {
       $regEventErr = "Event is required";
       $errors = "yes";
     } else {
       $regEvent = test_input($_POST["regEvent"]);
       $errors = "no";
     }

     if($errors == "no") {
        $change = 'UPDATE reg_info SET fname="'.$fname.'", lname="'.$lname.'", email="'.$email.'", explevel="'.$experience.'", addinfo="'.$additional.'", event="'.$regEvent.'" where id='.$id;
        $result=$conn->query($change);

        if ($result) {
             echo '<script language="javascript">';
             echo 'alert("New record created successfully.")';
             echo '</script>';
             header('Location: regtable.php');
        } else {
             echo '<script language="javascript">';
             echo 'alert("Error. New record not created.")';
             echo '</script>';
             header('Location: regtable.php');
        }
     }

    }

    function test_input($data) {
    $data = trim($data);
    $data = stripslashes($data);
    $data = htmlspecialchars($data);
    return $data;
    }
    ?>

Answer 1

I figured out the issue! Whenever the form was submitted, the new POST data did not have anything assigned to the html id="id" that was passed into the PHP code to create the $id variable.

Since there was nothing in the form, $id was null, and thus the query did not update the database, even though the query and connection were completely valid.

Thanks to everyone who posted comments and advice, I really appreciate it.

Answer 2

Since the query in itself is valid, I can only guess that somehow the data is causing the issue. Try the following, which escapes every value that will be used in the query:

$fname = mysqli_real_escape_string( $conn, $fname );
$lname = mysqli_real_escape_string( $conn, $lname );
$email = mysqli_real_escape_string( $conn, $email );
$experience = mysqli_real_escape_string( $conn, $experience );
$additional = mysqli_real_escape_string( $conn, $additional );
$regEvent = mysqli_real_escape_string( $conn, $regEvent );
$id = mysqli_real_escape_string( $conn, $id );

$change = "UPDATE reg_info SET fname='$fname', lname='$lname', email='$email', explevel='$experience', addinfo='$additional', event='$regEvent' where id='$id'";