ErnieBob ErnieBob - 7 months ago 13
SQL Question

Datetime / Timestamp to be handled in PHP-MySQL

Hi I would like to know which one is the best option between datetime and timestamp to be handled in php-mysql. The things I'll need to do will basically be: order by date both ways, and select all the rows with a certain year. Is it even possible? Can I select all the rows with a certain year or month in php-mysql? If so, can you type down the SQL query that would be like

"SELECT * FROM my_table WHERE my_date.year = 2014"
for example? Also please tell me if datetime is better than timestamp and why, thank you all in advance.

Answer

Here is an OOP way of doing what you want, references are below.

namespace Connection;
class Database
{
    protected $Con;
    public function __construct()
    {
        $this->Con = new PDO("MySQL:host=X;dbname=X", "user", "pass");
    }
    public function GetInstance()
    {
        return $this->Con;
    }
}

// end class

$sql = "SELECT * FROM Table WHERE DATE_FORMAT(Column, %Y) = 2014";
use Connection;
$smpt = ((new Database)->GetInstance()
                      ->Prepare($sql))
                      ->execute();
foreach ($smpt->fetchAll() as $row):
    echo $row['Column'];
endforeach;

Look at this StackOverflow answer for the difference between DateTime and TimeStamp.

References:

Date Format