how to find data from database by matching single word in a string stored in table's column-CodePudding

Here is the query to find the address from a table where it matches to variable $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);

In the table vendor_info the column mailing address is like 324 WEST GORE STREETORLANDO, FL 32806

I want to get result if any single word is match from mailing_address column

now the result of dd(company); is empty array,

kindly help me out to figure out this solution, it is in symfony,

CodePudding user response：

Maybe like this:

   $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";
   ......

Result:

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

CodePudding user response：

@mscdeveloper's answer is basicly correct - but as you use doctrine you should also use prepared statements to be safe(r) from sql injections.

$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);