-1

I am using bind_param to update data in my SQL database. When all fields are filled, the form submits successfully but if any field is left blank the database doesn't updates. I don't know what's wrong with my code.

Here is my html form

<form id="msform" action="includes/cand-reg-dbs.php" method="POST">
<label>Your Full Name</label>
<input type="text" name="full-name"><br />
<label>Your Date of Birth</label>
<input type="date" name="dob"><br />
<label>Father's Name</label>
<input type="text" name="f-name"><br />
<label>Mother's Name</label>
<input type="text" name="m-name"><br />
<label>Address</label>
<input type="text" name="address"><br />
<label>City</label>
<input type="text" name="city"><br />
<label>Pincode</label>
<input type="text" name="pincode"><br />
<input type="submit" name="submit" id="submit-button" class="" value="Save" /></form>

Here is the action of the above form cand-reg-dbs.php

session_start();

if (isset($_POST['submit'])) {

    $dbServername = "localhost";
    $dbUsername = "root";
    $dbPassword = "";
    $dbName = "jobin";

    $uid = $_SESSION['username'];

    $con = new mysqli($dbServername, $dbUsername, $dbPassword, $dbName);

if ($con->connect_error) {
die("Connection failed: " . $con->connect_error);
}

$stmt = $con->prepare("UPDATE users SET Full_Name = ?, DOB = ?, f_Name = ?, m_Name= ?, Address = ?, City = ?, Pincode = ? WHERE Username = ?");
$stmt->bind_param("ssssssis", $fullName, $dob, $fName, $mName, $address, $city, $pincode, $uid);

    $fullName = $_POST['full-name'];
    $dob = $_POST['dob'];
    $fName = $_POST['f-name'];
    $mName = $_POST['m-name'];
    $address = $_POST['address'];
    $city = $_POST['city'];
    $pincode = $_POST['pincode'];

    echo "New records created successfully";

    $stmt->execute();
    $stmt->close(); 
    $con->close();
}

In the users table the default value are-

Full_Name VARCHAR(40) default value is set to ''
DOB date default value is set to 12-12-2012
f_Name VARCHAR(40) default value is set to ''
m_Name VARCHAR(40) default value is set to ''
Address VARCHAR(120) default value is set to ''
City VARCHAR(25) default value is set to ''
Pincode int(6) default value is set to 0
Sandeep
  • 1
  • 4
  • 3
    Does it give any errors when it fails? Check for [mysqli errors](http://php.net/manual/en/mysqli.error.php) – aynber Jan 19 '18 at 17:27
  • 1
    `DOB date default value is set to 12-12-2012` - Huh? That can't be right. – Funk Forty Niner Jan 19 '18 at 17:34
  • You also don't have a column bearing the `Username` name. – Funk Forty Niner Jan 19 '18 at 17:34
  • 40 characters for a full name is ridiculously stingy. Use `VARCHAR(255)` for any "string type" columns by default and only alter that default if you've got a compelling reason. – tadman Jan 19 '18 at 17:36
  • Instead of smashing around with low-level PHP, why not use a library that does this for you? There are many high-quality ORMs for PHP like [Doctrine](http://www.doctrine-project.org/), [Propel](http://propelorm.org/) and [Eloquent](https://laravel.com/docs/master/eloquent) that take care of composing queries and updating your data. – tadman Jan 19 '18 at 17:37
  • "*When all fields are filled, the form submits successfully but if any field is left blank the database doesn't updates.*" ... do you mean this is what HAPPENS (please echo out mysql errors if so)... or do you mean, this is what you WANT TO HAPPEN... as in, you dont want it to update the database with empty fields? – IncredibleHat Jan 19 '18 at 17:43
  • You could try something like using `coalesce()` in your update SQL to leave a field value in place if null is passed in - i.e. `Full_Name = coalesce(?,Full_Name)`. But this assumes the value is null and not just empty ('') – Nigel Ren Jan 19 '18 at 17:47
  • I have checked for errors the first one says `Fatal error: Uncaught exception 'mysqli_sql_exception' with message 'Incorrect date value: '' for column 'DOB' at row 1' ` – Sandeep Jan 19 '18 at 17:56
  • Second error `mysqli_sql_exception: Incorrect date value: '' for column 'DOB' at row 1` – Sandeep Jan 19 '18 at 17:57

1 Answers1

0

May be using isset will help this

 $fullName = isset($_POST['full-name'])?$_POST['full-name']:'';
 $dob = isset($_POST['dob'])?$_POST['dob']:'2018-01-18';

and same on for all the fields

Sanjun Dev
  • 518
  • 8
  • 20
  • How to write similar statement for `$dob` where the default value is set to `2018-01-18`. I tried, the browser gives some error `Parse error: syntax error, unexpected '$dob' (T_VARIABLE)` – Sandeep Jan 19 '18 at 17:48
  • $dob = isset($_POST['dob'])?$_POST['dob']:'2018-01-18'; is that you meant to want? – Sanjun Dev Jan 19 '18 at 17:58