两个条件的Word搜索-- PDO

Question

我想知道是否有可能给我的网站上的搜索引擎提供2个条件。站点上的每个用户都有一个股票代码列表，目标是向他提供与每个用户拥有的股票列表相对应的文章。

例如，搜索引擎将搜索文章，只有当文章中有2个或更多的股票代码时，搜索引擎才会显示(如果文章只有一个代码，那么它也会显示)。

我很想知道有没有人知道怎么做。提前感谢

用户1的股票列表是'aapl:us，间谍:us，intc:us，goog:us'，然后只有超过2股的文章才会显示给他(如果文章中只提到一只股票，那么它也会显示给他)

 <?php
    $list = 'aapl:us,spy:us,intc:us,goog:us';
    $ticker = explode(",",$list);
    $count = count($ticker);
    $pha="";
    for($i=0;$i<$count;$i++){
    $pha.= " = ".$ticker[$i]." OR ";
    }  

$query = "SELECT * FROM table WHERE ticker ".$pha;
$result = $db->prepare($query); 
$result->execute(); 

while($row = $result->fetch(PDO::FETCH_ASSOC)) {
echo '<li><a href="category.html?i='.$row['id'].'">'.$row['name'].'</a></li>';
}

​

​

Answer 1

利用REGEXP函数可以解决您的问题

用于搜索的

从…

$list = 'aapl:us,spy:us,intc:us,goog:us';

至

$list = '(^|,)aapl:us|spy:us|intc:us|goog:us(,|$)';

# OR without modifying your list just replace comma with pipe
$list = 'aapl:us,spy:us,intc:us,goog:us';
$list = '(^|,)'.str_replace(',', '|', $list).'(,|$)'; 

和

从…

$query = "SELECT * FROM table WHERE ticker ".$pha;

至

$query = "SELECT * FROM table WHERE ticker REGEXP $list";

它将是

SELECT * FROM table WHERE ticker REGEXP (^|,)aapl:us|spy:us|intc:us|goog:us(,|$)

，用于创建特定数量的代码查询字符串，如下面所示

SELECT *,((ticker REGEXP '(^|,)aapl:us(,|$)') + (ticker REGEXP '(^|,)spy:us(,|$)') ) as sum 
FROM 
        table 
HAVING sum = 2

测试和以了解我们正在做什么

mysql> -- test variable
mysql> set @test='aapl:us,spy:us,intc:us,goog:us';
Query OK, 0 rows affected (0.00 sec)

mysql> -- variable contents
mysql> select @test;
+--------------------------------+
| @test                          |
+--------------------------------+
| aapl:us,spy:us,intc:us,goog:us |
+--------------------------------+
1 row in set (0.00 sec)

mysql> -- if you create sum it can be used in having clause
mysql> select ((@test REGEXP '(^|,)aapl:us(,|$)') + (@test REGEXP '(^|,)spy:us(,|$)') ) as sum;
+------+
| sum  |
+------+
|    2 |
+------+
1 row in set (0.00 sec)

mysql> -- for 2 ticker
mysql> select * from( select ((@test REGEXP '(^|,)aapl:us(,|$)') + (@test REGEXP '(^|,)spy:us(,|$)') ) as sum ) as q having sum =2;
+------+
| sum  |
+------+
|    2 |
+------+
1 row in set (0.00 sec)

mysql> -- for 3 or more ticker
mysql> select * from( select ((@test REGEXP '(^|,)aapl:us(,|$)') + (@test REGEXP '(^|,)spy:us(,|$)') ) as sum ) as q having sum >=3;
Empty set (0.00 sec)

生成查询的脚本

akshay@db-3325:/tmp$ cat test.php 
<?php

 $list = 'aapl:us,spy:us,intc:us,goog:us';

 $sum  = '('.implode("+", array_map(function($v){ return sprintf("(ticker REGEXP '(^|,)%s(,|$)')",$v); },explode(",",$list))).')';

 $query = "SELECT *, $sum as sum from table having sum = 2";

 print $query.PHP_EOL;
?>

输出

akshay@db-3325:/tmp$ php test.php 
SELECT *, ((ticker REGEXP '(^|,)aapl:us(,|$)')+(ticker REGEXP '(^|,)spy:us(,|$)')+(ticker REGEXP '(^|,)intc:us(,|$)')+(ticker REGEXP '(^|,)goog:us(,|$)')) as sum from table having sum = 2