Question 42008 in PHP Question posted in
Official manual can be found here.

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

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,

Tags:

2

Answers


  1. 0

    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 '%324 West Gore Street%' 
 AND 
  mailing_address LIKE '%Orlando%' 
 AND 
  mailing_address LIKE '%FL 32806%'
    Login or Signup to reply.
  2. 0

    @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);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search