更新语句不起作用（常规语句以及准备语句）

Question

I am able to insert and select from mysql database without any problem but not able to update records.

Initially I was using mysqli_query without any result or error. Later I tried prepared statement with no result.

Sharing my code down here:

 define('DB_SERVER', 'localhost');
 define('DB_USERNAME', 'xxxx');
 define('DB_PASSWORD', 'xxxx');
 define('DB_DATABASE', 'xxxx');
 $db = mysqli_connect(DB_SERVER,DB_USERNAME,DB_PASSWORD,DB_DATABASE);

 //the function
 function SaveLineItem() {
     $sql = "update lineitems set deliverycost=? ";

     $tdetail = "";
     if($_POST['tdetail']){
        $sql .= ",tracking_details=?" ;
        $tdetail = $_POST['tdetail'];
     }

     $sql .= " where id=?";

     $dc = $_POST['dc'];
     $lineItemId = $_POST['lineItemId'];
     echo $sql;

     $stmt = mysqli_prepare($db, $sql);
     echo "<br>after prepare statement";

    if($tdetail<>""){
        mysqli_stmt_bind_param($stmt,"isi",$dc1,$tdetail1,$lineItemId1);
     }else {
        mysqli_stmt_bind_param($stmt,"ii",$dc1,$lineItemId1);
    }

    echo "<br>after binding";

    $dc1 = $dc;
    if($tdetail<>""){
        $tdetail1 = $tdetail;   
        echo "<br>detail param bound";
    }

    $lineItemId1 = $lineItemId;
    mysqli_stmt_execute($stmt);

    echo "<br>statement executed";
     //header("location:../welcome.php");
}

The output is:

update lineitems set deliverycost=? ,tracking_details=? where id=?
after prepare statement
after binding
detail param bound
statement executed

But on checking the table there is no update. Trying since yesterday, what am I missing?

Update: This code was initially being developed in windows but later I decided to continue work in Ubuntu 16.04. It seems in ubuntu even insert is not working. Could it be ubuntu security related issue?

Answer 1

Try this please. You have $dc1 undefined at the time it is used in mysqli_stmt_bind_param. For you $dc1 holds the id of the updated record. If this is null that is why you won't see any change in the database. The query runs but it does not affect any rows.

 define('DB_SERVER', 'localhost');
 define('DB_USERNAME', 'xxxx');
 define('DB_PASSWORD', 'xxxx');
 define('DB_DATABASE', 'xxxx');
 $db = mysqli_connect(DB_SERVER,DB_USERNAME,DB_PASSWORD,DB_DATABASE);

 //the function
 function SaveLineItem() {
     $sql = "update lineitems set deliverycost=? ";

     $tdetail = "";
     if($_POST['tdetail']){
        $sql .= ",tracking_details=?" ;
        $tdetail = $_POST['tdetail'];
     }

     $sql .= " where id=?";

     $dc = intval($_POST['dc']);
     $lineItemId = intval($_POST['lineItemId']);
     echo $sql;
     $stmt = mysqli_prepare($db, $sql);
     echo "<br>after prepare statement";
     // define $dc1 here since you use it below. If it is undefined could resolve to an error or a wrong query.
     $dc1 = $dc;    
    if($tdetail<>""){
        mysqli_stmt_bind_param($stmt,"isi",$dc1,$tdetail1,$lineItemId1);
     }else {
        mysqli_stmt_bind_param($stmt,"ii",$dc1,$lineItemId1);
    }

    echo "<br>after binding";


    if($tdetail<>""){
        $tdetail1 = $tdetail;   
        echo "<br>detail param bound";
    }

    $lineItemId1 = $lineItemId;
    mysqli_stmt_execute($stmt);

    echo "<br>statement executed";
    mysqli_stmt_close($stmt);
    echo "<br>statement closed";
     //header("location:../welcome.php");
}