CODEWITHSUNDEEP
phpmysql
rdimouro
rdimouro

Reputation: 223

update MySQL DB row using form values in PHP

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!

Upvotes: 0

Views: 78

Answers (1)

tadman
tadman

Reputation: 211590

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.

Upvotes: 2

Related Questions