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) »
  • Want to search with a list of zip codes
Pages: [1]

Author Topic: Want to search with a list of zip codes  (Read 622 times)

jimcrist

  • I’m new here
  • *
  • Posts: 8
  • Karma: 0
  • CiviCRM version: 4.4
  • CMS version: WordPress
  • MySQL version: 5.5.42-37.1-log
  • PHP version: 5.4.38
Want to search with a list of zip codes
March 25, 2015, 01:13:23 pm
Hi,
I've got CiviCRM installed and I imported contacts. Now I want to be able to search on a list of zip codes. For example, All contacts where the zip is one of these: 53711, 53719, 53595. The list might be a dozen or two dozen zips long. I tried using Search Builder, but couldn't get it to work at all?! Even searching on a single zip or a city...(e.g.  Contacts City Home = Madison. I keep getting this error: Sorry but we are not able to provide this at the moment. DB Error: no such field

The basic search works fine, as does the advanced search. So, two questions (1) searching on a list of zips (2) why isn't Search Builder working?

Thanks in advance...jim

jimcrist

  • I’m new here
  • *
  • Posts: 8
  • Karma: 0
  • CiviCRM version: 4.4
  • CMS version: WordPress
  • MySQL version: 5.5.42-37.1-log
  • PHP version: 5.4.38
Re: Want to search with a list of zip codes
March 25, 2015, 01:25:46 pm
I just did a debug / backtrace on the Search Builder and got this...it's greek to me!

backTrace

#0 /home1/scwmtaor/public_html/wp-content/plugins/civicrm/civicrm/CRM/Core/Error.php(149): CRM_Core_Error::backtrace()
#1 [internal function](): CRM_Core_Error::handle(Object(DB_Error))
#2 /home1/scwmtaor/public_html/wp-content/plugins/civicrm/civicrm/packages/PEAR.php(931): call_user_func((Array:2), Object(DB_Error))
#3 /home1/scwmtaor/public_html/wp-content/plugins/civicrm/civicrm/packages/DB.php(969): PEAR_Error->PEAR_Error("DB Error: no such field", -19, 16, (Array:2), "SELECT DISTINCT UPPER(LEFT(contact_a.sort_name, 1)) as sort_name  FROM civicr...")
#4 /home1/scwmtaor/public_html/wp-content/plugins/civicrm/civicrm/packages/PEAR.php(564): DB_Error->DB_Error(-19, 16, (Array:2), "SELECT DISTINCT UPPER(LEFT(contact_a.sort_name, 1)) as sort_name  FROM civicr...")
#5 /home1/scwmtaor/public_html/wp-content/plugins/civicrm/civicrm/packages/DB/common.php(1905): PEAR->raiseError(NULL, -19, NULL, NULL, "SELECT DISTINCT UPPER(LEFT(contact_a.sort_name, 1)) as sort_name  FROM civicr...", "DB_Error", TRUE)
#6 /home1/scwmtaor/public_html/wp-content/plugins/civicrm/civicrm/packages/DB/mysql.php(898): DB_common->raiseError(-19, NULL, NULL, NULL, "1054 ** Unknown column 'Home-address.city' in 'where clause'")
#7 /home1/scwmtaor/public_html/wp-content/plugins/civicrm/civicrm/packages/DB/mysql.php(327): DB_mysql->mysqlRaiseError()
#8 /home1/scwmtaor/public_html/wp-content/plugins/civicrm/civicrm/packages/DB/common.php(1216): DB_mysql->simpleQuery("SELECT DISTINCT UPPER(LEFT(contact_a.sort_name, 1)) as sort_name  FROM civicr...")
#9 /home1/scwmtaor/public_html/wp-content/plugins/civicrm/civicrm/packages/DB/DataObject.php(2434): DB_common->query("SELECT DISTINCT UPPER(LEFT(contact_a.sort_name, 1)) as sort_name  FROM civicr...")
#10 /home1/scwmtaor/public_html/wp-content/plugins/civicrm/civicrm/packages/DB/DataObject.php(1626): DB_DataObject->_query("SELECT DISTINCT UPPER(LEFT(contact_a.sort_name, 1)) as sort_name  FROM civicr...")
#11 /home1/scwmtaor/public_html/wp-content/plugins/civicrm/civicrm/CRM/Core/DAO.php(160): DB_DataObject->query("SELECT DISTINCT UPPER(LEFT(contact_a.sort_name, 1)) as sort_name  FROM civicr...")
#12 /home1/scwmtaor/public_html/wp-content/plugins/civicrm/civicrm/CRM/Core/DAO.php(908): CRM_Core_DAO->query("SELECT DISTINCT UPPER(LEFT(contact_a.sort_name, 1)) as sort_name  FROM civicr...", TRUE)
#13 /home1/scwmtaor/public_html/wp-content/plugins/civicrm/civicrm/CRM/Contact/BAO/Query.php(4424): CRM_Core_DAO::executeQuery("SELECT DISTINCT UPPER(LEFT(contact_a.sort_name, 1)) as sort_name  FROM civicr...")
#14 /home1/scwmtaor/public_html/wp-content/plugins/civicrm/civicrm/CRM/Contact/Selector.php(1076): CRM_Contact_BAO_Query->searchQuery(NULL, NULL, NULL, FALSE, FALSE, TRUE)
#15 /home1/scwmtaor/public_html/wp-content/plugins/civicrm/civicrm/CRM/Utils/PagerAToZ.php(81): CRM_Contact_Selector->alphabetQuery()
#16 /home1/scwmtaor/public_html/wp-content/plugins/civicrm/civicrm/CRM/Utils/PagerAToZ.php(106): CRM_Utils_PagerAToZ::getDynamicCharacters(Object(CRM_Contact_Selector), FALSE)
#17 /home1/scwmtaor/public_html/wp-content/plugins/civicrm/civicrm/CRM/Utils/PagerAToZ.php(53): CRM_Utils_PagerAToZ::createLinks(Object(CRM_Contact_Selector), NULL, FALSE)
#18 /home1/scwmtaor/public_html/wp-content/plugins/civicrm/civicrm/CRM/Contact/Form/Search.php(937): CRM_Utils_PagerAToZ::getAToZBar(Object(CRM_Contact_Selector), NULL)
#19 /home1/scwmtaor/public_html/wp-content/plugins/civicrm/civicrm/CRM/Contact/Form/Search/Builder.php(394): CRM_Contact_Form_Search->postProcess()
#20 /home1/scwmtaor/public_html/wp-content/plugins/civicrm/civicrm/CRM/Core/Form.php(261): CRM_Contact_Form_Search_Builder->postProcess()
#21 /home1/scwmtaor/public_html/wp-content/plugins/civicrm/civicrm/CRM/Core/QuickForm/Action/Refresh.php(75): CRM_Core_Form->mainProcess()
#22 /home1/scwmtaor/public_html/wp-content/plugins/civicrm/civicrm/packages/HTML/QuickForm/Controller.php(203): CRM_Core_QuickForm_Action_Refresh->perform(Object(CRM_Contact_Form_Search_Builder), "refresh")
#23 /home1/scwmtaor/public_html/wp-content/plugins/civicrm/civicrm/packages/HTML/QuickForm/Page.php(103): HTML_QuickForm_Controller->handle(Object(CRM_Contact_Form_Search_Builder), "refresh")
#24 /home1/scwmtaor/public_html/wp-content/plugins/civicrm/civicrm/CRM/Core/Controller.php(339): HTML_QuickForm_Page->handle("refresh")
#25 /home1/scwmtaor/public_html/wp-content/plugins/civicrm/civicrm/CRM/Core/Invoke.php(327): CRM_Core_Controller->run((Array:4), (Array:0))
#26 /home1/scwmtaor/public_html/wp-content/plugins/civicrm/civicrm/CRM/Core/Invoke.php(76): CRM_Core_Invoke::runItem((Array:13))
#27 /home1/scwmtaor/public_html/wp-content/plugins/civicrm/civicrm/CRM/Core/Invoke.php(52): CRM_Core_Invoke::_invoke((Array:4))
#28 /home1/scwmtaor/public_html/wp-content/plugins/civicrm/civicrm.php(515): CRM_Core_Invoke::invoke((Array:4))
#29 [internal function](): CiviCRM_For_WordPress->invoke("")
#30 /home1/scwmtaor/public_html/wp-includes/plugin.php(496): call_user_func_array((Array:2), (Array:1))
#31 /home1/scwmtaor/public_html/wp-admin/admin.php(212): do_action("toplevel_page_CiviCRM")
#32 {main}

jimcrist

  • I’m new here
  • *
  • Posts: 8
  • Karma: 0
  • CiviCRM version: 4.4
  • CMS version: WordPress
  • MySQL version: 5.5.42-37.1-log
  • PHP version: 5.4.38
Re: Want to search with a list of zip codes
March 25, 2015, 02:22:11 pm
I don't think there's any Linux cron jobs set up for CiviCRM/WordPress, so that could be a problem? I'm not a Linux person...

JonGold

  • Ask me questions
  • ****
  • Posts: 638
  • Karma: 81
    • Palante Technology
  • CiviCRM version: 4.1 to the latest
  • CMS version: Drupal 6-7, Wordpress 4.0+
  • PHP version: PHP 5.3-5.5
Re: Want to search with a list of zip codes
March 25, 2015, 07:10:45 pm
Hi Jim,

That shouldn't be the problem here - this is a SQL error.  You've posted the backtrace, but could you also paste the exact error?  I can see the SQL error, but it's cut off - pasting the full error will let us see the full SQL error. Also, maybe a screenshot of how you're using Search Builder here?  There's more than one way to do this search in Search Builder, one slightly more tedious but more foolproof.
Sign up to StackExchange and get free expert CiviCRM advice: https://civicrm.org/blogs/colemanw/get-exclusive-access-free-expert-help

jimcrist

  • I’m new here
  • *
  • Posts: 8
  • Karma: 0
  • CiviCRM version: 4.4
  • CMS version: WordPress
  • MySQL version: 5.5.42-37.1-log
  • PHP version: 5.4.38
Re: Want to search with a list of zip codes
March 26, 2015, 04:46:23 am
Thanks Jon,
I've attached screen shots of the search criteria and the resulting error. Hopefully, that's the "full SQL error" you're referring to? If I do the same search using the Advanced Search I get 1733 records. My real goal is to be able to search for zip = 53701, 53711, 53590, etc.
Thanks!
Jim
« Last Edit: March 26, 2015, 04:53:12 am by jimcrist »

JonGold

  • Ask me questions
  • ****
  • Posts: 638
  • Karma: 81
    • Palante Technology
  • CiviCRM version: 4.1 to the latest
  • CMS version: Drupal 6-7, Wordpress 4.0+
  • PHP version: PHP 5.3-5.5
Re: Want to search with a list of zip codes
March 26, 2015, 07:06:25 am
Hi Jim,

That's not quite what I meant - but how about trying your postal code search like in this screenshot?
Sign up to StackExchange and get free expert CiviCRM advice: https://civicrm.org/blogs/colemanw/get-exclusive-access-free-expert-help

jimcrist

  • I’m new here
  • *
  • Posts: 8
  • Karma: 0
  • CiviCRM version: 4.4
  • CMS version: WordPress
  • MySQL version: 5.5.42-37.1-log
  • PHP version: 5.4.38
Re: Want to search with a list of zip codes
March 26, 2015, 10:40:04 am
Jon,
Awesome! Works like a charm. I could have sworn I tried Primary yesterday. Primary works. Home, which should work, gives me the DB Error: no such field. And the Regex zip|zip|zip works. That's exactly what I was looking for...thanks!
Jim
Pages: [1]
  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Support »
  • Using CiviCRM »
  • Using Core CiviCRM Functions (Moderator: Yashodha Chaku) »
  • Want to search with a list of zip codes

This forum was archived on 2017-11-26.