Re: Re: DB_DataObject: support for LIKE expressions

Stijn de Reede wrote:

I dunno if this should be in the main release. IMHO it complicates things, and certainly doesn't make DB_DataObject easier to use. Setting the option in to ini file isn't a good idea either, because I think you might want to change behaviour mid-script. In my opinion this should not be done at such a low level as DB_DataObject is.

if it is of any interest: DB_QueryTool has a so called addtWhereSearch() - method which works like this: $table->addWhereSearch('text','some STRING') builds the following where part WHERE LOWER(text) LIKE '%some%string%'

Stijn Mitchell Perilstein wrote:

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, ------------------------------------------------------------------------

--- 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! */
 

-- Wolfram ... translating template system ... http://sourceforge.net/projects/simpletpl now also as PEAR-package available ... authentication system .... http://sf.net/projects/auth ... and more at .... http://opensource.visionp.de