Umesh Palav Umesh Palav - 4 months ago 68
PHP Question

jquery jtable update and create date issue

I have got problems with date field. When I create a new record or update an existing one, the date field is saved as

000-00-00.


Lecture_date: {
title: 'lecture date',
width: '11%',
type: 'date'
},

$result = mysql_query("INSERT INTO lecture_schedule(lecture_no, lecture_date) VALUES('" . $_POST["lecture_no"] . "'," . $_POST["lecture_date"] . ");");


With the datepicker when I select any day ,It is stored in table with value
(0000-00-00).


The DB field is date type.

I download the PHP sample code, create a DB with example sql and change the RecordDate field editable and I have the same problem.

DB value: 0000-00-00
JTable shows: 1899-11-30


What should I do??
Please help
This is the code
jquery code:-

$(document).ready(function () {


var assignmentid= "<?php echo $assignmentid; ?>";
//Prepare jTable
$('#PeopleTableContainer').jtable({
title: 'Table of people',
actions: {
listAction: 'teacherfill.php?action=list&aid="<?php echo $assignmentid; ?>"',
createAction: 'teacherfill.php?action=create&aid="<?php echo $assignmentid; ?>"',
updateAction: 'teacherfill.php?action=update&aid="<?php echo $assignmentid; ?>"',
deleteAction: 'teacherfill.php?action=delete&aid="<?php echo $assignmentid; ?>"'
},
fields: {
lecture_id: {
key: true,
create: false,
edit: false,
list: false
},
lecture_no: {
title: 'Lecture No',
width: '40%'
},
lecture_date: {
title: 'Lecture Date',
width: '20%',
type:'date'
}

}
});

//Load person list from server
$('#PeopleTableContainer').jtable('load');

});


php code:-

if($_GET["action"] == "update" && $_GET["aid"])
{

$assignmentid=$_GET['aid'];
$updatedlecno=$_POST["lecture_no"];
//$updatedlecdate=date("Y-m-d",strtotime($_POST['lecture_date']));
$updatedlecdate=$_POST['lecture_date'];
$lecid=$_POST["lecture_id"];
$q="UPDATE lecture_schedule SET lecture_no=$updatedlecno,lecture_date=$updatedlecdate WHERE (lecture_id=$lecid)";
$result = mysqli_query($conn,$q);
if($result)
{
$er="No error";
}
else{
$er1=mysqli_error($conn);
$er="error";

}
//Return result to jTable
$jTableResult = array();
$jTableResult['Result'] = "OK";
//$jTableResult['status'] = $er;
//$jTableResult['date'] = $updatedlecdate;




print json_encode($jTableResult);
}

Answer

First, don't use the mysql_* functions. These functions are deprecated. Use mysqli_* or PDO instead.

Second, don't put raw $_POST data into your SQL statements. Bobby Tables will explain. Use parameterized queries instead.

Third, check if $_POST['lecture_date'] actually contains a value. In your example data, it's Lecture_date - case matters.

Fourth, dates in SQL queries are still strings. You need to wrap the value in single quotes for SQL to treat it correctly. Otherwise it's going to see 2016-07-29 as 2016 - 7 - 29 = 1980. You should get an error with this when you address my third point.

Comments