Zend_Db_Select and the LIKE clause

This is a post I’ve wanted to write for a long time, perhaps it will be useful to others. It’s about using Zend_Db_Table_Select and the SQL LIKE statement. I remember it giving me some headaches in the past, so here it is:

The problem is that Zend’s implementation is a bit counterintuitive, as the % sign is part of the query – as it has a special significance in SQL – and not of the string being search. So the first thing in my mind was:

My_Table_Example extends Zend_Db_Table
{
    public function search($value)
    {
        $select = $this->select();
        $select->where('column LIKE ?%', $value);

        // ...
    }
}

Which doesn’t quite work. Of course, one can always use good ol’ string concatenation, do it like this:

My_Table_Example extends Zend_Db_Table
{
    public function search($value)
    {
        $select = $this->select();
        $select->where('column LIKE "' . $value . '%"'); 

        // ...
    }
}

…and risk an SQL Injection attack. Definitely not the best approach! In the end I found the best solution to do it is:

My_Table_Example extends Zend_Db_Table
{
    public function search($value)
    {
        $select = $this->select();
        $select->where($this->_db->quoteInto('column LIKE ?', $value . '%'));

        // ...
    }
}

…which, as previously stated, it’s quite counterintuitive. But hey, it works