duanlv5084 2014-08-20 18:53
浏览 60
已采纳

将两行合并为一个字符串

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条回答 默认 最新

  • doubei3312 2014-08-20 18:59
    关注

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

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)

报告相同问题?

悬赏问题

  • ¥100 连续两帧图像高速减法
  • ¥15 组策略中的计算机配置策略无法下发
  • ¥15 如何绘制动力学系统的相图
  • ¥15 对接wps接口实现获取元数据
  • ¥20 给自己本科IT专业毕业的妹m找个实习工作
  • ¥15 用友U8:向一个无法连接的网络尝试了一个套接字操作,如何解决?
  • ¥30 我的代码按理说完成了模型的搭建、训练、验证测试等工作(标签-网络|关键词-变化检测)
  • ¥50 mac mini外接显示器 画质字体模糊
  • ¥15 TLS1.2协议通信解密
  • ¥40 图书信息管理系统程序编写