Re: [請益] mysql搜尋問題

[weiclin (CC)]

※ 引述《villix (瓜子被蜀國的狗吃了)》之銘言：
: 不好意思請問一下,如果現在有兩張table, table1有需要搜尋的字串,
: table2裡面是有句子的字串,可以將table1的所有字串搜尋出來當作array
: 然後在table2裡面搜尋有table1字串的句子嗎?希望是可以將table1的所有
: 字串在table2裡面做fulltext search 然後做count這樣~~

大概是這樣?
<?php

try {
    $pdo = new PDO('sqlite::memory:');
    $pdo->exec("CREATE TABLE 'keywords' ('keyword' TEXT);");
    $pdo->exec("INSERT INTO 'keywords' ('keyword') VALUES ('google'),('bing'),('yahoo');");

    $pdo->exec("CREATE TABLE 'messages' ('message' TEXT);");
    $pdo->exec("INSERT INTO 'messages' ('message') VALUES ('how about google.com?'),('why not bing?'),('is yahoo good?');");
    $pdo->exec("INSERT INTO 'messages' ('message') VALUES ('Captain America'),('Gundam Mark II'),('google AlphaGO');");

    $result = $pdo->query("SELECT M.*, K.* FROM messages M
        INNER JOIN (SELECT keyword FROM keywords) K
        ON M.message LIKE '%' || K.keyword || '%'");

    if ($result) {
        foreach($result as $row) {
            echo "found keyword [{$row['keyword']}] in message [{$row['message']}]\n";
        }
    }

    $result = $pdo->query("SELECT COUNT(M.message) AS cnt, E.keyword AS keyword FROM messages M
        INNER JOIN (SELECT keyword FROM keywords) E
        ON M.message LIKE '%' || E.keyword || '%' GROUP BY E.keyword");

    if ($result) {
        foreach($result as $row) {
            echo "the keyword [{$row['keyword']}] was founded in {$row['cnt']} messages\n";
        }
    }

} catch (PDOException $e) {
    echo "Error!: {$e->getMessage()}\n";
}



執行結果:
$ php inner_join.php
found keyword [google] in message [how about google.com?]
found keyword [bing] in message [why not bing?]
found keyword [yahoo] in message [is yahoo good?]
found keyword [google] in message [google AlphaGO]
the keyword [bing] was founded in 1 messages
the keyword [google] was founded in 2 messages
the keyword [yahoo] was founded in 1 messages

--
※ 發信站: 批踢踢實業坊(ptt.cc), 來自: 210.68.230.200
※ 文章網址: https://www.ptt.cc/bbs/PHP/M.1459232693.A.6A3.html
推 shadowjohn: 寫的不錯
推 liaosankai: 感謝，學到新 LIKE 寫法
推 villix: 看起來很不錯～感謝大大=w=
推 shadowjohn: 不過like的部分應該用prepare-s... like ?
→ weiclin: 我認為用不到 prepare, 也沒辦法使用 prepare
→ MOONRAKER: http://goo.gl/bn4p7q 這不就用了 作法相通
→ MOONRAKER: http://goo.gl/bGSoz0 要pdo也有pdo的
→ weiclin: 你看看這篇,理由差不多 http://goo.gl/6c8VpA
推 shadowjohn: 嗯嗯，查sql自身欄位可以不用用，也不需要用的:D
推 GALINE: 這招有點帥，不過關鍵字或 message 的時候感覺 full table
→ GALINE: scan 會有點苦。資料不大或是線下作業比較適合這樣搞
推 GALINE: 「關鍵字或 message 多的時候」
→ GALINE: 如果是線上服務需要搜尋，建議還是弄個搜尋引擎來
→ GALINE: 像是 Solr 或 Elasticsearch....