用2个条件搜索字词PHP PDO

Question

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

例如，搜索引擎将搜索的文章和仅显示是否存在的股或更多2个代号制品内（并且如果该制品具有仅一个股票那么它也将显示它）

我我很想知道是否有人知道如何去做。在此先感谢

用户号码1的股票列表是'aapl：我们，间谍：我们，intc：我们，goog：我们'然后只有超过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) 

脚本生成查询

[email protected]:/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; 
?> 

输出

[email protected]:/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