update MySQL DB row using form values in PHP

Question

I have a simple html form page that needs to update 2 column values where 'Some_DB_Table'.id = the user-entered id number. However, I'm a little stuck on how to do that using prepared statements to avoid SQL Injection.

MY CODE:

HTML:

<form id="workorderMovement" name='workorderMovement_form' action="workordermovementGET.php" method="post">



<fieldset id="userid">

  <span>Welcome <?php echo $user ?> </span>

</fieldset> 




<fieldset id="sgnum">

<fieldset id="fieldset" style="text-align: center;"> 
  <span>Please enter the SG Number</span>
</fieldset>

<input type="text" name="sgnumber" id="sgnumber"> &nbsp;&nbsp;&nbsp; <input type="button" name="searchButton" id="searchButton" value="SEARCH">

</fieldset> 


<br/>
<br/>



<fieldset id="stageSelectField">

  <fieldset id="fieldset" style="text-align: center;"> 
    <span>Please select the Stage Completed</span>
  </fieldset>

<select name="stageSelect" id="stageSelect">
  <option value="Please Select">Please Select</option>
  <option value="Film Done">Film Done</option>
  <option value="Staged Done">Staged Done</option>
  <option value="Cleanroom Done">Cleanroom Done</option>
  <option value="GB2 Done">GB2 Done</option>
  <option value="Bagging Done">Bagging Done</option>
  <option value="Inspection Done">Inspection Done</option>
  <option value="LC Done">LC Inspection Done</option>
  <option value="IGU Done">IGU Done</option>
</select>

</fieldset> 


<br/>
<br/>


<fieldset id="floorNotesField">

  <fieldset id="fieldset" style="text-align: center;"> 
    <span>Please enter any new work order notes</span>
  </fieldset>

  <textarea type="text" name="floorNotes" id="floorNotes" class="floorNotesText"></textarea>

</fieldset>


<br/>
<br/>
<br/>

</form> <!-- End Work Order Movement Form -->

<fieldset id="doneButtonField">

  <input type="button" name="doneButton" id="doneButton" value="DONE">

</fieldset>

MY AJAX:

 j("#doneButton").click(function(){


 //send Workorder Movement Data values to php using ajax.

 var sgnumber = j('#sgnumber').val();
 var stageselect = j('#stageSelect').val();
 var floornotes = j('#floorNotes').val();
 j.ajax ({
    method: 'POST',
    url: "workordermovementUPDATE.php",
    data: {sgNumber: sgnumber, stageSelect: stageselect, floorNotes: floornotes},
    dataType: 'json',
    success: function( data ){
        alert(data);
    }
  });

});

MY PHP:

    <?php 


include('inc.php');


//Get Table Options.
if (isset($_POST['sgNumber'])) {
    $sgNumber = $_POST['sgNumber'];

    if (isset($_POST['stageSelect'])) {
        $stageSelect=$_POST['stageSelect'];
    }
    if (isset($_POST['floorNotes'])) {
        $floorNotes=$_POST['floorNotes'];
    }

    //connect  to the database 
    $conn = new mysqli($servername, $username, $password, $dbname);

    // Check connection
    if(mysqli_connect_errno() ) {
        printf('Could not connect: ' . mysqli_connect_error());
        exit();
    }

    $conn->select_db($dbname);

    if(! $conn->select_db($dbname) ) {
        echo 'Could not select database. '.'<BR>';
    }

    $sql= "UPDATE invoices SET productionstage = ".$stageSelect.", floornotes = ".$floorNotes." WHERE id = ?";
    $stmt = $conn->prepare($sql);
    $stmt->bind_param('i', $sgNumber);
    $stmt->execute();
    $stmt->store_result();     

    if(mysqli_query($conn, $stmt)){
        echo "".$sgnumber." Updated Successfully!";
    } else {
        echo "ERROR: Could not update ".$sgnumber."".mysqli_error($conn)."";
    }


////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////


//Free the result variable. 
 $result->free();


//Close the Database connection.
 $conn->close();

}//End If statement

?>

Is this correct/ any suggestions?

Thank you!

Since you're preparing things and making a bind_param call I'm not sure how you totally missed the boat on putting in placeholders for all values and not just some arbitrary ones. What is $stageSelect doing in there? Replace everything with ? and bind those values. — tadman
– tadman, Commented Dec 5, 2016 at 18:30
@tadman so something like: $sql= "UPDATE invoices SET productionstage = ?, floornotes = ? WHERE id = ?' and then do $stmt->bind_param('i', $sgNumber, $stageSelect, $floorNotes);? I'm not sure how to do the bind_param part to avoid SQL Injection when updating a table vs just SELECTing data from it. — rdimouro
– rdimouro, Commented Dec 5, 2016 at 18:58

tadman · Accepted Answer · 2016-12-05 19:27:33Z

2

If you read the documentation on bind_param carefully you'll see you need to specify the type of each parameter. Normally this isn't a big deal:

 $stmt = $conn->prepare(
   "UPDATE invoices SET productionstage=?,floornotes=? WHERE id = ?"
 );
 $stmt->bind_param('ssi', $stageSelect, $floorNotes, $sgNumber);

Try to avoid creating intermediate variables for statements. That can often lead to situations where you're inadvertently running the wrong query.

answered Dec 5, 2016 at 19:27

tadman

212k23 gold badges237 silver badges266 bronze badges

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

2 Comments

rdimouro Over a year ago

So I tested the update like this and my productionstage column updates correctly but floornotes does not. the floornotes column type is text so I've set my param_bind type as 's' like so: 'ssi' and my actual text just says 'test' but when I redo the search, the field returns empty and I can see in the database that the column for the row is also empty. I also don't receive any errors...the data type for column productionstage is 'varchar'. Could I be having an issue with param_bind and the column type of text?

rdimouro Over a year ago

Thank you so much! I was able to solve the issue. Turns out my code editor and my server were having communication difficulties that blocked some of my edits from going on the server. Upon fixing, everything worked perfectly.

Collectives™ on Stack Overflow

update MySQL DB row using form values in PHP

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related