Drupal MySQL multiple tables, multiple conditions return table column data that match keyword
How do I create a query that selects or joins multiple table columns and returns that only match the keyworkd condition.
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 following 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();
$keyword
can be anything. So if $keyword
there were to be "land" I only need the result "Land free and" to be returned from the table field_description
, but I get both the field_title value and the field_description value regardless of my $keyword
mapping on the same 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 SELECTs
So my desired output would be
If I have a keyword like welcome
then ouput should be
[field_title] => WELCOME TO THE USA (field_title_table)
[field_description]=> '' (field_description_table)
If I have a keyword like land
then ouput should be
[field_title] => '' (field_title_table)
[field_description]=> 'Land of the free and' (field_description_table)
If I have a keyword like the
then ouput should be
[field_title] => WELCOME TO the USA (field_title_table)
[field_description]=> 'Land of the free and' (field_description_table)
source to share
I think the query you want is:
SELECT A.filed_title,B.field_description
FROM field_title_table A,field_description_table B
WHERE A.entityid =B.entityid
AND (A.filed_title LIKE '%your_keyword%' OR B.field_description LIKE '%your_keyword%' )
I tested it and it worked
I think I got what I expect, well you can't fulfill the condition in the query, so you have to do it with a stored procedure where you run your query and filter the result.
Well there is a way to use php to filter your result
<?php
// your query
$key="/".$keyword."/";
foreach($result AS $row){
if(preg_match($key,$row['field_title']))
{
echo 'field title =>' .$row['field_title'];
echo 'filed_description => \'\' ';
}else{
echo 'field title => \'\' ';
echo 'filed_description => '.$row['field_description'];
}
}
?>
source to share