Phil Phil - 2 months ago 8
MySQL Question

Having issues inserting jQuery datepicker into MySQL

I have a survey form with a jQuery date picker but I am having issues getting the date inserted into the MySQL database. The result keeps coming up all zeros.

My PHP and MySQL skills aren't great so a little help would be greatly appreciated.

The date format in the database is datetime.

My code is below.

<input type="text" name="surveydate" id="datepicker">

<?php
session_start(); // Session starts here.

$servername = "";
$username = "";
$password = "";
$dbname = "";

$_SESSION['firstname'] = $_POST['firstname'];
$_SESSION['lastname'] = $_POST['lastname'];
$_SESSION['gender'] = $_POST['gender'];
$_SESSION['postcode'] = $_POST['postcode'];
$_SESSION['surveydate'] = $_POST['surveydate'];

$firstname = $_SESSION['firstname'] ;
$lastname = $_SESSION['lastname'];
$gender = $_SESSION['gender'];
$postcode = $_SESSION['postcode'];
$surveydate= $_SESSION['surveydate'];

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}

$sql = "INSERT INTO results (firstname,lastname,gender,postcode,surveydate)

VALUES ('$firstname','$lastname','$gender','$postcode','$surveydate')";

$today = date("d M Y");

if ($conn->query($sql) === TRUE) {

} else {
echo "Error: " . $sql . "<br>" . $conn->error;
}

$conn->close();

?>

Answer

Since if the datatype of the surveydate is date you have to enter using 'Y-m-d' format into the DB other wise it will entering as '0000-00-00' only.

Ensure any of the two methods and this will find your code work better.

Method 1: Changing of the datatype in the jquery datpicker itself.

$(document).ready(function() {
$("#datepicker").datepicker({ dateFormat: 'yy-mm-dd' });
});

Method 2: Changing the format of the date while entering into the DB

Replace:

$_SESSION['surveydate'] = $_POST['surveydate'];

With:

$_SESSION['surveydate'] = date('Y-m-d',strtotime($_POST['surveydate']));

Adopt any one method. if you use method1 you no need to follow method-2 or vice versa as you need to do.

Comments