2

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

Armatus
  • 2,206
  • 17
  • 28
Sideshow
  • 1,321
  • 6
  • 28
  • 50

2 Answers2

2

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.

Michael Berkowski
  • 267,341
  • 46
  • 444
  • 390
2

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.

Community
  • 1
  • 1
John Woo
  • 258,903
  • 69
  • 498
  • 492
  • Thanks - and thanks for the extra information. I use `mysqli_real_escape_string()` on the inputted variables - will this avoid any mysqli injection attempts ? – Sideshow Jan 16 '13 at 14:42
  • 1
    but still be careful with that: [SQL injection that gets around mysql_real_escape_string()](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) – John Woo Jan 16 '13 at 14:43