mysql query to drupal query

Question

Please can anyone convert this query into drupal db_select

    $query="SELECT DISTINCT 'supplier_id','supplier_name','supplier_address','supplier_city', 'supplier_pincode', 'supplier_state', 'supplier_country' FROM pmai_supplier_directory WHERE ";
foreach($list as $k=>$value)
{
    $query.=" supplier_interest LIKE '%".$value."%' OR";
}
$query=substr($query,0,strlen($query)-2);
echo $query.=" ORDER BY supplier_name";

Thank you

regilero · Accepted Answer · 2015-04-02 14:48:21Z

4

So the query is :

SELECT DISTINCT 'supplier_id','supplier_name','supplier_address','supplier_city',
   'supplier_pincode', 'supplier_state', 'supplier_country' 
FROM pmai_supplier_directory 
WHERE supplier_interest LIKE '%foo%' 
   OR supplier_interest LIKE '%bar%'
   OR supplier_interest LIKE '%baz%'
ORDER BY supplier_name";

This could be done that way:

$list = array('foo','bar','baz');
$query = db_select('pmai_supplier_directory','pmsp')

  ->fields('pmsp',array(
    'supplier_id',
    'supplier_name',
    'supplier_address',
    'supplier_city',
    'supplier_pincode',
    'supplier_state',
    'supplier_country'
  ))
  ->orderBy('supplier_name')
  ->distinct();

  $orCond = db_or();$elt = 0;
  foreach($list as $k => $value) {
    $orCond->condition('pmsp.supplier_interest','%'.$value.'%','LIKE');
    $elt++;
  }
  if ($elt) {
    $query->condition($orCond);
  }

Then you just have to execute and fetch results.

edited Apr 2, 2015 at 14:48

answered Jul 26, 2012 at 12:05

regilero

30.7k6 gold badges65 silver badges101 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

zwirbeltier Over a year ago

isn't DISTINCT missing?

Collectives™ on Stack Overflow

mysql query to drupal query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related