how to write this query using DATE_FORMAT using Zend Framework?

Question

Basically, I want a news page with Archives list in the side column based on the month and year.. for example June 2012. The table is named 'news', with columns news_id, news_title, news_entry, updated, created. Updated and created are timestamps. Right now I am using normal php as below:

mysql_select_db($database_admin_conn, $admin_conn);
$query_getArchives = "SELECT DISTINCT DATE_FORMAT (news.updated, '%M %Y') AS archive, DATE_FORMAT (news.updated, '%Y-%m') AS link FROM news ORDER BY news.updated DESC";
$getArchives = mysql_query($query_getArchives, $admin_conn) or die(mysql_error());
$row_getArchives = mysql_fetch_assoc($getArchives);
$totalRows_getArchives = mysql_num_rows($getArchives);

Code to display archives column is as below:

<h3>News Archives</h3>
<ul>
<?php do { ?>
<li class="seperator
<?php echo (isset($_GET['archive']) && $_GET['archive'] == $row_getArchives['link'] ? 'currentItem' : '') ?>">
<a href="news.php?archive=<?php echo $row_getArchives['link']; ?>"><?php echo $row_getArchives['archive']; ?></a>
</li>
<?php } while ($row_getArchives = mysql_fetch_assoc($getArchives)); ?>
</ul>

How do I realise the select statement in the first block of code in Zend Framework, to achieve the same result as the present code output? Thanks in advance!

shukshin.ivan · Answer 1 · 2013-02-08T06:13:13.367

0

First create table class into /models/DbTable/Archive.php or using zf command tool.

class Application_Model_DbTable_Archive extends Zend_Db_Table_Abstract
{
    protected $_name = 'news'; // actual table name here
}

Then use the following code

$table = new Application_Model_DbTable_Archive();

$select = $table->getAdapter()->select()
            ->from(array('news' => $table->info(Zend_Db_Table::NAME)), 
                array(
                    'archive'=>new Zend_Db_Expr('DISTINCT DATE_FORMAT (news.updated, \'%M %Y\')'), 
                    'link'=>new Zend_Db_Expr('DISTINCT DATE_FORMAT (news.updated, \'%Y-%m\')')))
            ->order('news.updated DESC');
// then use $select
$row_getArchives = $table->fetchAll($select);
$totalRows_getArchives  = $row_getArchives ->count();

and the view

foreach($row_getArchives as $row){
    echo $row->link;
}

If you don`t want to use table classes, try this one:

$adapter = Zend_Db_Table::getDefaultAdapter();
$select = $adapter->select()
        ->from(array('news' => 'news'), 
            array(
                'archive'=>new Zend_Db_Expr('DISTINCT DATE_FORMAT (news.updated, \'%M %Y\')'), 
                'link'=>new Zend_Db_Expr('DISTINCT DATE_FORMAT (news.updated, \'%Y-%m\')')))
        ->order('news.updated DESC');
// then use $select
$row_getArchives = $adapter->fetchAll($select);
$totalRows_getArchives  = count($row_getArchives);

and the view

foreach($row_getArchives as $row){
    echo $row['link'];
}

edited Feb 08 '13 at 06:13

answered Feb 06 '13 at 18:28

shukshin.ivan

11,075
4
53
69

@TimFountain I just love to use db-classes, since they keep table name and I don`t need to remember it, I just use `$table->info(Zend_Db_Table::NAME)` In case I wish to change table name, I have to change it just in the class. It`s convenient. – shukshin.ivan Feb 06 '13 at 20:35
Thanks Ivan, but that is leavin me with this error: Fatal error: Class 'Application_Model_DbTable_Archive' not found in C:\vhosts\balaji\news\news.php on line 15. Reading the Zend help documentation now :) – atomheart Feb 07 '13 at 06:08
@atomheart you should first create zf project and then create table class. Try the updated answer and accept it if works =) – shukshin.ivan Feb 07 '13 at 06:44
Thanks for your reply Ivan. For this particular project, I am only using Zend for connecting to the database using using library/Zend/Loader/Autoloader.php, and reading and writing into database. For my next learning project, I intend to get more involved with Zend. For my present requirement, I wanted to know if the select statement can be realised in a zend select statement format. something like: $getRecent = $dbRead->select('news_id', 'news_title')->FROM('news')->ORDER(updated); – atomheart Feb 07 '13 at 17:01
@atomheart If you don`t want to create table class, use the updated answer. – shukshin.ivan Feb 08 '13 at 06:14
Hello Ivan, thanks again. But It comes up with an error: Fatal error: Call to a member function select() on a non-object .. – atomheart Feb 08 '13 at 19:00
@atomheart Ok, your problem is adapter initialization rather than just query. You should fix your question. Good luck. – shukshin.ivan Feb 09 '13 at 20:05

how to write this query using DATE_FORMAT using Zend Framework?

1 Answers1