Latest |Kites |Pictures |Programming |Life
?
[filed under Programming]MySQL compare sub query null

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 :-)

28th of October, 2008@12:00:28 PM
5 comments, permanent link to article
[filed under Programming]Zend Framework MySQL DB Pagination Tutorial

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:

<table class="my_table">
  <thead>
    <tr class="table_head">
      <th>People</th>
    </tr>
  </thead>
  <tfoot>
    <tr class="table_foot">
      <td colspan="4"><?= $this->paginationControl($this->paginator, 'Sliding', 'partials/search-pagination.phtml'); ?></td>
    </tr>
  </tfoot>
 
  <tbody>
    <?= $this->partialLoop('partial-loops/people.phtml', $this->paginator) ?>
  </tbody>
</table>

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.

 

28th of October, 2008@10:15:03 AM
3 comments, permanent link to article
[filed under Programming]Zend Framework Menus Navigation

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.

10th of September, 2008@10:08:48 AM
7 comments, permanent link to article
[filed under Programming]Zend Framework actionStack

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>

 

10th of September, 2008@10:07:44 AM
1 comments, permanent link to article
[filed under Programming]youtube

best youtube comment so far:

JESUS IM COMING
Right all over your mom

4th of September, 2008@9:55:01 AM
add a comment, permanent link to article
[filed under Programming]AS3 sprintf

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

sprinf

3rd of September, 2008@10:52:35 PM
add a comment, permanent link to article
[filed under Programming]Zend Framework MySQL Out Of Memory Error

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.

29th of August, 2008@9:22:29 AM
1 comments, permanent link to article
[filed under Programming]shotgun blast of functions

Start a side project. <-- This is quite amusing and informative. There is a classic quote:

Naturally, the first thing I did was institutionalize Subversion.
(I've apparently always been a champion of source control, though I
didn't realize it until setting this story to paper.)
 
The second thing I did was start extracting the magic numbers into
configuration files. At the time, it was a pretty common PHP idiom to
use .ini files for configuration. Most of what you'd need was
supported, and I'm pretty sure PHP came with a library (aka a shotgun
blast of functions in the global namespace) that could understand .ini
files.

Haha! Man-o-man. So true. It is impossible to find any related functions. Anyway, we're getting namespaces in PHP6, wow, welcome to the 90s, man.

21st of August, 2008@10:01:16 AM
2 comments, permanent link to article
[filed under Programming]Python memory leak detector

Tags: Python, memory leak, leek, object, garbage collection, garbage collector, __del__, inspect, gc, DEBUG_LEAK

I have a nasty memory leak in my Python script which I can't find. For some random projects my script gobbles up 2gig of memory then quietly dies.

Here is my first attempt at cobbling together a leak detector:

import gc
import inspect

def dump_garbage():
    # force collection
    print "\nCollecting GARBAGE:"
    gc.collect()
    # prove they have been collected
    print "\nCollecting GARBAGE:"
    gc.collect()
   
    print "\nGARBAGE OBJECTS:"
    for x in gc.garbage:
        s = str(x)
        if len(s) > 80: s = "%s..." % s[:80]
       
        print "::", s
        print "        type:", type(x)
        print "   referrers:", len(gc.get_referrers(x))
        try:
            print "    is class:", inspect.isclass(type(x))
            print "      module:", inspect.getmodule(x)
           
            lines, line_num = inspect.getsourcelines(type(x))
            print "    line num:", line_num
            for l in lines:
                print "        line:", l.rstrip("\n")
        except:
            pass

        print

class tmp(object):
    def __init__(self):
        a = 0

if __name__=="__main__":
    import gc
    gc.enable()
    gc.set_debug(gc.DEBUG_LEAK)

    # make a leak
    l = [tmp()]
    l.append(l)
    del l

    dump_garbage()

When run it outputs:

Collecting GARBAGE:
gc: collectable <tmp 00BE1730>
gc: collectable <list 00BED788>

Collecting GARBAGE:

GARBAGE OBJECTS:
:: <__main__.tmp object at 0x00BE1730>
        type: <class '__main__.tmp'>
   referrers: 4
    is class: True
      module: <module '__main__' from 'C:\XXXXXX.py'>
    line num: 33
        line: class tmp(object):
        line:     def __init__(self):
        line:         a = 0

:: [<__main__.tmp object at 0x00BE1730>, [...]]
        type: <type 'list'>
   referrers: 4
    is class: True
      module: None

As you can see it tries to figure out where the object is defined in your code. This should give you some clues as to where the leak is happening. If you are still having problems try adding some debug comments to the classes as you create them, print the debug info in the dump_garbage() function.

This is based on this python memory leak detector at active state. I am learning all this as I go. I guess outputing this as a .csv would help. If you have any comments or improvements drop me a line in the comments below and I'll update the code.

These are some links and stuff to help you get started hunting down your memory leak:

Update:

A simple urllib2 memory leak test.

19th of August, 2008@10:38:37 AM
add a comment, permanent link to article
[filed under Programming]Python chain methods

I don't know why I didn't know this, but you can chain methods in Python. I use this quite a bit in PHP and Zend Framework uses it a little. But here it is in Python:

class table_model:
    def __init__(self):
        pass

    def connect(self):
        print 'connect to DB'
        return self
       
    def run_query(self):
        print 'run query'
        return self

    def get_last_result(self):
        return 'Hello'


table = table_model()
res = table.connect().run_query().get_last_result()
print res

>>>
connect to DB
run query
Hello

 

That's it. Job done.

14th of August, 2008@2:34:47 PM
add a comment, permanent link to article
Server Grind [0.4331 seconds]