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();

}
2
  • 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. Dec 7, 2012 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, 2012 at 17:06

1 Answer 1

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();

}
1
  • 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, 2013 at 11:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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