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! :)

SQL - Procedural Programming - TRY ... CATCH

The TRY...CATCH construnct was introduced with SQL Server 2005 for error handling using T-SQL. Statements to be tested for an error are enclosed in a BEGIN TRY ... END TRY block.
A CATCH block immediately follows the TRY block, and error-handling logic is stored here. The following examples shows the basic syntax:

BEGIN TRY
-- code that may produce errors
END TRY
BEGIN CATCH
-- error handling logic
END CATCH

SQL Server evaluates each statement in TRY block sequentially. If a runtime error is encountered, control immediately jumps to the CATCH block, and error information can be retrieved, logged, and displayed to the user.

SQL - Procedural Programming - Variable

A variable is a container for a single data value of a particular type. In Transact-SQL (T-SQL), all variables are preceded by an @ symbol.

Local variables are used in T-SQL scripts for a variety of purposes including:

• Storing values to be tested by control-of-flow statement
• Acting as counter in a loop
• Storing the results of an expression
• Retrieving field values for a single record using a SELECT statement

Variable are also used to pass values into parameters for stored procedures and user-defined functions. When declaring a variable, you must specify its name, datatype and sometimes the length and precision of datatype. The DECLARE statement can be used to declare multiple variables by separating them with commas.

Example

DECLARE @var1 int, @var2 varchar(255);

The preferred way to set the contents of a variable is use the SET statement. It is also possible to use the SELECT statement to set the value of one or more variables.

SET @var1=5;

SELECT @var2='A varchar string';