How to insert values from JQuery datepicker to MySQL date datatype using Codeigniter?

Question

i can't insert the values from my JQuery Datepicker to my MySQL database with Date datatype. how will i convert string to date datatype? im using Codeigniter with MVC. here's my code:

javascript

<script>
$(function() {
    $( "#datepicker" ).datepicker({
        showOn: "button",
        buttonImage: "images/icons/calendar_24.png",
        buttonImageOnly: true,
        onSelect: function(dateText, inst){
            $("input[name='dob']").val(dateText);
        }
    });

});
</script>

controller

function create()
{
    $data = array(
        'FirstName' => $this->input->post('fname'),
        'DateofBirth' => $this->input->post('dob')
    );

    $this->site_model->add_record($data);
    $this->index();
}

view

<?php echo form_open('site/create');?>
<p>
    <label for="fname">First Name:</label>
    <input type="text" name="fname" />
</p>

<p>
    <input type="text" name='dob' id="datepicker" />
</p>

thanks guys! i read some solution in google and i did this:

controller:

function create()
{
    $date = $this->input->post('dob');

    $data = array(
        'FirstName' => $this->input->post('fname'),
        'DateofBirth' => date('Y-m-d', strtotime($date))
    );

    $this->site_model->add_record($data);
    $this->index();
}

and this solved my problem! :)

Answer 1

I assume your MySQL table's dob column is of DATE type. Date type takes value in the format of yyyy-mm-dd e.g. 2012-09-21

So to answer your question - you need to format the date from the datepicker to the above valid format. e.g. if your datepicker date is in the dd-mm-yy format you need to convert it to mysql date format using php's date() function in your controller .... (there are other methods to handle dates like datetime class too)

function create()
{
    $data = array(
        'FirstName' => $this->input->post('fname'),
        'DateofBirth' => date('Y-m-d', strtotime(str_replace('-', '/', $this->input->post('dob')))); 
    );

    $this->site_model->add_record($data);
    $this->index();
}

NOTE when using strtotime()

Dates in the m/d/y or d-m-y formats are disambiguated by looking at the separator between the various components: if the separator is a slash (/), then the American m/d/y is assumed; whereas if the separator is a dash (-) or a dot (.), then the European d-m-y format is assumed.