Error - probably a simple solution....

Tried using AjaxSearch and receive the following error:

« MODx Parse Error »
MODx encountered the following error while attempting to parse the requested resource:
« Execution of a query to the database failed - 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 ’( DISTINCT CAST(ntv.id AS CHAR) SEPARATOR "," ) AS tv_id, GROUP »
SQL: SELECT sc.id, sc.pagetitle, sc.longtitle, sc.description, sc.alias, sc.introtext, sc.menutitle, sc.content, sc.publishedon, GROUP_CONCAT( DISTINCT CAST(ntv.id AS CHAR) SEPARATOR "," ) AS tv_id, GROUP_CONCAT( DISTINCT ntv.value SEPARATOR ", " ) AS tv_value FROM `modx_site_content` sc LEFT JOIN( SELECT DISTINCT tv.id, tv.value, tv.contentid FROM `modx_site_tmplvar_contentvalues` tv WHERE (((tv.value LIKE ’%gas%’))) ) AS ntv ON sc.id = ntv.contentid WHERE ((sc.published=1) AND (sc.searchable=1) AND (sc.deleted=0) AND (sc.type=’document’) AND (sc.privateweb=0)) GROUP BY sc.id HAVING (((sc.pagetitle LIKE ’%gas%’) OR (sc.longtitle LIKE ’%gas%’) OR (sc.description LIKE ’%gas%’) OR (sc.alias LIKE ’%gas%’) OR (sc.introtext LIKE ’%gas%’) OR (sc.menutitle LIKE ’%gas%’) OR (sc.content LIKE ’%gas%’) OR (tv_value LIKE ’%gas%’))) ORDER BY sc.publishedon,sc.pagetitle

Anyone have some ideas to help?

Perhaps your version of MySQL does not support that kind of query?

Read this post : http://modxcms.com/forums/index.php/topic,31830.msg193322.html#msg193322

I am running MySQL 4.0.25 and Apache 1.3.33 Any help or ideas? Still struggling. I went away from the search to address other issues but I am back and need to resolve.

Thanks!

I believe that the GROUP_CONCAT function was not available in your version of MySQL.

GROUP_CONCAT() was added in MySQL 4.1

http://dev.mysql.com/doc/refman/4.1/en/group-by-functions.html#function_group-concat

Thank you for the info. I am not a MySQL expert by any means. If I were to update would the items built on 4.0 (I have a different site running off it) screw up or are the versions backward compatible?

To avoid the group concat with the tv_content try to add the parameter &whereSearch=`content` in your AS snippet call.

I am performing a MySQL update on my server this evening and will see how it goes. Hopefully this solves things. Will keep you posted.

Quote from: sottwell at May 13, 2009, 12:56 PM

I believe that the GROUP_CONCAT function was not available in your version of MySQL.

GROUP_CONCAT() was added in MySQL 4.1

http://dev.mysql.com/doc/refman/4.1/en/group-by-functions.html#function_group-concat

It was in fact the MySQL! Had my host do an upgrade last night and walah, the search works now! Thank you much for your help.

Quick question, is there a way to use AjaxSearch to also look inside folders that contain PDF files?