1

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 :)

2
  • Is having a space as separator instead of |*| an option? Commented Aug 20, 2014 at 18:56
  • @EndeNeu No because in the data can be space and it will mess up everything Commented Aug 20, 2014 at 18:58

2 Answers 2

7

TRY THIS #1

HAVING Content LIKE "%jon%doe%"

TRY THIS #2

HAVING Content LIKE "%jon" AND Content LIKE "%doe%"

TRY THIS #3

HAVING REPLACE(Content,'|*|',' ') LIKE "%jon doe%"

GIVE IT A TRY !!! :-)

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

2 Comments

I almost down voted this as it didn't have GIVE IT A TRY!! at the bottom
The first one suited the best. Thanks for the simple answer :)
1

Use:-

HAVING Content LIKE "%jon%" AND Content LIKE "%doe%"

3 Comments

see the answer above, HAVING Content LIKE "%jon%doe%" suited for me the best
HAVING Content LIKE "%jon%doe%" assumes that doe would always follow joe. My answer has no assumptions regarding at what place jon or doe comes
Thats another good point of view, there is no such thing a bad advice. Thanks !

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.