drupal 7 - Mysql like search with more than one column -
i have drupal site search option. if user enters search keyword, need compare more 1 columns , display records.
i have tried following query
$search = 'test'; $sql_query = db_select('logoinfo', 'l')->fields('l'); $or = db_or(); $or->condition('search_field', '%'.db_like($search).'%','like'); $or->condition('companyname', '%'.db_like($search).'%','like'); $sql_query->condition($or); $selectlogos = $sql_query->execute();
it displays records matching search keyword order of auto increment id asc.
but want display records first having both search_field
, companyname
matches keyword, after other records matches either companyname
or search_field
. please advise achieve this.
since orderby requires field name , can't order expression, you'll need use addexpression alias , order alias. expression in example return 0 if value not in both fields , 1 if in both fields. far know should standard sql, may vary on different database backends; expression may need adjusted depending on database using.
<?php $search = 'test'; $sql_query = db_select('logoinfo', 'l')->fields('l'); $or = db_or(); $or->condition('search_field', '%'.db_like($search).'%','like'); $or->condition('companyname', '%'.db_like($search).'%','like'); $sql_query->condition($or); $safe_search = db_like($search); $ex_alias = $sql_query->addexpression("l.search_field '%$safe_search%' , l.companyname '%$safe_search%'"); $sql_query->orderby($ex_alias, 'desc'); $selectlogos = $sql_query->execute(); ?>
Comments
Post a Comment