php 如何通过匹配表列中存储字符串中的单个单词来从数据库中查找数据

hrirmatl  于 2022-10-30  发布在  PHP
关注(0)|答案(2)|浏览(97)

下面的查询将从表中查找与变量$fromCity匹配的地址,

$fromCity= "324 West Gore Street, Orlando, FL 32806, USA";
$sql = "SELECT * FROM vendor_info WHERE mailing_address LIKE '$fromCity'";
$em = $this->getDoctrine()->getManager();
$stmt = $em->getConnection()->prepare($sql);
$stmt->execute();
$company = $stmt->fetchAll();
dd($company);

在表vendor_info中,列mailing address类似于324 WEST GORE STREETORLANDO, FL 32806
如果mailing_address列中有任何单个单词匹配,我希望得到结果
现在dd(company);的结果是空数组,
请帮助我找出这个解决方案,它是在symfony,

20jt8wwn

20jt8wwn1#

也许是这样的:

$fromCity = "324 West Gore Street, Orlando, FL 32806";
   $fromCity_arr = explode(', ', $fromCity);
   $whereStr = '';
   foreach($fromCity_arr as $fromCity_el){
     if(!empty($whereStr)){$whereStr .= " AND ";}
     $whereStr .= "mailing_address LIKE '%".$fromCity_el."%'";
   }
   $sql = "SELECT * FROM vendor_info WHERE $whereStr";
   ......

结果:

SELECT 
 * 
FROM 
  vendor_info 
WHERE 
  mailing_address LIKE '%324 West Gore Street%' 
 AND 
  mailing_address LIKE '%Orlando%' 
 AND 
  mailing_address LIKE '%FL 32806%'
5jdjgkvh

5jdjgkvh2#

@mscdeveloper的答案基本上是正确的--但是当你使用doctrine的时候,你也应该使用prepared语句来避免sql注入。

$fromCity = "324 West Gore Street, Orlando, FL 32806";
$fromCity_arr = explode(', ', $fromCity);
$stm = $this->getEntityManager()->getRepository(MyModel::class)->getQueryBuilder('a');
for($i=0;$i<count($fromCity_arr);$i++) {
    $stm->andWhere('a.mailing_address like ?)
        ->setParameter($i+1, '%'.$fromCity_arr[$i].'%'); 
}

$result = $stm->getQuery()->getResult();
dump($stm->getQuery()->getSql());
dd($result);

相关问题