Hey, this is cool. I had a query like so:

SELECT stuff
FROM table
WHERE
col = ( SELECT col FROM table_2 WHERE id IN (1,2,3) )

And the sub query was returning NULL sometimes. So I wanted the rows from table where col is null. But the syntax to do that would be:

SELECT stuff
FROM table
WHERE
col IS ( SELECT col FROM table_2 WHERE id IN (1,2,3) )

Now this query isn't going to work when the sub query returns a number. The answer is to use this funky operator thatsolves this problem <=>, the null safe equality operator:

SELECT stuff
FROM table
WHERE
col <=> ( SELECT col FROM table_2 WHERE id IN (1,2,3) )

Smooth :-)

Tags: Zend Framework 1.7 Pagination MySQL SQL Pages Paged Page Next Prev Previous

Just a quick how-to-make a MySQL table paginatior in Zend Framework.

You'll need these as a reference, zend.paginator.usage and zend.db.select.

First in your controller make a link to MySQL and make an SQL select() object (I find this disgusting syntax, but hey ho)... So, in the following code I am in my controllers action method:

public function myTableAction()
{

    $db = Zend_Db::factory( $this->config->db );
       
    $select = $db->select()
            ->from(array('p' => 'people'), array('id','name'))
            ->order('name');
   
    //echo $select->__toString();
    $this->view->paginator = Zend_Paginator::factory($select);
    $this->view->paginator->setCurrentPageNumber($this->_getParam('page'));
}

So you have passed the MySQL syntax object to the paginator. This will add the LIMIT to the SQL and run it. The LIMIT syntax is built up from the page you are on and how many items per page you are displaying, this information is grabbed from the URL.

Next you need to make your view show this data and the:<prev 1 2 3 4 5 next >HTML. The paginatior object we passed to the view in the above code (the second to last line) is an iterator, so we can just loop over it:

OK, so the bit that builds the table is the partialLoop, we pass our pagination object that contains our table rows to the partialLoop iterator. This builds each <tr> row from the paginatior, the paginatior got the data from the MySQL table (after it manipulated your SQL adding a LIMIT 10,10) and got the item count from your table (it altered your query adding a COUNT() and re-queried the DB)

The loop partial looks like this (partial-loops/people.phtml):

<tr>
    <td><?= $this->name ?></td>
</tr>

Note how we have access to the two columns from MySQL as object properties. BTW I keep my view .phtml files in views/scripts and my partials in views/scripts/partials and my loop partials in views/scripts/loop-partials. You don't have to follow what I do, just make sure your paths make sense.

The style of the:<prev 1 2 3 4 5 next >HTML is in the partials/search-pagination.phtml view, this you can copy from this page (zend.paginator.usage -- Example Paginations Controls (at the end of the page)) and save it in the correct script view directory. Mine is a modified version (partials/search-pagination.phtml):

<?php if ($this->pageCount): ?>
<div class="pagination">

<?php if (isset($this->previous)): ?>
  <a href="<?= $this->url(array('page' => $this->previous)); ?>">&laquo; PREV</a> -
<?php endif; ?>

<?php
    /* Page links */
   
    foreach ($this->pagesInRange as $page): ?>
    <a href="<?= $this->url(array('page' => $page)); ?>" <?php if($page == $this->current): ?>id="selected"><?php endif; ?><?= $page; ?></a>
<?php endforeach; ?>

<?php if (isset($this->next)): ?>
 - <a href="<?= $this->url(array('page' => $this->next)); ?>">Next &gt;</a>
<?php endif; ?>

</div>
<?php endif; ?>

That's it really. You'll need a bit of CSS to style the links.

For more info take a look at the Zend Framework wiki: Zend_Paginator

Job done.

Tags: Zend Framework, menus, controller, action, navigation, template, menu.phtml, actionStack, setResponseSegment, renderScript

This is how to put menu logic in your web page.

OK, from the get go, this is hard. You may need to read it a couple of times or do more research.

What we want is a web page with a menu system (a list of items) on the left of the page. The menu will be different for each controller and action.

What usually happens in ZF is the ApplicationController class is created and the method indexAction is called. indexAction then uses the model to get data and passes info to the view (for example using $this->view->data = 99; ). The view is then rendered as a string, which is appended to the Layout content.

It took me a while to figure that out.

To make the menu we need a seperate controller, the MenuController will decide what to show on the menu. The the menu view will render it and we capture the output of the view into a layout variable. The menu HTML is used later in the layout.phtml template.

In our application controller we push the "menu" onto the actionStack which makes ZF run MenuController::applicationAction next.

ApplicationController.php
class ApplicationController extends Zend_Controller_Action
{
    public function __construct(...)
    {
        //...
        // this tells the framework to run the MenuController after this
        $this->_helper->actionStack('application', 'menu');
    }
}

MenuController.php
class MenuController extends Zend_Controller_Action
{
    public function applicationAction
    {
        // we don't want to append the menu to the end
        // of the layout content, so:
        $this->_helper->viewRenderer->setResponseSegment('menu');
        $this->view->menu = array('x', 'y', 'z');
    }
   
    public function anotherAction
    {
        $this->_helper->viewRenderer->setResponseSegment('menu');
        $this->view->menu = array('a', 'b', 'b');
    }
}

layout.phtml (/layouts/)
<html><head></head><body>

<?= $this->layout()->menu; ?>

<?= $this->layout()->content; ?>

</body></html>

application.phtml (in /views/scripts/menu/)
<ul>
<?php foreach( $this->menu as $m ): ?>
  <li><?= $m ?></li>
<?php endforeach; ?>
</ul>

another.phtml (in /views/scripts/menu/)
<ul>
<?php foreach( $this->menu as $m ): ?>
  <li><?= $m ?></li>
<?php endforeach; ?>
</ul>

The MenuController changes the layout response segment to 'menu', all the HTML in the menu view is saved to this variable.

This HTML to make the menu is then merged into the web page in layout.phtml.

More notes on Zend Framework Menu Navigation.

Further notes on Zend Framework Menu Navigation

This is how you pass variables to another action on the actionStack:

$this->_helper->actionStack('application', 'menu', 'default', array('show_menu'=>$this->getRequest()->action));

Then in your menu controller, ApplicationController::menuAction you can:

$show_menu = $this->_getParam( 'show_menu' );

Then you can use this variable to highlight the correct menu item, e.g:

<ul>
  <li>menu 1</li>
  <li id="selected">menu 2</li>
  <li>menu 3</li>
</ul>

best youtube comment so far:

JESUS IM COMING
Right all over your mom

Tags: Actionscript flash AS3 flex adobe print format number format date string format float

sprinf

Tags: Zend framework, ZF, PHP, MySQL, MySQLi, PDO, SQL, FORMAT, LONGTEXT, FLOAT, floating point, out of memory error

There is a bug in the MySQLi (Note the i) adapter that causes FORMAT() to fail.

For more info see the Zend DB email list. Here is the ZF DB MySQLi bug report.

Hope that helps someone out :-) it's one of the obscure little bugs that are hard to pin down.