MySQLi bind_param UPDATE不使用空值

Question

I have the following MySQL query that appears sound but wont update the table when one variable is empty

$stmtEmploymentDetails = getDB()->prepare(
    'UPDATE EmploymentDetails SET BranchOffice=?, EmploymentDate=?, EmploymentStatus=?, EmploymentType=?, OrdinaryHours=?, JobClassification=?, ManagmentGroupID=? WHERE EmployeeID=?'
);

and the binding param

$stmtEmploymentDetails->bind_param(
    'ssiiisss',
    $branchOffice,
    $employmentDate,
    $employmentStatus,
    $employmentType,
    $ordinaryHours,
    $jobClassification,
    $managmentGroupID,
    $employeeID
);

$stmtEmploymentDetails->execute;

I have also tried using COALESCE but the value (I'm assuming) is empty not null

$stmtEmploymentDetails = getDB()->prepare(
    'UPDATE EmploymentDetails SET BranchOffice=COALESCE(BranchOffice=?, BranchOffice), EmploymentDate=COALESCE(EmploymentDate=?, EmploymentDate), EmploymentStatus=COALESCE(EmploymentStatus=?, EmploymentStatus), EmploymentType=COALESCE(EmploymentType=?, EmploymentType), OrdinaryHours=COALESCE(OrdinaryHours=?, OrdinaryHours), JobClassification=COALESCE(JobClassification=?, JobClassification), ManagmentGroupID=COALESCE(ManagmentGroupID=?, ManagmentGroupID) WHERE EmployeeID=?'
);

This same approach works without any problems on a smaller query with

$stmtEmployeeProfile = getDB()->prepare(
    'UPDATE EmployeeProfiles SET AKAName=? WHERE EmployeeID=?'
);

and

$stmtEmployeeProfile->bind_param(
    'ss',
    $AKAName,
    $employeeID
);

$stmtEmployeeProfile->execute();

But in that query the values are always supplied. Anyone know where the problem is?

Answer 1

Have you put any error reporting around the query to see what is actually happening?

if ($stmtEmploymentDetails = getDB()->prepare(
'UPDATE EmploymentDetails 
 SET BranchOffice=?, EmploymentDate=?, 
 EmploymentStatus=?, EmploymentType=?, 
 OrdinaryHours=?, 
 JobClassification=?, 
 ManagmentGroupID=? 
 WHERE EmployeeID=?')) { 
    $stmtEmploymentDetails->execute(); 
 } else {
 echo "Failed prepare statement" . $this->conn->error . $this->conn->error;
 }

I hope that helps a little bit, when you have this error then work from that. Could be an issue of spelling or you haven't done the correct checks for values being empty. Way of doing this is a simple isset() statement such as.

if (isset($stmtEmplotmentDetails)) {
// do something 
} else {
$stmtEmplotmentDetails = 0; // or default value e.g. "N/A"
}

Also thinking on from have you checked the datatypes are correct to corrospond with the database, you have strings and integers in your bind parameter query. Do they match up with the datatype within your database?