CiviCRM Community Forums (archive)

I have some SQL code that I'd like to make in to a custom search. The SQL has some temporary tables and MySQL defined variables. These components of the code are necessary due to the complexity of the search. I can get a (simpler) custom search to work that just contains one SQL query. In trying to get the complicated search to work (that contains more than one query), I've tried putting multiple queries in the return value for the "all" function (which does indeed generate the SQL that I'm looking for), but I get an error. Is there another place that I can insert some SQL to run before the main query, but in such a way that it runs with the same MySQL session as the main query (so that I can initialize temporary tables and variables)?

For example:

I can get a query like this to work:
"select sort_name from civicrm_contact where id = 3"

but I can't get one like this to work:
"set @foo = 3; select sort_name from civicrm_contact where id = @foo"

Yes! Thanks, that works wonderfully. (The syntax requires two colons though).

Fore example, in the "all" function, I put the following code:
CRM_Core_DAO::executeQuery( "set @foo=3" );