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?
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");
}
Try putting the execute statement into a variable and at the end echo it so you can see if your query fails or not.
And then try putting the output of your query into the phpmyadmin to see if you have errors.