Search Operators – not just for Google anymore

Google is nifty. One thing I particularly like is their use of advanced search operators (I call them search expressions…but who am I to change Google’s nomenclature). If you don’t know what I’m talking about, it’s the ability to do cool stuff like this:

I liked this idea so well that I stole incorporated it into a project I’m working on. I wanted to be able to search for invoices using Google-esque search operators so I came up with this little nugget (more after the jump)

First I had to define my search operators.

$expressions = array
(
	'name' 		=> array(
		'type'		=> 'LIKE',
		'columns'	=> array('invoices.contact_name_first','invoices.contact_name_last')),
	'cc' 		=> array(
		'type'		=> 'EXACT',
		'columns'	=> 'billing_methods.creditcard_4_digits',
		'jointable'	=> 'billing_methods',
		'joinon'	=> 'billing_method_ID'),
	'status'	=> array(
		'type'		=> 'EXACT',
		'columns'	=> 'invoices.invoice_status'),
	'account'	=> array(
		'type'		=> 'EXACT',
		'columns'	=> 'invoices.account_ID'),
	'id'		=> array(
		'type'		=> 'EXACT',
		'columns'	=> 'invoices.invoice_ID'),
	'company'	=> array(
		'type'		=> 'LIKE',
		'columns'	=> 'invoices.contact_company_name')
);

The array keys are my actual operators (or keywords) that can be used in a search expression (IE: name:Bob, company:ACME). The array values define the type of search (EXACT or LIKE) and where to find the information in your database.

Once you have your expressions defined you just need to build the query. Here’s how I did it:

if (preg_match('#(\w+):\s*(\w+)#',$exp,$patterns))
{
	if (array_key_exists($patterns[1],$expressions))
	{
		if (array_key_exists('jointable',$expressions[$patterns[1]]))
		{
			$join = " JOIN {$expressions[$patterns[1]]['jointable']} on invoices.{$expressions[$patterns[1]]['joinon']} = {$expressions[$patterns[1]]['jointable']}.{$expressions[$patterns[1]]['joinon']}";
		}
		if (is_array($expressions[$patterns[1]]['columns']))
		{
			foreach ($expressions[$patterns[1]]['columns'] as $v)
			{
				if ($expressions[$patterns[1]]['type'] == 'LIKE')
					$_where .= $_where ? " OR $v like '%{$patterns[2]}%'" : " WHERE $v like '%{$patterns[2]}%'";
				else 
					$_where .= $_where ? " OR $v ='{$patterns[2]}'" : " WHERE $v='{$patterns[2]}'";
			}
		}
		else 
		{
			if ($expressions[$patterns[1]]['type'] == 'LIKE')
				$_where .= $_where ? " OR {$expressions[$patterns[1]]['columns']} like '%{$patterns[2]}%'" : " WHERE {$expressions[$patterns[1]]['columns']} like '%{$patterns[2]}%'";
			else
				$_where .= $_where ? " OR {$expressions[$patterns[1]]['columns']} = '{$patterns[2]}'" : " WHERE {$expressions[$patterns[1]]['columns']} = '{$patterns[2]}'";
		}
	}
	else #it was an expression, but not one we know about.  we'll ignore the bogus exp and do a name search 
	{
		foreach ($expressions['name']['columns'] as $v)
		{
			$_where .= $_where ? " OR $v like '%{$patterns[2]}%'" : " WHERE $v like '%{$patterns[2]}%'";
		}
	}
}
else #no expression given...do a name search
{
	foreach ($expressions['name']['columns'] as $v)
	{
		$_where .= $_where ? " OR $v like '%$exp%'" : " WHERE $v like '%$exp%'";
	}
}
 
$_qry = "SELECT invoice_ID from invoices ";
if ($join)
{
	$_qry .= $join;
}
$_qry .= $_where;
return $_qry;
}

First I parse the search query (passed from a form) and check to see if they used any defined search operators. If they did I construct a query based on the operator definition from above. In my case I only needed the IDs of any invoicing matching the query…your application may require additional database columns to be returned. In that case you would just need to change the

$_qry = "SELECT invoice_ID from invoices ";

Line to include additional DB columns.

So there you have it…it looks more complicated than it really is, but it gives your users an easy way to find the information they’re looking for. Trust me, they’ll love it!

Don't be stingy...share with your friends!

Leave a Reply

Your email address will not be published. Required fields are marked *