PHP :How to convert array string of date to mysql date ( 27/1/2014 ) to ( 2014-01-27) before update in mysql

Question

i have this code that will get the date from looping input, make it array using map function.

var target_completion_date = $('input#target_completion_date').map(function() {
       return $(this).val();
       }).get();

The Output of this JavaScript is like this in string array:

27/1/2014,25/1/2014,29/1/2014

The question is, how can I code the update MySQL or convert (27/1/2014,25/1/2014,29/1/2014) to (2014-01-27,2014-01-25,2014-01-25) before it save and update to database:

$target_completion_date = $_POST['target_completion_date']; 



> for ($i = 0; $i <5;)  {

> $sql1 = "UPDATE AT_tna_assessment SET identify_needs = '$identify_needs[$i]', activity_required = '$part_c_activity[$i]',
> priority = '$part_c_priority[$i]', **target_completion_date = ?** , remarks = '$part_c_remark[$i]'
> WHERE id ='$part_c_id[$i]'";

> mysql_query($sql1) or die ("Cant Connect");           
$i++;       
}

I have use target_completion_date = DATE(STR_TO_DATE('$target_completion_date[$i]','%m-%d-%Y')); but not working

I GOT ALREADY MY ANSWER. THANKS ALL

I have solve this problems by this code. Thanks :)

  for ($i = 0; $i <5;) {

    $tb = explode("/", ($target_completion_date[$i]));   
    date_duration_from[$i] = $tb[2] . "-" . $tb[1] . "-" . $tb[0].",";


    $sql1 = "UPDATE AT_tna_assessment SET identify_needs = '$identify_needs[$i]',   activity_required = '$part_c_activity[$i]', priority = '$part_c_priority[$i]',  target_completion_date= '$date_duration_from[$i]' , remarks = '$part_c_remark[$i]' WHERE id
    ='$part_c_id[$i]'";


     mysql_query($sql1) or die ("Cant Connect");
     $i++;   

    }

i get it from simpleDatePicker script... i have group it in javascript and sent it to proses database. it all in string format — airi, Jan 27 '14 at 01:39
You are vulnerable to [SQL injection attacks](http://bobby-tables.com)... — Marc B, Jan 27 '14 at 01:40
@user3238836 I am not familiar with `simpleDatePicker`, but personally I'd be severely pissed off if a website tried to impose the nonsensical m/d/y format on me ;) — Niet the Dark Absol, Jan 27 '14 at 01:42
hehe.. you can just think like this an array of (27/1/2014,25/1/2014,29/1/2014) how to convert to this (2014-01-27,2014-01-25,2014-01-25) before sent to sql.. :) — airi, Jan 27 '14 at 01:45
you should also post what error you get when you say it is not working... — Buddha, Jan 27 '14 at 01:59
i got no error since this for update the database... only it will not store the data since wrong date format suppose 2013-03-12 but it sent 12/3/2013 — airi, Jan 27 '14 at 02:26
If you have a solution, please post it below and accept it to mark this question "solved". Don't put answers inside your question, that isn't how the Q&A format of Stack Overflow works, and don't put meta tags like "(SOLVED)" in the title of your question. Just post and accept an answer. — user229044, Jan 27 '14 at 03:42
owh.. sorry.. i cant answer my own question.. i will fix this.. thanks.. im new here.. — airi, Jan 27 '14 at 05:24
i just do not want people come and answer because i already got answer .. that why i put solved.. Dont't want to waste people time.. btw i have corrected.. sorry again :) — airi, Jan 27 '14 at 05:27

score 3 · Answer 1 · answered Jan 27 '14 at 01:41

3

With something like ...

<?PHP
$date_array = explode(",", "27/1/2014,25/1/2014,29/1/2014");
foreach($date_array as $date) {
    $timestamp = strtotime($date);
    $mysql_date[] = date("Y-m-d", $timestamp);
}
echo(implode(",", $mysql_date));

answered Jan 27 '14 at 01:41

craCH

103
9

inb4 mysql injection vulnerability: This code converts the user input to a timestamp, then to a strictly controlled string output. Any attempt at injection will just result in a `0` timestamp and is therefore safe. – Niet the Dark Absol Jan 27 '14 at 01:43
sorry for late...testing this code .. i used this code but only get 1970-01-01 <-- which means 0000-00-00.. not the data – airi Jan 27 '14 at 02:19
$date_array = explode(",", "$target_completion_date"); – airi Jan 27 '14 at 02:20
from your example i just got like this "1970-01-01,1970-01-01,1970-01-01" not 2014-01-27 etc etc – airi Jan 27 '14 at 02:56
@user3238836 correct, this example is incorrect because `strtotime` doesn't accept the date format of `dd/mm/YYYY`. It will accept `dd-mm-YYYY` though. You should use the answer given by CBroe inside your foreach like so: https://eval.in/95239. Or, use a solution like this: http://stackoverflow.com/a/2891949/2812842 – scrowler Jan 27 '14 at 04:19
I'm struggling to work out why so many people upvoted this answer when it doesn't even work: https://eval.in/96279 – scrowler Jan 30 '14 at 02:15

score 2 · Accepted Answer · answered Jan 27 '14 at 04:14

2

list($day, $month, $year) = explode('/', '27/1/2014');
echo sprintf('%4d-%02d-%02d', $year, $month, $day);

answered Jan 27 '14 at 04:14

CBroe

91,630
14
92
150

although i already got the answer.. but your solution simple and i like it. thanks.. gain more knowledge on this matter. – airi Jan 27 '14 at 05:19
`echo sprintf()` is an "antipattern". There is absolutely no reason that anyone should ever write `echo sprintf()` in any code for any reason -- it should be `printf()` without `echo` every time. – mickmackusa Apr 09 '22 at 07:55

score 0 · Answer 3 · answered Jan 27 '14 at 04:48

Try this code.

$date_array = explode(",", "27/1/2014,25/1/2014,29/1/2014");

echo 'Unformat dates: '.implode(",", $date_array).PHP_EOL;

foreach($date_array as $date) {
    list($day,$month,$year) = explode('/', $date);
    $date_fmt[] = date('Y-m-d', strtotime($year.'-'.$month.'-'.$day));
}

echo 'Format dates: '.implode(",", $date_fmt).PHP_EOL;

Output:

Unformat dates: 27/1/2014,25/1/2014,29/1/2014
Format dates: 2014-01-27,2014-01-25,2014-01-29

PHP :How to convert array string of date to mysql date ( 27/1/2014 ) to ( 2014-01-27) before update in mysql

3 Answers3