By 8ballsaysiwin on

I am relative new to PHP, could someone please tell me what the error in this syntax is?

    $types = crmax_query("SELECT value, label FROM {civicrm_option_value} WHERE option_group_id=(SELECT id FROM {civicrm_option_group} WHERE name='activity_type') AND filter=0 and is_active=1");

This is the error I am getting.

Error Details:

Array
(
    [callback] => Array
        (
            [0] => CRM_Core_Error
            [1] => handle
        )

    [code] => -2
    [message] => DB Error: syntax error
    [mode] => 16
    [debug_info] => SELECT value, label FROM {civicrm_option_value} WHERE option_group_id=(SELECT id FROM {civicrm_option_group} WHERE name='activity_type') AND is_active=1 [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 '} WHERE option_group_id=(SELECT id FROM {civicrm_option_group} WHERE name='activ' at line 1]
    [type] => DB_Error
    [user_info] => SELECT value, label FROM {civicrm_option_value} WHERE option_group_id=(SELECT id FROM {civicrm_option_group} WHERE name='activity_type') AND is_active=1 [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 '} WHERE option_group_id=(SELECT id FROM {civicrm_option_group} WHERE name='activ' at line 1]
    [to_string] => [db_error: message="DB Error: syntax error" code=-2 mode=callback callback=CRM_Core_Error::handle prefix="" info="SELECT value, label FROM {civicrm_option_value} WHERE option_group_id=(SELECT id FROM {civicrm_option_group} WHERE name='activity_type') AND is_active=1 [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 '} WHERE option_group_id=(SELECT id FROM {civicrm_option_group} WHERE name='activ' at line 1]"]
)
⋅ Categories: Drupal 6.x

Comments

nevets’s picture

nevets commented

I have no idea what crmax_query is, but the error suggests it does not handle '{' and '}' (that's a feature of db_query), so try removing them. It is also possible the sub query is a problem, in the case rewriting the query as 

SELECT ov.value, ov label FROM {civicrm_option_value} ov JOIN {civicrm_option_group} og ON (ov.option_group_id = og.id) WHERE og.name='activity_type' AND ov.filter=0 and ov.is_active=1

may help.

8ballsaysiwin’s picture

8ballsaysiwin commented

Thanks for your suggestions.

I just tried both of those options, to no avail. The first (getting rid of the curly brackets) broke the page and the second (replacing the query) returned the same error. I also tried replacing crmax_query with db-query and that also broke the page.

Any other thoughts?

jaypan’s picture

jaypan
Location Victoria, BC
commented

The syntax looks fine to me. It could possibly be that your second 'and' isn't capitalized, and the function requires it to be, but I don't know.

If you use db_query, you should get an error message. What is it?

Contact me to contract me for D7 -> D10/11 migrations.
Or anything really. I'm friendly, and open to work or conversation.