Using LIKE wildcards with Zend_Db

Continuing the Zend_Db article series, let’s discuss the LIKE condition.

The LIKE condition allows you to use wildcards in the WHERE clause of an SQL statement. This allows pattern matching. It can be used in any valid SQL statement (SELECT, INSERT, UPDATE or DELETE).

LIKE wildcards:

• _ allows you to match a single character
• % allows you to match any string of any length (including zero length)

Note*:

$db = Zend_Db::factory('Pdo_Mysql', $dbConnect);

LIKE _

• Return all ids which start with ‘1’ and second digit is between 0 and 9 (10, 11, 12, …, 18, 19):

SELECT * FROM `table` WHERE (`id` LIKE '1_' )

$col = $this->db->quoteIdentifier('id'); 
$where = $this->db->quoteInto("$col LIKE ? ", '1_'); 		
$select = $this->db->select()
	->from('table')
	->where($where);
$result = $this->db->fetchAll($select);

• Return all instances whose name is 4 characters long, where the first two characters are ‘Fr’ and the last character is ‘d’ (Frad, Fred, Frod, etc.) :

SELECT * FROM `table` WHERE (`name` LIKE 'Fr_d' )

$col = $this->db->quoteIdentifier('name'); 
$where = $this->db->quoteInto("$col LIKE ? ", 'Fr_d'); 
$select = $this->db->select()
	->from('table')
	->where($where);
$result = $this->db->fetchAll($select);

LIKE %

• Returns all instances that have the ‘gallery’ string in the source field:

SELECT * FROM `table` WHERE (`source` LIKE '%gallery%' )

$col = $this->db->quoteIdentifier('source'); 
$where = $this->db->quoteInto("$col LIKE ? ", '%gallery%'); 
$select = $this->db->select()
	->from('table')
	->where($where);
$result = $this->db->fetchAll($select);

• Returns all instances that have the ‘gallery’ or ‘folder’ strings in the source field:

SELECT * FROM `table` WHERE (`source` LIKE '%gallery%' OR `source` LIKE ('%folder%') )

$col = $this->db->quoteIdentifier('source'); 
$where = $this->db->quoteInto("$col LIKE ? ", '%gallery%'); 
$where .= $this->db->quoteInto("OR $col LIKE (?) ", '%folder%');
$select = $this->db->select()
	->from('table')
	->where($where);
$result = $this->db->fetchAll($select);

NOT LIKE _

• Returns all 2-digit ids that don’t start with 1 (20->99 ) or have a different number of digits than 2 (1, 2, …, 8, 9, 100, 101, …):

SELECT * FROM `table` WHERE (`id` NOT LIKE '1_' )

$col = $this->db->quoteIdentifier('id'); 
$where = $this->db->quoteInto("$col NOT LIKE ? ", '1_'); 
$select = $this->db->select()
	->from('table')
	->where($where);
$result = $this->db->fetchAll($select);

NOT LIKE %

• Returns all instances that don’t have ‘gallery’, ‘folder’ or ‘file’ strings in the source field:

SELECT * FROM `table` WHERE (`source` NOT LIKE ('%gallery%') AND `source` NOT LIKE ('%folder%') AND `source` NOT LIKE ('%file%') )

$col = $this->db->quoteIdentifier('source'); 
$where = $this->db->quoteInto("$col NOT LIKE (?) ", '%gallery%'); 
$where .= $this->db->quoteInto("AND $col NOT LIKE (?) ", '%folder%');
$where .= $this->db->quoteInto("AND $col NOT LIKE (?) ", '%file%');		
$select = $this->db->select()
	->from('table')
	->where($where);
$result = $this->db->fetchAll($select);

OTHER Example

SELECT * FROM `table` WHERE `number` LIKE '[4-6]_6%' 

$col = $this->db->quoteIdentifier('number'); 
$where = $this->db->quoteInto("$col LIKE ? ", '[4-6]_6%'); 		
$select = $this->db->select()
	->from('table')
	->where($where);
$result = $this->db->fetchAll($select);

• The number column starts with a digit between 4 and 6 ([4-6])
• The second character in the number column can be anything (_)
• The third character in the number column is 6 (6)
• The rest of the number column can be any string, of any length (%)