PDO Update query neither successful nor showing any error

Question

Here is the way I am making an update query the query is running but without success and nor error result. While i have manually tested the query in MySql workbench with parameters and all are working fine. What I am missing. I am also not know why it is not throwing any exception or error if the it is not successful.

function UpdateLeaveDetailRequestStatus(){

    global $connPDO;
    date_default_timezone_set("Asia/Karachi");
    //$connPDO->beginTransaction();
    $currentDate = date('Y-m-d H:i:s');

    $binds = array(
        ":leave_start_date"                     =>   $_POST["leaveStartDate"],
        ":leave_end_date"                       =>   $_POST["leaveEndDate"],
        ":leave_total_days"                     =>   $_POST["leaveTotalDays"],
        ":leave_type"                           =>   $_POST["leaveType"],
        ":leave_in_out_time"                    =>   $_POST["leaveInOutTime"],
        ":leave_duration"                       =>   $_POST["leaveDuration"],
        ":leave_reason"                         =>   $_POST["leaveReason"],
        ":leave_current_status_id"              =>   $_POST["leaveCurrentStatusId"],
        ":leave_current_status_set_by"          =>   $_COOKIE["userID"],
        ":leave_current_status_set_dateTime"    =>   $currentDate,
        ":leave_detail_id"                      =>   $_POST["leaveDetailId"]

    );

    echo $_POST["empId"] ."\n";
    echo $_POST["leaveStartDate"].       "\n";
    echo $_POST["leaveEndDate"].         "\n";
    echo $_POST["leaveTotalDays"].       "\n";
    echo $_POST["leaveType"].            "\n";
    echo $_POST["leaveInOutTime"].       "\n";
    echo $_POST["leaveDuration"].        "\n";
    echo $_POST["leaveReason"].          "\n";
    echo $_POST["leaveCurrentStatusId"]. "\n";
    echo $_COOKIE["userID"].             "\n";
    echo $currentDate.                   "\n";
    echo $_POST["leaveDetailId"].        "\n";

    $sqlProjectQueueUpdate =
        "Update `ttl_employee_switch`.`tbl_emp_leave_details`
          SET
           `leave_start_date`                       = `:leave_start_date`,
           `leave_end_date`                         = `:leave_end_date`,
           `leave_total_days`                       = `:leave_total_days`,
           `leave_type`                             = `:leave_type`,
           `leave_in_out_time`                      = `:leave_in_out_time`,
           `leave_duration`                         = `:leave_duration`,
           `leave_reason`                           = `:leave_reason`,
           `leave_current_status_id`                = `:leave_current_status_id`,
           `leave_current_status_set_by`            = `:leave_current_status_set_by`,
           `leave_current_status_set_dateTime`      = `:leave_current_status_set_dateTime`
           WHERE `leave_detail_id`                 = `:leave_detail_id`";

    echo $sqlProjectQueueUpdate;
    $statement = $connPDO->prepare($sqlProjectQueueUpdate);
    $output = $statement->execute($binds);

    //$output = CustomPDO($connPDO, $sqlProjectQueueUpdate, $binds);
    echo $output;
}

Here is the javascript of calling above function:

if(leaveInOutTime == null || leaveInOutTime == undefined ||leaveInOutTime=="")
    {
        leaveInOutTime = "00:00:00";
    }

    var data = {};
    var actionName= "";

    data =   {
        //"action"                :   actionName,
        "empId"                   :   empID,
        "leaveStartDate"          :   leaveStartDate,
        "leaveEndDate"            :   leaveEndDate,
        "leaveTotalDays"          :   leaveTotalDays,
        "leaveDuration"           :   leaveDuration,
        "leaveType"               :   leaveType,
        "leaveInOutTime"          :   leaveInOutTime,
        "leaveReason"             :   leaveReason
    };

    //alert($("#selectedLeaveFormId").attr("leave-id"));

    if(isInsert ==false){
        data["leaveCurrentStatusId"] = 1;
        data["leaveDetailId"] = $("#selectedLeaveFormId").attr("leave-id");
        data["action"] = "UpdateLeaveDetailRequestStatus";
    }
    else
    {
        data["action"] = "GenerateLeaveDetailRequest";
    }



    console.log(data);



    console.log(empID);
    console.log(leaveStartDate);
    console.log(leaveEndDate);
    console.log(leaveTotalDays);
    console.log(leaveDuration);
    console.log(leaveType);
    console.log(leaveInOutTime);
    console.log(leaveReason);


    $.ajax({
        url  :  baseURL + "EmpLeave.php",
        type : "POST",
        data : data,
        success : function(data, textStatus, XMLHttpRequest) {
            console.log(data);

            if(data == 1)
            {
                ClearForm();
                if(isInsert){
                console.log("Successful entry" + data);
                }else{
                    alert("Leave Reocrd Updated.")
                }

            }
            else if(data == 2)
            {

                alert("Failed to generate leave request.");

            }
            else
            {
                console.log(data);
                alert("An error occurred, please Contact Administrator. " + data);

            }
        },
        error: function(jqXHR, textStatus, errorThrown) {
            alert("An error occured when adding Project Question. Please Contact Administrator."+ errorThrown);
        }
    });

`$_COOKIE["userID"]` - Are you storing the actual user id in a cookie? I really hope that you have some other way of verifying that the user id they have in the cookie is theirs, or anyone would be able to hijack any user by simply changing the id in that cookie. — M. Eriksson, Jul 01 '19 at 05:10
Activate the [PDO error handling](https://www.php.net/manual/en/pdo.error-handling.php) then you will also see possible PDO errors! — CodyKL, Jul 01 '19 at 05:11
[An idea for your CustomPDO function](https://phpdelusions.net/pdo/pdo_wrapper#function). — Your Common Sense, Jul 01 '19 at 07:46

score 1 · Accepted Answer · answered Jul 01 '19 at 05:08

Please get rid of all those backticks:

$sqlProjectQueueUpdate =
    "UPDATE ttl_employee_switch.tbl_emp_leave_details
      SET
       leave_start_date                       = :leave_start_date,
       leave_end_date                         = :leave_end_date,
       leave_total_days                       = :leave_total_days,
       leave_type                             = :leave_type,
       leave_in_out_time                      = :leave_in_out_time,
       leave_duration                         = :leave_duration,
       leave_reason                           = :leave_reason,
       leave_current_status_id                = :leave_current_status_id,
       leave_current_status_set_by            = :leave_current_status_set_by,
       leave_current_status_set_dateTime      = :leave_current_status_set_dateTime
       WHERE leave_detail_id                 = :leave_detail_id";

The backticks don't belong on the RHS of the column assignments, which should just be string (or other type of) literal. Anyway, you don't need backticks here since your column and table names are not reserved MySQL keywords.

@MuhammadFaizanKhan That's a good question, but I have seen this behavior before, which is maybe why I didn't bother commenting on that. — Tim Biegeleisen, Jul 01 '19 at 05:22

PDO Update query neither successful nor showing any error

1 Answers1