php - Why is only the first word in records being matched?

Question

I am using the following code snippets to search multiple columns of a database:

PHP:

if (!(isset($_GET['pagenum']))) {
      $pagenum = 1;
    } else {
      $pagenum = $_GET['pagenum'];
    }
    $page_limit = ($_GET["show"] <> "" && is_numeric($_GET["show"]) ) ? $_GET["show"] : 8;
    
    try {
      $keyword = trim($_GET["keyword"]);
      if ($keyword <> "" ) {
        $sql = "SELECT * FROM tbl_contacts WHERE 1 AND "
                . " (first_name LIKE :keyword OR middle_name LIKE :keyword OR last_name LIKE :keyword OR job_title LIKE :keyword OR organization LIKE :keyword OR address LIKE :keyword OR notes LIKE :keyword) ORDER BY first_name ";
        $stmt = $DB->prepare($sql);
        
        $stmt->bindValue(":keyword", $keyword."%");
      }else {
        $sql = "SELECT * FROM tbl_contacts WHERE 1 ORDER BY first_name ";
        $stmt = $DB->prepare($sql);
      }
    
      $stmt->execute();
      $total_count = count($stmt->fetchAll());  
    
      $last = ceil($total_count / $page_limit);
    
      if ($pagenum < 1) {
        $pagenum = 1;
      } elseif ($pagenum > $last) {
        $pagenum = $last;
      }
    
      $lower_limit = ($pagenum - 1) * $page_limit;
      $lower_limit = ($lower_limit < 0) ? 0 : $lower_limit;
    
    
      $sql2 = $sql . " limit " . ($lower_limit) . " ,  " . ($page_limit) . " ";
      
      $stmt = $DB->prepare($sql2);
      
      if ($keyword <> "" ) {
        $stmt->bindValue(":keyword", $keyword."%");
       }
       
      $stmt->execute();
      $results = $stmt->fetchAll();
    } catch (Exception $ex) {
      echo $ex->getMessage();
}

HTML:

<div class="col-lg-12" style="padding-left: 0; padding-right: 0;" >
    <form action="index.php" method="get" >
    <div class="col-lg-6 pull-left form-group has-feedback has-clear" style="padding-left: 0;"  >
      <span class="pull-left">  
        <label class="col-lg-12 control-label" for="keyword" style="padding-right: 0;">
          <input type="text" value="<?php echo $_GET["keyword"]; ?>" placeholder="name or job title" id="" class="form-control" name="keyword" style="height: 41px;">
          <span class="form-control-clear glyphicon glyphicon-remove form-control-feedback hidden"></span>
        </label>
        </span>
      <button id="searchBut" class="btn btn-info">search</button>
    </div>
    </form>
    <div class="pull-right" ><a href="contacts.php"><button class="btn btn-success"><span class="glyphicon glyphicon-user"></span> Add New Contact</button></a></div>
  </div>

The problem that I encounter is that the only search results that I get are if the keyword that I used happens to also be the first word of a record. In other words, only the first words in records are being matched. For example, one of the columns of the database is named 'address' and it contains customers' addresses. So if one of the fields in that column contains 2525 Main Street, if I search for 2525, I will get a hit/result. But if I search for main or street, I will not get a hit.

So what is the correct code to perform keyword searches on multiple columns that will return results if the keyword is present regardless of its position in the field? So if I search for main or street, that record is returned.

question from:https://stackoverflow.com/questions/65929469/why-is-only-the-first-word-in-records-being-matched

Answer 1

On this line:

$stmt->bindValue(":keyword", $keyword."%");

Try to change it to:

$stmt->bindValue(":keyword", "%".$keyword."%");

This way you are using a wildcard for both ends of your keyword string and not only at the end. Since you are only applying the wildcard at the end, records are only being matched if your keyword is at the beginning of a record's string/value.