Drupal MySQL multiple tables, multiple conditions return table column data which matches a keyword

Viewed 62

How to create a query that selects or joins multiple table and returns column that only match the condition keyworkd.

I have table1 'field_title_table'

entityid | field_title     | entitytype
---------------------------------------        
413      | WELCOME TO the USA  |   page_hub

table2 'field_description_table'

entityid | field_description     | entitytype
---------------------------------------------        
413      | Land of the free and  |   page_hub

I tried below steps

$query = db_select('field_title_table', 'wb');
$query->join('field_description_table','sd','sd.entityid = wb.entityid');
$query->fields('wb',array('field_title'));
$query->fields('sb',array('field_description'));

 $db_or = db_or();              
 $db_or->condition('wb.field_title,'%'.$keyword.'%','LIKE');
 $db_or->condition('sb.field_description,'%'.$keyword.'%','LIKE');
 $query->condition($db_or);
 $result = $query->execute()->fetchAll();

The $keyword can be anything. So if $keyword were to be 'land', I wanted only the result 'Land of the free and ' to be returned from the field_description table, but I get both field_title value and field_description value regardless of my $keyword only matching on one table.

So basically I want this

SELECT field_title from field_title_table WHERE field_title LIKE '%$keyword%'

SELECT field_description from field_description_table WHERE field_description LIKE '%$keyword%'

without having multiple SELECT's

So my desired output would be

if I have the keyword as welcome then ouput should be

[field_title] => WELCOME TO THE USA (field_title_table)
[field_description]=> '' (field_description_table)

if I have the keyword as land then ouput should be

[field_title] => '' (field_title_table)
[field_description]=> 'Land of the free and' (field_description_table)

if I have the keyword as the then ouput should be

[field_title] => WELCOME TO the USA (field_title_table)
[field_description]=> 'Land of the free and' (field_description_table)
1 Answers
Related