CiviCRM Community Forums (archive)

Hi there.

I am having a problem with creating a new smart group.  Here is my workflow:

I've created a custom search component called 'ConstituentListing'.  The component takes a state_province_id, a state_senate_district and a house_senate_district.  If I enter those data and search, I am returned the proper result set.  I can now choose 'New Smart Group' from the search page drop-down of actions.  I then name the group and save.  Everything works great.

Next I added a new action to the search page drop-down list called 'GetConstituents'.  What this action does is query the database to collect the state senate and house districts (if any) represented by the individuals selected in the current search.  Once it has those data, it redirects to the custom search component that I've described above using the query string 'force=1&csid=11&reset=1' . $shd_string . $ssd_string -- where $shd_string and $ssd_string are each a comma-separated list of districts.  Since I'm using 'force=1', the redirect skips the form input step of the custom search so instead of getting my house and senate district info from the form input, I parse it out from the query string.  This returns the same result set as if I had entered those values in the form by going directly to the custom search.

The problem is that if I now (after being redirected to the custom search via the new search page drop-down task) select 'New Smart Group' from the search page drop-down list of actions, I give the smart group a name and description then click save and I get a mysql error.


DB Error: syntax 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 'AND state_province.id = 1044 AND contact_a.id NOT IN ( ' at line 7, 1064

My debug info is:

SELECT contact_a.id as contact_id
FROM      civicrm_contact contact_a
LEFT JOIN civicrm_address address ON ( address.contact_id       = contact_a.id AND
                                       address.is_primary       = 1 )
LEFT JOIN civicrm_state_province state_province ON state_province.id = address.state_province_id
LEFT JOIN civicrm_value_1_legislative_district_in_which_t ldit ON ldit.entity_id = contact_a.id
 WHERE contact_a.contact_type   = 'Individual' AND  AND state_province.id = 1044 AND contact_a.id NOT IN (
                              SELECT contact_id FROM civicrm_group_contact
                              WHERE civicrm_group_contact.status = 'Removed'
                              AND   civicrm_group_contact.group_id = 151 )  UNION
SELECT contact_id as contact_id
FROM   civicrm_group_contact
WHERE  civicrm_group_contact.status = 'Added'
  AND  civicrm_group_contact.group_id = 151  [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 'AND state_province.id = 1044 AND contact_a.id NOT IN (
                        ' at line 7]

The 'NOT IN (...' section of the query is being added somewhere. ie - it is not part of my original query.  Why would my smart group save without any problems when doing the custom search straight from the form, but fail when giving the search exactly the same data via a redirect?

Is there something that I need to reset?  Where does the 'NOT IN (' portion of the query get added?

Thanks in advance for any help you can provide.

Yours,
axlroach

Hi lobo,

Thanks for your quick response.

It actually looks like the error is happening because the state_senate_district and state_house_district are not getting to the SaveSearch.php file.

The space between those 2 'AND's should be filled with something like:
"( (ldit.state_house_district = '016' OR ldit.state_house_district = '011') OR (ldit.state_senate_district = '06') )"

I build this part of the clause in my 'ConstituentListing' search component file by taking the values for shd and ssd from the query string.  The constituents that match those districts show up on the search page as they should.

Once I select 'New Smart Group' from the pull down on that search page though, the values for state_senate_district and state_house_district seem to get lost.  That part of the clause doesn't seem to go through to the next step. 

Does that make sense?  Is there a way to pass those values on to the SaveSearch file?

Thanks!
axlroach