How to INSERT date in PHP & Mysqli

20.3k views Asked by At

i have variable $est_date = "25-02-2015" this is DD-MM-YYYY format so now i need to insert this value in mysql table, the column type is DATE TIME, so i tried like this but the date are not storing in the coloumn est_date, it just showing me 00-00-0000

$mysqli = new mysqli('localhost','root','','smart_marine');
$insertdate = date("d-m-Y", strtotime($est_date));
$insert = $mysqli->prepare("INSERT into repair ( 
                                            cont_details_id,
                                            depot_details_id, 
                                            cont_condition,
                                            est_cost,
                                            currency,
                                            est_date, 
                                            location
                                            )
                                VALUES (?,?,?,?,?,?,?)");

        $phpArray = json_decode($serialize_data, true);
        foreach ($phpArray as $key => $value)
        { 
            if($value['rate']!=''&& $value['drop']!='')
            {       
            $insert->bind_param('sssssss',
                                 $value['id'], 
                                 $depot_details_id, 
                                 $value['drop'], 
                                 $value['rate'],
                                 $currency,
                                 $insertdate, 
                                 $location);                                            
            $insert->execute();
            }
        }

please some one help me.

i tried to store like this too

$insert->bind_param('sssssss',
                                     $value['id'], 
                                     $depot_details_id, 
                                     $value['drop'], 
                                     $value['rate'],
                                     $currency,
                                    STR_TO_DATE($est_date , '%d-%m-%Y'), 
                                     $location);        

but this return error "undefined function STR_TO_DATE"
3

There are 3 answers

0
Death-is-the-real-truth On BEST ANSWER

You need to change this line:-

$insertdate = date("d-m-Y", strtotime($est_date));

to

$insertdate = date("Y-m-d", strtotime($est_date));

Note:- DATETIME data-type format is Y-m-d H:i:s. thanks.

0
OllyBarca On

A datetime format is YYYY-MM-DD. Reset the date like this:

$date = date("Y-m-d", strtotime($est_date));

Then pass into your query.

0
Nugroho Setiawan On

The general format of DATE in mysqli is

 'date_add' => date("Y:m:d H:i:s"),

and if you have a variable just put after your value of variable