从sql数据库获取月，日，年的php时间

Question

1398305717 <- my sql database time code like this

mysql time row name is time

.php

$numMonth = date('m', $time);

$sql = mysql_query("SELECT * FROM videos WHERE date('m',`time`) = $numMonth DESC LIMIT 1");
$row = mysql_fetch_assoc($sql);
echo $id = $row['id'];

i want to get last id equals to month numer? so i tried to like this.. but not work. how to get it from php. so help me to this post how to get month number from sql.. thanks.

Edit : i want like this.. but not work :(

$sql = mysql_query("SELECT * FROM videos WHERE MONTH(FROM_UNIXTIME(`time`)) = '$numMonth' DESC LIMIT 1");

Answer 1

you have to convert the timestamp then extract the month. and you don't need to use php date functions to get the current month you can just use mysql's functions to do it

$sql = mysql_query("SELECT *, DATE_FORMAT(FROM_UNIXTIME(`time`),'%Y') as year,DATE_FORMAT(FROM_UNIXTIME(`time`),'%m') as month FROM videos WHERE MONTH(FROM_UNIXTIME(`time`)) = MONTH(CURDATE()) ORDER BY ID  LIMIT 1");

working demo: http://sqlfiddle.com/#!2/b4e465/8

don't forget about year, if you expect multiple years of timestamps