Sunil Sharma Sunil Sharma - 6 months ago 10
SQL Question

UPDATE IS NOT WORKING

This is my index page ,data fetching is working but update table is not working, can you please help me to slove this problem.

IndexPage

<script>
jQuery("#rowed2").jqGrid({
url:'server.php',
datatype: "json",
colModel:[
{name:'WeekNo',index:'WeekNo', width:30 , editable:false,editrules : { required: false}},
{name:'WeekBeginning',index:'WeekBeginning', width:100 ,editable:true,editrules : { required: true}},
{name:'SITE',index:'SITE', width:40 , editable:true,editrules : { required: true}},
{name:'Name',index:'Name', width:150,align:"center" , editable:true,editrules : { required: true}},
{name:'WFH',index:'WFH', width:40, classes: 'cvteste', editable:true,editrules : { required: true}},
{name:'Login',index:'Login', width:80, editable:true,editrules : { required: false}},
{name:'Manager_Login',index:'Manager_Login', width:90 , editable:true,editrules : { required: true}},
{name:'Lead',index:'Lead', width:70, editable:true,editrules : { required: true}},
{name:'Cost_center',index:'Cost_center', width:70, editable:true,editrules : { required: true}},
{name:'Business_Title',index:'Business_Title', width:100, editable:true,editrules : { required: true}},
{name:'Function',index:'Function', width:70, editable:true,editrules : { required: true}},
{name:'Workgroup',index:'Workgroup', width:70, editable:true,editrules : { required: true}},
{name:'Login_time',index:'Login_time', width:70, editable:true,editrules : { required: true}},
{name:'ROLE',index:'ROLE', width:70,editable:true,editrules : { required: true}},
{name:'Secondary_Skill',index:'Secondary_Skill', width:90, editable:true,editrules : { required: true}},
{name:'Weekoff',index:'Weekoff', width:60, editable:true,editrules : { required: true}}
],

rowNum:100,
rowList:[100,200,300],
pager: '#prowed2',
//sortname: ('WeekNo','Name') ,
viewrecords: true,
height:'500px',
sortorder: "asc",
gridComplete: function(){
var ids = jQuery("#rowed2").jqGrid('getDataIDs');
for(var i=0;i<ids.length;i++){
var cl = ids[i];
be = "<input style='height:22px;width:40px;' type='button' value='Edit' onclick=\"jQuery('#rowed2').jqGrid('editRow','"+cl+"');\" />";
se = "<input style='height:22px;width:40px;' type='button' value='Save' onclick=\"jQuery('#rowed2').jqGrid('saveRow','"+cl+"');\" />";
ce = "<input style='height:22px;width:50px;' type='button' value='Cancel' onclick=\"jQuery('#rowed2').jqGrid('restoreRow','"+cl+"');\" />";
jQuery("#rowed2").jqGrid('setRowData',ids[i],{act:be+se+ce});
}
},
editurl: "update.php"
});
jQuery("#rowed2").jqGrid('navGrid',"#prowed2{edit:true,add:true,del:true});

var timeoutHnd;
var flAuto = true;
function doSearch(ev){
if(!flAuto)return;
if(timeoutHnd) clearTimeout(timeoutHnd);
timeoutHnd = setTimeout(gridReload,500);
}
function gridReload(){
var nm_mask = jQuery("#login").val();
var cd_mask = jQuery("#man").val();
var func = jQuery("#func").val();
jQuery("#rowed2").jqGrid('setGridParam',{url:"server.php? nm_mask="+nm_mask+"&cd_mask="+cd_mask+"&func="+func,page:1}).trigger("reloadGrid");

}
</script>


Here if i use WeekNo at WHERE condition it is showing error undefined variable so im not using WeekNo then it is not updating database not even generating log file.

UpdatePage

<?php


$con = mysqli_connect("localhost", "user", "password", "DB");


//This contains the data that will update the db
//$WeekNo = $_POST['WeekNo'];
$WeekBeginning = $_POST['WeekBeginning'];
$SITE = $_POST['SITE'];
$Name = $_POST['Name'];
$WFH = $_POST['WFH'];
//$Login = $_POST['Login'];
$Manager_Login = $_POST['Manager_Login'];
$Lead = $_POST['Lead'];
$Cost_center = $_POST['Cost_center'];
$Business_Title = $_POST['Business_Title'];
$Function = $_POST['Function'];
$Workgroup = $_POST['Workgroup'];
$Login_time = $_POST['Login_time'];
$ROLE = $_POST['ROLE'];
$Secondary_Skill = $_POST['Secondary_Skill'];
$Weekoff = $_POST['Weekoff'];

$sql = "UPDATE EmpMasterTB SET WeekBeginning='$WeekBeginning', SITE='$SITE', Name ='$Name', WFH='$WFH', Manager_Login='$Manager_Login', Lead='$Lead', Cost_center='$Cost_center', Business_Title='$Business_Title', Function='$Function', Workgroup='$Workgroup', Login_time='$Login_time', ROLE='$ROLE', Secondary_Skill='$Secondary_Skill', Weekoff='$Weekoff' WHERE ROLE = $_POST[ROLE] AND Login = $_POST[Login]";

mysqli_query($con,$sql) or die(mysqli_error($con));
mysqli_close($con);
?>

Answer

Your WHERE condition for your UPDATE query should be:

WHERE ROLE = '$_POST[ROLE]' AND login = '$_POST[Login]'

When binding variables, especially string type, you should be using ticks (') to incorporate them in your query.

Also, use *_real_escape_string() before you bind them to your query.

$role = mysqli_real_escape_string($con, $_POST["ROLE"]);

Do the rest for the POST submitted data.