1

I'm trying to change the following query to a drupal db_query:

SELECT first_name, SUBSTR(first_name,1,3) AS firstname_substr FROM civicrm_contact WHERE id != 'NULL' ;

I have tried this, but its not working:

$firstname_substr = $query->addExpression('SUBSTRING(cc.first_name,1,3)','cc.firstname_substr');

Below is the function I am trying to add it to:

function search_pcp($name, $from = 0, $count = 5){

  $query = db_select('civicrm_pcp', 'pcp');
  $query -> join('civicrm_contribution_page','cp', 'pcp.page_id = cp.id');
  $query -> join('civicrm_contact','cc','pcp.contact_id = cc.id');
  $query -> addJoin('LEFT OUTER','civicrm_entity_file','cef', 'cef.entity_id = pcp.id');
  $query -> addJoin('LEFT OUTER','civicrm_file','cf', 'cef.file_id = cf.id');
  $query -> condition('pcp.is_active','1','=');
  $query -> condition('cp.is_active','1','=');  

  $db_or = db_or();
  $db_or->condition('cc.first_name',  $name, 'like');
  $db_or->condition('cc.last_name',  $name, 'like');

  **//$db_or->condition($firstname_substr, $name);**

  $query -> condition($db_or);

  return $query->execute();

}
  • What is the error you are getting? Also, where are you placing that code into the function? You might complete the serach_pcp function showing where you have tried to put the code. – Johnathan Elmore Dec 7 '12 at 16:49
  • I put $firstname_substr just above line: $db_or = db_or(); Then I added this line $db_or->condition($firstname_substr, $name); just below $db_or->condition('cc.last_name', $name, 'like'); Sorry I'm a complete newbie!!! have been trying to figure it out all day – kab22 Dec 7 '12 at 17:06
2

I believe you need to change

$firstname_substr = $query->addExpression('SUBSTRING(cc.first_name,1,3)','cc.firstname_substr');

to

$query->addExpression('SUBSTRING(cc.first_name,1,3)','firstname_substr');

and

$db_or->condition($firstname_substr, $name);

to

$db_or->condition('firstname_substr', '%'. db_like($name) . '%', 'like');

So it will look like this:

function search_pcp($name, $from = 0, $count = 5){

  $query = db_select('civicrm_pcp', 'pcp');
  $query->join('civicrm_contribution_page','cp', 'pcp.page_id = cp.id');
  $query->join('civicrm_contact','cc','pcp.contact_id = cc.id');
  $query->addJoin('LEFT OUTER','civicrm_entity_file','cef', 'cef.entity_id = pcp.id');
  $query->addJoin('LEFT OUTER','civicrm_file','cf', 'cef.file_id = cf.id');
  $query->condition('pcp.is_active','1','=');
  $query->condition('cp.is_active','1','=');

  // changed cc.firstname_substr to just firstname_substr
  $query->addExpression('SUBSTRING(cc.first_name,1,3)','firstname_substr');

  $db_or = db_or();
  $db_or->condition('cc.first_name',  $name, 'like');
  $db_or->condition('cc.last_name',  $name, 'like');

  // corrected the condition statement
  $db_or->condition('firstname_substr', '%' . db_like($name) . '%', 'like');

  $query -> condition($db_or);

  return $query->execute();

}
| improve this answer | |
  • Thanks alot, I actually didnt even have to use a substring. all I required was your condition above - $db_or->condition('firstname_substr', '%' . db_like($name) . '%', 'like'); replacing the 'firstname_substr' with cc.first_name. That allowed me to search using any amount of letters, if that makes sense. Thanks Alot!!! – kab22 Jan 7 '13 at 11:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.