SQL insert query not working with JQuery UI datepicker

Question

The html input:

<input name="week_ending" type="text" id="week_ending" value="" />

The jQuery:

<script>
    $(function() {
        $( "#week_ending" ).datepicker();
    });
</script>

The PHP code:

$week_ending = db_quote($_POST['week_ending']);

function db_quote($value) {
    $connection = db_connect();
    return "'" . mysqli_real_escape_string($connection,$value) . "'";
}

The SQL:

$result = db_query("INSERT INTO `timesheets` (clientid, candid, weekending, department, orderno, basicpay, basiccharge, otpay, otcharge, ot2pay, ot2charge, status, hue, huc) VALUES ($client, $cand, $week_ending, $department, $order_no, $basic_pay, $basic_charge, $ot_pay, $ot_charge, $ot2_pay, $ot2_charge, $status, $hue, $huc)");
    if($result){
        print 'Success! ID of last inserted record is';
    } 
    else {
        die('Error : ' . db_error());
    }

Every row I insert to timesheets has the date as 0000-00-00. The data type is set to date in the timesheets table.

When inspecting the element after a date has been chosen the HTML is:

<input class="hasDatepicker" name="week_ending" id="week_ending" value="" type="text">

I'm guessing that explains the 0000-00-00 going into the database. I've tried to add .formatDate but that doesn't seem to make a difference and tried changing to <input type="date"> but still nothing is appearing in the value property

Does it work when you specify mysql compatible date format i.e. .datepicker({dateFormat: "yy-mm-dd"}) — Salman Arshad
– Salman Arshad, Commented Jul 24, 2015 at 13:44
@SalmanA That works, $( "#week_ending" ).datepicker().formatDate( "yy-mm-dd"); didn't?? Thanks — Ian Butler
– Ian Butler, Commented Jul 24, 2015 at 13:52

Salman Arshad · Accepted Answer · 2015-07-24 14:07:07Z

1

You can use the PHP functions: date() and strtotime to parse the format into a timestamp and then into correct date format.

Be aware that users can change the input field with wrong data.

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

edited Jul 24, 2015 at 14:07

Salman Arshad

274k85 gold badges450 silver badges540 bronze badges

answered Jul 24, 2015 at 13:54

FlRi

261 bronze badge

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL insert query not working with JQuery UI datepicker

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related