DB_DataObject: support for LIKE expressions

Here is a simple patch to DataObject.php that will allow LIKE queries by automatically using "LIKE" instead of "=" as the string comparison operator in the SQL. You might say, that's what ::whereAdd() and ::query() are there for. Yes, if you are building a query by hand, but this will save you one line of code! :-) Seriously, with this patch, if you have a form which you are copying to an object and then doing a ::find(), you need merely do this: $obj = new DataObject_MyFoo(); $obj->setFrom(&$HTTP_POST_VARS); $obj->find(); ... to allow users to include the '%' and '_' wildcard characters in their text fields to do search queries. /*** SIDE NOTE: for this purpose, I had to override the setFrom method because PHP sets HTTP_POST_VARS to the empty ('') string if a text input field has nothing in it when the form is submitted. This is bad because then a query like above goes astray from what I want by adding WHERE Field1='' AND Field2='', etc.; which is not what is intended in this case. My override looks for empty() strings and sets them to null ***/ If any field contains '%' or '_', then instead of the normal query, SELECT * FROM tCustomer WHERE last LIKE 'a%' AND state = 'SD' you would get SELECT * FROM tCustomer WHERE last = 'a%' AND state = 'SD' where the inputs were last => "a%" and state => "SD". A new .ini word "allow_wildcards" enables this; there no change if it is not set. For wildcards in mysql, see http://www.mysql.com/doc/en/String_comparison_functions.html Attached patch only tested with mysql. Hth someone, -- Mitchell Perilstein mitch at enetis dot net www.enetis.net/~mitch

--- DB/DataObject.php.orig 2003-01-16 11:21:20.000000000 -0700 +++ DB/DataObject.php 2003-01-18 00:00:24.000000000 -0700 @@ -1095,12 +1095,20 @@ continue; } + // Use LIKE instead of '=' if the field contains '%' or '_'. + // Option "allow_wildcards" in the .ini file enables this. + $options = &PEAR::getStaticProperty('DB_DataObject','options'); + $operator = (@$options['allow_wildcards'] && + preg_match('/%|_/', $this->$k)) + ? ' LIKE ' + : ' = '; + if ($v & DB_DATAOBJECT_STR) { - $this->whereAdd($k .' = ' . $__DB->quote($this->$k) ); + $this->whereAdd($k . $operator . $__DB->quote($this->$k) ); continue; } if (is_numeric($this->$k)) { - $this->whereAdd("$k = {$this->$k}"); + $this->whereAdd("$k $operator {$this->$k}"); continue; } /* this is probably an error condition! */