How to keep the database table data unmodified if input variable is empty from php?

Question

I ran into a problem, say I have the following:

$stmt = $conn->prepare("UPDATE grades SET exam1=?, exam2=? WHERE user='$user' AND course='$course'");
$stmt->bind_param("ii", $exam1, $exam2);

I have 2 different variables $exam1, $exam2, All of these are assigned with form data from html. However, when I submit the form, I only want to update certain data within the table, let's say I update $exam1 and leave out the input field for the $exam2,

$exam1 = $_GET['exam1']; 
$exam2 = $_GET['exam2']; //I did not insert a value for exam2 input field

this will turn grades's exam2 field to 0, as opposed to its original value.

So is there any way to prevent the empty variable from modifying the table data? I could've done 2 separate prepare statements each for a different exam, but I feel like there is got to be a better way because what if there are many columns and I only wish to update some?

Check if you have a value for exam2 ahead of time. If you don't then select the current value from the database. Alternatively you could use the check to modify the query itself. — Cfreak
– Cfreak, Commented Nov 9, 2015 at 3:25
either you create the update columns dynamically (the only value that needs to be changed) or just include the same value — Kevin
– Kevin, Commented Nov 9, 2015 at 3:26

Lock · Accepted Answer · 2015-11-09 03:56:59Z

3

I would build your query dynamically. Something like:

$sql = "
  UPDATE
    grades
  SET
    exam1 = ?
";

if ( ! empty($exam2) ) {
  $sql.= " , exam2 = ? "
}
$sql.= " WHERE user='$user' AND course='$course'";


$stmt = $conn->prepare($sql);

if ( ! empty($exam2) ) {
  $stmt->bind_param("ii", $exam1, $exam2);
} else {
  $stmt->bind_param("i", $exam1);
}

By the way, why are you using bind parameters for your exam1 and exam2 values but not your user and course?

edited Nov 9, 2015 at 3:56

answered Nov 9, 2015 at 3:50

Lock

5,53217 gold badges71 silver badges116 bronze badges

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

Collectives™ on Stack Overflow

How to keep the database table data unmodified if input variable is empty from php?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related