Convert dates in PHP and MySQL

Today I was working in project and I was confronted by one problem. As I live in Europe, the date's format are not the same in United States even MySQL. US use mm/dd/yyyy format, Europe use dd/mm/yyyy and MySQL use yyyy-mm-dd.


After I researched on PHP and MySQL official sites, I wrote algorithms that allowed me insert correctly data in SQL. 


PHP to MySQL:


SQL:


create schema test;


create tabela xpto(
id int not null auto_increment,
start_date date,
end_date date,
primary key(id)
)engine=innodb;




PHP:
<?php
$start_date = "10/10/2011";
$end_date = "12/10/2011";


$query0 = "select str_to_date('$start_date','%d/%m/%Y')";
$result0 = mysql_query($query0) or die(mysql_error());
$row0 = mysql_fetch_array($result0);


$query1 = "select str_to_date('$end_date','%d/%m/%Y')";
$result1 = mysql_query($query1) or die(mysql_error());
$row1 = mysql_fetch_array($result1);


$query2 = "insert into xpto (start_date, end_date) values ('".$row0[0]."','".$row1[0]."')";
$result2 = mysql_query($query2) or die(mysql_error());
?>


To check out the data inserted, type on mysql terminal:
select * from xpto;


MySQL to PHP:
It is enough select data stored and use two functions to show date format correctly.




PHP:
<?php
mysql_connect("localhost","user","pass") or die (mysql_error());
mysql_select_db("test");


$query0="select start_date from xpto where id=1";
$result0 = mysql_query($query0) or die (mysql_error());
$row0 = mysql_fetch_array($result0,MYSQL_NUM);



$query1="select end_date from xpto where id=1";
$result1 = mysql_query($query1) or die (mysql_error());
$row1 = mysql_fetch_array($result1,MYSQL_NUM);

$start_date=$row0[0];//"2011-10-10"
$end_date=$row1[0];//"2011-10-12"


$d0 = strtotime($start_date);
$d1 = date('d/m/Y',$d0);
echo $d1."<br />";


$d2 = strtotime($end_date);
$d3 = date('d/m/Y',$d2);
echo $d3."<br />";
?>


Happy Halloween! :)