Uploaded image for project: 'CiviCRM'
  1. CiviCRM
  2. CRM-21130

Search builder throws error if location type has space

    Details

    • Versioning Impact:
      Patch (backwards-compatible bug fixes)
    • Documentation Required?:
      None
    • Funding Source:
      Contributed Code
    • Verified?:
      No

      Description

      Steps to replicate

      1. Add new Location type say 'Mentor Directory'
      2. Using Search Builder, search for all contacts with email (Mentor Directory)
      3. Results in 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 'JOIN civicrm_location_type `Mentor_Directory-location_type` ON ( ( `Mentor_Dire' at line 2, 1064
        Additional Details: 
        Array ( [callback] => Array ( [0] => CRM_Core_Error [1] => handle ) [code] => -2 [message] => DB Error: syntax error [mode] => 16 [debug_info] => SELECT DISTINCT UPPER(LEFT(contact_a.sort_name, 1)) as sort_name FROM civicrm_contact contact_a LEFT JOIN civicrm_email `Mentor_Directory-email` ON ( contact_a.id = `Mentor_Directory-email`.contact_id ) and `Mentor_Directory-email`.location_type_id = LEFT JOIN civicrm_location_type `Mentor_Directory-location_type` ON ( ( `Mentor_Directory-email`.location_type_id = `Mentor_Directory-location_type`.id ) ) WHERE ( ( LOWER(`Mentor_Directory-email`.email) IS NULL ) ) AND (contact_a.is_deleted = 0) GROUP BY contact_a.id ORDER BY UPPER(LEFT(contact_a.sort_name, 1)) asc [nativecode=1064 ** 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 'JOIN civicrm_location_type `Mentor_Directory-location_type` ON ( ( `Mentor_Dire' at line 2] [type] => DB_Error [user_info] => SELECT DISTINCT UPPER(LEFT(contact_a.sort_name, 1)) as sort_name FROM civicrm_contact contact_a LEFT JOIN civicrm_email `Mentor_Directory-email` ON ( contact_a.id = `Mentor_Directory-email`.contact_id ) and `Mentor_Directory-email`.location_type_id = LEFT JOIN civicrm_location_type `Mentor_Directory-location_type` ON ( ( `Mentor_Directory-email`.location_type_id = `Mentor_Directory-location_type`.id ) ) WHERE ( ( LOWER(`Mentor_Directory-email`.email) IS NULL ) ) AND (contact_a.is_deleted = 0) GROUP BY contact_a.id ORDER BY UPPER(LEFT(contact_a.sort_name, 1)) asc [nativecode=1064 ** 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 'JOIN civicrm_location_type `Mentor_Directory-location_type` ON ( ( `Mentor_Dire' at line 2] [to_string] => [db_error: message="DB Error: syntax error" code=-2 mode=callback callback=CRM_Core_Error::handle prefix="" info="SELECT DISTINCT UPPER(LEFT(contact_a.sort_name, 1)) as sort_name FROM civicrm_contact contact_a LEFT JOIN civicrm_email `Mentor_Directory-email` ON ( contact_a.id = `Mentor_Directory-email`.contact_id ) and `Mentor_Directory-email`.location_type_id = LEFT JOIN civicrm_location_type `Mentor_Directory-location_type` ON ( ( `Mentor_Directory-email`.location_type_id = `Mentor_Directory-location_type`.id ) ) WHERE ( ( LOWER(`Mentor_Directory-email`.email) IS NULL ) ) AND (contact_a.is_deleted = 0) GROUP BY contact_a.id ORDER BY UPPER(LEFT(contact_a.sort_name, 1)) asc [nativecode=1064 ** 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 'JOIN civicrm_location_type `Mentor_Directory-location_type` ON ( ( `Mentor_Dire' at line 2]"] )

        Attachments

          Activity

            People

            • Assignee:
              yashodha Yashodha Chaku
              Reporter:
              yashodha Yashodha Chaku
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: