i have dynamic database, and i want to search in this table
FieldID | Content | TypeID
--------------------------
ABC-123 | jon | 1
EFG-456 | doe | 1
HIJ-789 | man | 1
So my SELECT query looks something like this:
SELECT
GROUP_CONCAT(fieldsContent.Content SEPARATOR '|*|') AS Content,
GROUP_CONCAT(fieldsContent.FieldID SEPARATOR '|*|') AS FieldID,
FROM (`customers`)
LEFT OUTER JOIN `fieldsContent` ON `fieldsContent`.`TypeID` = `customers`.`ID`
GROUP BY `fieldsContent`.`TypeID`
ORDER BY `customers`.`Created` DESC
And the result looks like this
Array
(
[0] => stdClass Object
(
[Content] => jon|*|doe|*|man
[FieldID] => ABC-123|*|EFG-456|*|HIJ-789
)
)
And when i'm adding HAVING and searching only for jon, it will return me the result
HAVING Content LIKE "%jon%"
But, when i'm trying to search jon doe it will return empty result
HAVING Content LIKE "%jon doe%"
Because jon doe don't exists in the string, only jon|*|doe
So how can i combine these two rows to one string without the SEPARATOR for me the search in them jon doe ?
BUT!! keep in mind that i need to obtain the SEPARATOR because i need to combine the data to be used in php.
ex:
$field = explode('|*|',$data->FieldID);
$content = explode('|*|',$data->Content);
foreach($field as $k => $FieldID){
switch($FieldID){
case 'ABC-123':
$res['first_name'] = $content[$k];
break;
case 'EFG-456':
$res['last_name'] = $content[$k];
break;
case 'HIJ-789':
$res['gender'] = $content[$k];
break;
}
}
Any ideas will be appreciated :)
