How to reformat dates in m/d/y with in PHP array from jqgrid post data?

Question

I have a jqgrid that sends update post data to my php for processing to a database. right now i have a problem with converting three of those columns into the desired yyyy-mm-dd format for injecting into a mysql database. How do i convert data in this array from m/d/Y to mysql yyyy-mm-dd?Where the heck do I convert correctly so the data is processed correctly and sent to database? Please I really need help any suggestions?

enter image description here

jqgrid Colmodel code:

{name:'lastvisit', index:'lastvisit', width:70, align:'right',formatter: 'date',srcformat:'yyyy-mm-dd',newformat: 'm/d/yy',editable:true, edittype: 'text',mtype:'POST' ,       editoptions:{size:10, dataInit:function(elem){$(elem).datepicker({dateFormat:'m/d/yy'});}}} ,


  {name:'cdate', index:'cdate', width:70, align:'right',formatter: 'date',srcformat:'yyyy-mm-dd',newformat: 'm/d/yy', edittype: 'text',editable:true ,mtype:'POST' ,editoptions:{size:10, dataInit:function(elem){$(elem).datepicker({dateFormat:'m/d/yy'});}}} ,

  {name:'ddate', index:'ddate', width:70, align:'right',formatter: 'date',srcformat:'yyyy-mm-dd',newformat: 'm/d/yy',date:'true',editable:true, edittype: 'text',editoptions:{size:10, dataInit:function(elem){$(elem).datepicker({dateFormat:'m/d/yy'});}}} ,

here is my PHP code:

/* columns array format:  $_POST['VARIABLE'] => 'DB column name' */
$crudColumns =  array(
    'id'=>'id'
    ,'name'=>'name'
    ,'id_continent'=>'id_continent'
,'lastvisit'=>'lastvisit'
    ,'cdate'=>'cdate'
    ,'ddate'=>'ddate'

);

Then they are cleaned and readied for processing:

/*----====|| GET and CLEAN THE POST VARIABLES ||====----*/
foreach ($postConfig as $key => $value){ 
    if(isset($_REQUEST[$value])){
        $postConfig[$key] = fnCleanInputVar($_REQUEST[$value]); 
    }
}
foreach ($crudColumns as $key => $value){ 
    if(isset($_REQUEST[$key])){
        $crudColumnValues[$key] = '"'.fnCleanInputVar($_REQUEST[$key]).'"';
    }
}

databse connect then sent to database for update row:

case $crudConfig['update']:
        /* ----====|| ACTION = UPDATE ||====----*/
        if($DEBUGMODE == 1){$firephp->info('UPDATE','action');}
        $sql = 'update '.$crudTableName.' set ';
        /* create all of the update statements */
        foreach($crudColumns as $key => $value){ $updateArray[$key] = $value.'='.$crudColumnValues[$key]; };
        $sql .= implode(',',$updateArray);
        /* add any additonal update statements here */
        $sql .= ' where id = '.$crudColumnValues['id'];
        if($DEBUGMODE == 1){$firephp->info($sql,'query');}
        mysql_query( $sql ) 
        or die($firephp->error('Couldn t execute query.'.mysql_error()));
        break;

designosis · Accepted Answer · 2013-05-20T05:39:00.933

1

I may not have understood the question, but if you're asking how to convert from one date format to another...

$bad_date = '5/16/2013';
$good_date = date('Y-m-d', strtotime($bad_date)); // returns 2013-05-16

... and you could change your foreach ($crudColumns) code like this ...

foreach ($crudColumns as $key => $value){ 
    if(isset($_REQUEST[$key])){
        if ($key == 'lastvisit' || $key == 'cdate' || $key == 'ddate') {
            $crudColumnValues[$key] = '"'.date('Y-m-d', strtotime($_REQUEST[$key])).'"';
        } else {
            $crudColumnValues[$key] = '"'.fnCleanInputVar($_REQUEST[$key]).'"';
        }
    }
}

edited May 20 '13 at 05:39

answered May 20 '13 at 04:41

designosis

5,182
1
38
57

I thank you but the problem is how do i integrate this into this php system so that the lastvisit, cdate, and ddate are converted into the good_date and then posted into my database row in correct format? Where would i introduce it? Total noob and any guidance is a big help. – NewHistoricForm May 20 '13 at 04:44
I see ... updating the answer in a moment – designosis May 20 '13 at 04:45
i've rearranged my answer above. any success? – designosis May 20 '13 at 04:55
no. Returns this: query: update demo set id="522",name="ooisii84858585959595",id_continent=" Ramon",lastvisit=1969-12-31,cdate=1969-12-31,ddate=1969-12-31 where id = "522" , it appears that their is a problem because the dates are not the dates I input and their are no quotes around them. – NewHistoricForm May 20 '13 at 05:04
Well the dates come out wrong but they post fine. for example: I posted this:cdate 8/17/2013 ddate 12/19/2013 lastvisit:5/12/2013 and comes out like lastvisit="1969-12-31",cdate="1969-12-31",ddate="1969-12-31" – NewHistoricForm May 20 '13 at 05:26
can you post the mysql `update` query result from BEFORE you added my code? I'm guessing the date format may be strange and therefore not interpreted correctly. – designosis May 20 '13 at 05:30
I Posted:lastvisit 12/31/2012cdate12/31/1969 ddate12/31/1969 it posted update demo set id="520",name="New",id_continent=" Ramon",lastvisit="12/31/2012",cdate="12/31/1969",ddate="12/31/1969" where id = "520" – NewHistoricForm May 20 '13 at 05:36
Try the updated code ... the only change is the `strtotime` parameter now uses `$_REQUEST[$key]` instead of `$value` – designosis May 20 '13 at 05:40

How to reformat dates in m/d/y with in PHP array from jqgrid post data?

1 Answers1