I want to update database table CERTTBL from a form. I only enter the UNIT and DAYSLEFT as I only want to update the daysleft of that unit. Please explain the result? ------------------------------------------------------------ <?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 />"; $unit=$_POST['unit']; $tenant=$_POST['tenant']; $effectdate=$_POST['effectdate']; $expiredate=$_POST['expiredate']; $moveindate=$_POST['moveindate']; $daysleft=$_POST['daysleft']; $date90=$_POST['date90']; $date60=$_POST['date60']; $date30=$_POST['date30']; $sql = "UPDATE certtbl SET unit = '$unit', tenant = '$tenant', effectdate = '$effectdate', expiredate = '$expiredate', moveindate = '$moveindate', daysleft = '$daysleft', date90 = '$date90', date60 = '$date60', date30 = '$date30' where unit = $unit"; 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); ?> ------------------------------------------------------------------------------- the result: ERROR: Could not able to execute UPDATE certtbl SET unit = 'unit1', effectdate = '', expiredate = '', moveindate = '', daysleft = '500', date90 = '', date60 = '', date30 = '' where unit = unit1. Unknown column 'unit1' in 'where clause'
Unknown column 'unit1' in 'where clause' ... that's beacuse when you build the query, you forgot the ' Should be .... date30 = '$date30' where unit = '$unit' "; PHP:
Thanks so much. 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); ?> ---------------------------------- the result: record was updated successfully
No, of course. You should check the value of the input and, if != from empty, then use that value. Eg (simplyfied!) $unit = $_POST [ 'unit' ]; $sql = "UPDATE mainttbl SET "; if (trim( $_POST [ 'datereceived' ] ) !== "") { $datereceived = $_POST [ 'datereceived' ]; $sql_upd [ ] = " datereceived = '{$datereceived}' "; } if (trim( $_POST [ 'area' ] ) !== "") { $area = $_POST [ 'area' ]; $sql_upd [] = " area = '{$area}' "; } .... if (sizeof($sql_upd)) { $sql .= implode(",", $sql_upd); $sql .= " 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); PHP:
This is 2023 not 1998. You have NO business blindly dumping variables into a query string with no sanitization or safety. Or even with such. Just because you replaced "mysql_" with "mysqli_" doesn't mean you get a free ride on that broken garbage insecure practice that we've been told to stop using for over 17 years. I'm a little terrified and dismayed nobody pointed that out, and in fact in some cases doubled down on the double-dumbass $sql string approach. But I guess that goes with the use of the garbage <center> tag as if it's still 1997 as well. The multiple echo for nothing doing you no favors either. Stunning usage scenario for showing where PDO can really excel. <?php echo ' <h1>', date('m/d/y'), '</h1>'; $db = new PDO( 'mysql:dbname=homedb;host=localhost', // dsn 'root', // username '', // password [ PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, PDO::ATTR_EMULATE_PREPARES => false ] ); $stmt = $db->prepare(' UPDATE certtbl SET tenant = ?, effectdate = ?, expiredate = ?, moveindate = ?, daysleft = ?, date90 = ?, date60 = ?, date30 = ? WHERE unit = ? '); $stmt->exec([ $_POST['tenant'], $_POST['effectdate'], $_POST['expiredate'], $_POST['moveindate'], $_POST['daysleft'], $_POST['date90'], $_POST['date60'], $_POST['date30'], $_POST['unit'] ]); echo ( $stmt->rowCount() ? 'record was updated successfully.' : 'record ', htmlspecialchars($_POST['unit']), 'not found or update failed.' ); Code (markup): Though you should have a lot more checks in there of the user values.
I loved "dascos" 's answer too, I will minify that one further; but better version is with PDO provided by "deathshadow". $where_clause = []; #Separate Where clause elements foreach (['id', 'unit'] as $field) //fields that will be part of where clause { $where_clause []= "`{$field}` = '".$_POST[$field]."'"; unset($_POST[$field]); } array_walk($_POST, function (&$item, $key){ $item = "`$key`='{$item}'"; }); #Form value updates part $query = 'UPDATE mainttbl SET '.implode(',', $_POST).' WHERE '.implode(' AND ', $where_clause).';'; #Create Query mysqli_query($link, $query) or die(mysqli_error($link)); PHP:
Oh for the love of Christmas NO! DO NOT SHIT VARIABLES INTO YOUR QUERY STRINGS! Especially something like the keys inside $_POST given how easy it is client-side to bullshit that, possibly screwing with other fields in the same table. You're BEGGING For XSS exploits with that crap. If you can't hardcode the query, you probably shouldn't be building a query... but if you "have to" you should be using a whitelist of fields to use/lookup, NOT pulling it from user generated values!
My Goodness ! Are you sure you are OK? READ THE GOD DAMN REPLY AGAIN {but better version is with PDO provided by "deathshadow"}, the questioner doesn't even even have idea what the HECK is PDO, and I just minified the crap, nothing more than that. I know what the heck is XSS and how to prevent that, I kept it simple as possible keeping in view the level of user ! DONT ..... TRY TO FIND A WAY TO ADD YET ANOTHER REPLY AND ASSUME ALL ARE DUMMIES HERE. Correct your tone and think at least once before posting such crap responses again; seriously it was bullshit. >> htmlspecialchars($_POST['unit']) Who on earth uses "htmlspecialchars" for integers ? use intval() next time please !