如何在mysql中添加日期过滤器。Joomla

Question

<?php
        $database = & JFactory::getDBO();
        $query = "SELECT asset_id FROM #__content WHERE state = 1";
        $database->setQuery( $query );
        $count_at = $database->loadObjectList();
        $count_nbr = count($count_at);
?>

因此，$count_nbr返回所有创建和发布的文章，但我需要向其添加“今天”过滤器。如果某个大师能帮上忙的话...需要2种类型的过滤器："1天“和”周“…

更新:在SELECT asset_id FROM #__content WHERE state = 1 AND publish_up = $date;中添加$date = date (Y-m-d);和修改字符串无助于导致mysql m-d H:i:s中的publish_up，我需要忽略时间戳...

Answer 1

要在今天发表文章：

$today = date('Y-m-d');
$query = "SELECT asset_id FROM #__content WHERE state = 1 AND DATE_FORMAT(publish_up, '%Y-%m-%d') = '$today'";
...

它使用MySQL DATE_FORMAT()函数将发布日期格式化为Y-m-d格式，以便您可以对其进行比较。

要获得本周发表的文章，请使用YEARWEEK()函数：

$query = "SELECT asset_id FROM #__content WHERE state = 1 AND YEARWEEK(publish_up) = YEARWEEK(NOW())";