CiviCRM Community Forums (archive)

*

News:

Have a question about CiviCRM?
Get it answered quickly at the new
CiviCRM Stack Exchange Q+A site

This forum was archived on 25 November 2017. Learn more.
How to get involved.
What to do if you think you've found a bug.



  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Support »
  • Using CiviCRM »
  • Using Core CiviCRM Functions (Moderator: Yashodha Chaku) »
  • Search Builder: IN (list of postal codes)
Pages: [1]

Author Topic: Search Builder: IN (list of postal codes)  (Read 1780 times)

michaels23

  • I post occasionally
  • **
  • Posts: 77
  • Karma: 1
Search Builder: IN (list of postal codes)
October 24, 2010, 12:05:46 pm
Can I use Search Builder to find contacts IN a list (*not* a range) of postal codes, e.g., 70124,70128,70003,70119,70065,70129,70002,70005,70006,70056,70115 ?

Everything I've tried results in either "in valid format" or a DB error:

Database Error Code: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''(\'70124\')' AND contact_a.contact_type IN ('Individual') ) ) AND (contact_a.' at line 1, 1064

TIA!
« Last Edit: October 24, 2010, 01:48:51 pm by michaels23 »

Yashodha Chaku

  • Forum Godess / God
  • Ask me questions
  • *****
  • Posts: 755
  • Karma: 57
    • CiviCRM
Re: Search Builder: IN (list of postal codes)
October 24, 2010, 11:23:04 pm
michaels23 :
This has been fixed for 3.3 and should be available in the next alpha release.
You could also apply the patch.

-Yashodha
Found this reply helpful? Contribute NOW and help us improve CiviCRM with the Make it Happen! initiative.

gastrit

  • I post occasionally
  • **
  • Posts: 60
  • Karma: 2
  • CiviCRM version: 4.2.2
  • CMS version: 7.8
  • MySQL version: 5.1.41
  • PHP version: 5.3.2
Re: Search Builder: IN (list of postal codes)
July 18, 2011, 02:37:45 am
Hello!

I just had the same problem in my Civicrm 4.0/Drupal 7.0 installation and I also get the same message "Please enter correct Data (in valid format)" at demo.civicrm.org (drupal 7.X).

I am trying to search for contacts with addresses in Scandinavia (Sweden, Norway and Denmark) and my search criteria looks like this:
Individual -> Country -> Home -> IN -> (Sweden,Norway,Denmark) results in above mentioned error message
and
Individual -> Country -> Home -> IN -> ('Sweden','Norway','Denmark') results in no matches although I verified that my database contains contacts from Sweden (I used 'United States' at demo.civicrm.org and got no matches) and when I look in civicrm_saved_search.where_clause it has the following value:
( LOWER(civicrm_country.name) IN ('\\\'Sweden\\\'','\\\'Norway\\\'','\\\'Denmark\\\''))

Am I doing something wrong or is this still a bug?

//Jonas Tano

xavier

  • Forum Godess / God
  • I’m (like) Lobo ;)
  • *****
  • Posts: 4453
  • Karma: 161
    • Tech To The People
  • CiviCRM version: yes probably
  • CMS version: drupal
Re: Search Builder: IN (list of postal codes)
July 18, 2011, 05:36:15 am
If you are on 4.0, you should upgrade to the latest version.

Can you try on demo.civicrm.org?

X+
-Hackathon and data journalism about the European parliament 24-26 jan. Watch out the result

gastrit

  • I post occasionally
  • **
  • Posts: 60
  • Karma: 2
  • CiviCRM version: 4.2.2
  • CMS version: 7.8
  • MySQL version: 5.1.41
  • PHP version: 5.3.2
Re: Search Builder: IN (list of postal codes)
July 18, 2011, 08:51:23 am
Thanx for fast reply in an old thread!

As I mentioned in my post I have already tried it on demo.civicrm.org:
("I also get the same message "Please enter correct Data (in valid format)" at demo.civicrm.org (drupal 7.X).")

I just tried it again with Firefox (used Chrome earlier) and got the same result:
I searched for (United States) using the IN operator (instead of the scandinavian countries) and didn't get any matches with apostrophes and got the error message when I tried without apostrophes. I also tried without parentheses with no luck. If I search for United States using the = operator I get matches.

To mee it seems to be a bug but I am fairly new to CiviCRM so I wouldn't really know. Could someone else please try to search for contacts in some countries using the IN operator in the search builder?

Donald Lobo

  • Administrator
  • I’m (like) Lobo ;)
  • *****
  • Posts: 15963
  • Karma: 470
    • CiviCRM site
  • CiviCRM version: 4.2+
  • CMS version: Drupal 7, Joomla 2.5+
  • MySQL version: 5.5.x
  • PHP version: 5.4.x
Re: Search Builder: IN (list of postal codes)
July 18, 2011, 11:49:50 am

I suspect this is a bug / missing feature in search builder. You'll need to investigate and submit a patch. Search builder is not being actively developed or maintained

lobo
A new CiviCRM Q&A resource needs YOUR help to get started. Visit our StackExchange proposed site, sign up and vote on 5 questions

gastrit

  • I post occasionally
  • **
  • Posts: 60
  • Karma: 2
  • CiviCRM version: 4.2.2
  • CMS version: 7.8
  • MySQL version: 5.1.41
  • PHP version: 5.3.2
Re: Search Builder: IN (list of postal codes)
July 21, 2011, 09:00:48 am
Ok,

I think I have found a solution to my problem but I'm not sure if this is the way to go for search builder in general. In the file CRM/Contact/Form/Search/Builder.php in the function formRule() there is a check for if the search word is in valid format. The regular expression expects to find apostrophe (') before and after every search word for the IN operator. But in the code that builds the database question there is an escape functions that adds backslashes before every SQL-dangerous character (e.g. apostrophes).

So I simply removed the check for apostrophes (just for the IN operator) and my searches are now working. I also added the possibilty to write some scandinavian and german letters (å, ä, ö, æ, ø and ü). Here are the old and new code lines:

if ( $v[1] == 'IN' ) {
...
OLD
Code: [Select]
if (!preg_match( '/^[(][\']([A-Za-z0-9\'\,\s]+)[\'][)]$/', $inVal) ) {NEW
Code: [Select]
if (!preg_match( '/^[(]([A-Za-z0-9åäöÅÄÖæÆøØüÜœŒ\,\s]+)[)]$/', $inVal) ) {...
}

I was also annoyed by the fact that I couldn't learn myself to not put spaces after the commas so I added the possibility to do so :0) That means I added a trim for each of the values found within the parentheses in the file CRM/Contact/BAO/Query.php in the function buildClause():

Code: [Select]
case 'IN'
...
foreach ( $values as $v ) {
     $v = trim( $v, " " );    // NEW LINE OF CODE
     $val[] = "'" . CRM_Utils_Type::escape( $v, $dataType ) . "'";
}

I haven't tested this much but my searches works now and I have only changed code for the IN operator so I think this pretty safe to implement. Comments anyone?

Donald Lobo

  • Administrator
  • I’m (like) Lobo ;)
  • *****
  • Posts: 15963
  • Karma: 470
    • CiviCRM site
  • CiviCRM version: 4.2+
  • CMS version: Drupal 7, Joomla 2.5+
  • MySQL version: 5.5.x
  • PHP version: 5.4.x
Re: Search Builder: IN (list of postal codes)
July 21, 2011, 12:30:25 pm

Can you please file an issue and attach your patch (http://drupal.org/node/367392) to the issue. Also please add this forum link to the issue

thanx for investigating and patching. Much appreciated

lobo
A new CiviCRM Q&A resource needs YOUR help to get started. Visit our StackExchange proposed site, sign up and vote on 5 questions

Eileen

  • Forum Godess / God
  • I’m (like) Lobo ;)
  • *****
  • Posts: 4195
  • Karma: 218
    • Fuzion
Re: Search Builder: IN (list of postal codes)
July 21, 2011, 01:46:49 pm
karma +

good sleuthing!
Make today the day you step up to support CiviCRM and all the amazing organisations that are using it to improve our world - http://civicrm.org/contribute

gastrit

  • I post occasionally
  • **
  • Posts: 60
  • Karma: 2
  • CiviCRM version: 4.2.2
  • CMS version: 7.8
  • MySQL version: 5.1.41
  • PHP version: 5.3.2
Re: Search Builder: IN (list of postal codes)
July 21, 2011, 03:55:34 pm
Thanx  :)

This is my first patch ever so please bear with me. Is this somewhat correct? http://issues.civicrm.org/jira/browse/CRM-8548

tklawsuc

  • I’m new here
  • *
  • Posts: 7
  • Karma: 0
  • CiviCRM version: 4.06
  • CMS version: Drupal 7
  • MySQL version: 5
  • PHP version: 5.2/5.3
Re: Search Builder: IN (list of postal codes)
October 19, 2011, 01:03:34 pm
I know this is an old post but I am using v4.0.6 and it seems the patches have already made it into that release. However, when I try to search using IN for city with value (City1,City2) I get an error because the buildClause (line 1813 in my version) is being called without a datatype in the Query.php file. As a result buildClause does not escape the cities and the application fails with a db error because it is using (City1,City2) instead of ('City1','City2'). When I change the call to pass in 'String' for dataType it works fine. So the question is should the buildClass function use the String dataType as a default or should the call to buildClass for IN pass in the 'String' datatype?

Donald Lobo

  • Administrator
  • I’m (like) Lobo ;)
  • *****
  • Posts: 15963
  • Karma: 470
    • CiviCRM site
  • CiviCRM version: 4.2+
  • CMS version: Drupal 7, Joomla 2.5+
  • MySQL version: 5.5.x
  • PHP version: 5.4.x
Re: Search Builder: IN (list of postal codes)
October 19, 2011, 03:36:29 pm

can u try this patch:

Code: [Select]
Index: CRM/Contact/BAO/Query.php
===================================================================
--- CRM/Contact/BAO/Query.php   (revision 36807)
+++ CRM/Contact/BAO/Query.php   (working copy)
@@ -1811,9 +1811,15 @@
                         }
                     }
                     
+                    $type = null;
+                    if ( CRM_Utils_Array::value( 'type', $field ) ) {
+                        $type = CRM_Utils_Type::typeToString( $field['type'] );
+                    }
+
                     $this->_where[$grouping][] = self::buildClause( $fieldName,
                                                                     $op,
-                                                                    $value );
+                                                                    $value,
+                                                                    $type );
                     $this->_qill[$grouping][]  = "$field[title] $op $value";
                 }
                 
A new CiviCRM Q&A resource needs YOUR help to get started. Visit our StackExchange proposed site, sign up and vote on 5 questions

tklawsuc

  • I’m new here
  • *
  • Posts: 7
  • Karma: 0
  • CiviCRM version: 4.06
  • CMS version: Drupal 7
  • MySQL version: 5
  • PHP version: 5.2/5.3
Re: Search Builder: IN (list of postal codes)
October 19, 2011, 07:21:27 pm
That works and it's a much better solution than what I had. Thanks!

Pages: [1]
  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Support »
  • Using CiviCRM »
  • Using Core CiviCRM Functions (Moderator: Yashodha Chaku) »
  • Search Builder: IN (list of postal codes)

This forum was archived on 2017-11-26.