I want to update one field in a database table but with this code(which works now if I fill in all
fields). All fields left blank are cleared. I need a solution.

<?php
$link = mysqli_connect("localhost", "root", "", "homedb"); 
// Check connection
if($link === false){ die("ERROR: Could not connect. " . mysqli_connect_error()); }

echo "<center>";echo date('m/d/y');echo "<br />";
   //Assign values to variables

$unit=$_POST['unit'];
$datereceived=$_POST['datereceived'];
$time=$_POST['time'];
$area=$_POST['area'];
$problem=$_POST['problem'];
$action=$_POST['action'];
$compday=$_POST['compday'];
$compmoyr=$_POST['compmoyr'];
$cost=$_POST['cost'];
$charge=$_POST['charge'];
$ordno=$_POST['ordno'];
$id = 'id';
 $sql = "UPDATE mainttbl SET 
datereceived = '$datereceived', time = '$time', area = '$area', problem = '$problem', action = '$action', 
compday = '$compday', compmoyr = '$compmoyr', cost = '$cost', charge = '$charge', 
ordno =  '$ordno' WHERE id = '$id' ";

 if(mysqli_query($link, $sql)){ echo "record was updated successfully."; } 
else { echo "ERROR: Could not able to execute $sql. " . mysqli_error($link); }
 // Close connection
mysqli_close($link); 
?>

    Welcome to the forums. I edited your post to wrap the code in [code]...[/code] tags, as that works much better than applying the </> format option in the edit window (which is really only suitable for in-line monospaced text).

    The direct answer to your issue would seem to be something like having an array of the possible field names, and looping through it to see which elements of the $_POST array with those keys exist and have a non-blank values, maybe adding those to another array you can then use to build the query.

    However, the thing that jumps out at me is that the sample code is ripe for SQL injection attacks, as you just take whatever is in the form post data and submit it to the database. Using prepared statements would help prevent this. Additionally, if I could talk you into using the PDO database extension (using a MySQL dsn) instead of MySQLi, you could use prepared statements to prevent those injection attacks, and also leverage the PDO ability to set an array of placeholders=>values that would mean you would not have to be concerned with the database field types. (It suppose it could be done in MySQLi prepared statements, but it's just a lot clumsier at it, in my opinion.)

      Write a Reply...