Re: [請益] mysql搜尋問題
※ 引述《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
推
03/30 00:57, , 1F
03/30 00:57, 1F
推
03/30 11:36, , 2F
03/30 11:36, 2F
推
03/30 12:39, , 3F
03/30 12:39, 3F
推
03/30 15:24, , 4F
03/30 15:24, 4F
→
03/30 16:09, , 5F
03/30 16:09, 5F
→
03/30 16:31, , 6F
03/30 16:31, 6F
→
03/30 16:32, , 7F
03/30 16:32, 7F
→
03/30 16:40, , 8F
03/30 16:40, 8F
推
03/31 09:34, , 9F
03/31 09:34, 9F
推
04/01 08:57, , 10F
04/01 08:57, 10F
→
04/01 08:57, , 11F
04/01 08:57, 11F
推
04/01 08:59, , 12F
04/01 08:59, 12F
→
04/01 09:00, , 13F
04/01 09:00, 13F
→
04/01 09:01, , 14F
04/01 09:01, 14F
討論串 (同標題文章)