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

Does the variable have a quote in it? Look into prepared statements. Also check for errors so you know what is currently happening. — chris85
– chris85, Commented Apr 18, 2016 at 16:50
Little Bobby says your script is at risk for SQL Injection Attacks. Learn about prepared statements for MySQLi. Even escaping the string is not safe! — Jay Blanchard
– Jay Blanchard, Commented Apr 18, 2016 at 16:51
Maybe check first if you can connect successfully to database — Fil
– Fil, Commented Apr 18, 2016 at 17:19
Right after the query, do an "echo $result->error" and share with us the result here. — Dan H
– Dan H, Commented Apr 18, 2016 at 19:52
SQL injections aren't just about malicious users. They break your query because the data is being passed in incorrectly. Check for errors, guessing isnt that fun. — chris85
– chris85, Commented Apr 18, 2016 at 20:48

Arinlome · Accepted Answer · 2016-04-20 14:19:56Z

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.

answered Apr 20, 2016 at 14:19

Arinlome

113 bronze badges

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

Comments

Technoh · Accepted Answer · 2016-04-18 20:56:42Z

0

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'";

answered Apr 18, 2016 at 20:56

Technoh

1,60015 silver badges34 bronze badges

3 Comments

Reto Over a year ago

Isn't the id usually numeric? So why the quotes there?

chris85 Over a year ago

@Reto $id can be anything doesn't matter. The quotes aren't really important for integers, they will be corrected. Not quoting a string is less forgiving though. See this, stackoverflow.com/a/3308020/4333555, for more on quoting integers vs. not.

Arinlome Over a year ago

I tried adding the above code, but there was no change in the behavior of the page.

Collectives™ on Stack Overflow

PHP MySQL Update not working when using variable in WHERE clause

2 Answers 2

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related