How to insert values from JQuery datepicker to MyS

问题:

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! :)

回答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.

回答2:

<script>
$(function() {
    $( "#datepicker" ).datepicker({
        showOn: "button",
        buttonImage: "images/icons/calendar_24.png",
        buttonImageOnly: true,
        dateFormat: 'Y-m-d'       // No need to add onselect , value selected will automatically be added to input field #datepicker       
    });

});
</script>

Your code is fine

回答3:

$(document).ready(function() {

    var year=new Date().getFullYear();
    var yearTo=year-18;
    var yearFrom=year-100;

    //display the years from hundred years in the past till 18 years in the past

    $( "#datePickerDOB" ).live("click",function(){      

        $( "#datePickerDOB" ).datepicker({
            changeMonth: true,
            changeYear: true,
            yearRange: yearFrom+":"+yearTo,
            dateFormat: "dd-mm-yy"
        });
    });
});

回答4:

Use the following to format the datepicker:

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

回答5:

Your controller should look like this:

{
    $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();
}

I think this suits you better