Format Date and Insert Into Database Table: PHP

Convert string type variable data to formatted date type variable and insert it into database table..

Connecting the PHP Script to database
db.php

1
2
3
4

< ?php
$conn = mysql_connect("localhost","username","password");
$db      = mysql_select_db("table_name",$conn);
?>

Our database name is technotip and table name is temp.
date-time.php

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

< ?php
       include_once('db.php');
 
// [created_at] => Sun Mar 23 06:39:16 +0000 2008
 
$raw = "Sun Mar 23 06:39:16 +0000 2008";
 
$xplod = explode(' ', $raw);
 
print_r($xplod);
 
$string = "$xplod[5]-$xplod[1]-$xplod[2] $xplod[3]";
 
echo "<br />$string";
 
$date = date("Y-m-d H:i:s", strtotime($string));
 
echo "<br />$date";
 
 
if(msql_query("INSERT INTO test VALUES('$date')"))
echo "Inserted successfully!";
else
echo "Failed .. please try again!";
 
?>

Twitter and other API’s return these kind of string Sun Mar 23 06:39:16 +0000 2008
which we store into a variable. using PHP standard function explode we separate the elements based on space.

Video Tutorial: Format Date and Insert Into Database Table: PHP

Now we use print_r() function to see the structure of the array:

1
2
3
4
5
6
7
8

Array ( 
  [0] => Sun 
  [1] => Mar 
  [2] => 23 
  [3] => 06:39:16 
  [4] => +0000 
  [5] => 2008 
)

Next, by using its index value we arrange the date according to the format that is suitable to be stored into mysql table.

Convert the string to time format using strtotime() php standard function.

Finally, using date() function match the date formats and store it inside another variable which is then used to pass the date to the database.

Here is the SQL Query:

1

INSERT INTO test VALUES('$date')