CODEWITHSUNDEEP
mysqlsqldatetimetimestamp
Sideshow
Sideshow

Reputation: 1351

Get month name from php timestamp

I have a table which stores a php timestamp as a interger. I need to access this data by date and year. I can grab the year and match it with a column row that exists within the table but need to obtain the month name from the timestamp.

Code so far

$query="SELECT * FROM archive_agent_booking
    WHERE agent_id='$account_no'
    AND (MONTHNAME(comp_date)='$month' AND year='$year' AND details='')
    ORDER BY order_time";

unfortunately this is returning nothing.

comp_date is the column that contains the timestamps

Upvotes: 2

Views: 1556

Answers (2)

John Woo
John Woo

Reputation: 263683

you need to convert it to date first using FROM_UNIXTIME

MONTHNAME(FROM_UNIXTIME(comp_date)) = '$month'

As a sidenote, the query is vulnerable with SQL Injection if the value(s) came from the outside. Please take a look at the article below to learn how to prevent from it. By using PreparedStatements you can get rid of using single quotes around values.

Upvotes: 2

Michael Berkowski
Michael Berkowski

Reputation: 270599

You just need to wrap it in FROM_UNIXTIME() to convert it first to a MySQL DATETIME which is needed by MONTHNAME()

AND MONTHNAME(FROM_UNIXTIME(comp_date)) = '$month'

If you are early enough in this project to change direction a little, I would recommend storing the value as a proper DATETIME type instead of the int value Unix timestamp. MySQL is better able to handle date types natively without having to convert in and out with UNIX_TIMESTMAP(), FROM_UNIXTIME() all the time.

We assume your value $month is properly escaped if it originates from user input. Otherwise, consider switching to an API which supports prepared statements like MySQLi or PDO.

Upvotes: 2

Related Questions