SQL parameterized query with LIKE '% ? %' PHP

Question

I have a Search function in php and have created it using a parameterized query to make it secure.

$words = $_POST['words']//words is the form that has the words submitted by the user 
$array = explode(',', $words);
$con = mysqli_connect("localhost","user","pass","database");

$stmt = $con->prepare(" SELECT column_name FROM table WHERE column_name LIKE ?")
foreach($array as $key) { //searches each word and displays results   
  $stmt->bind_param('s', $key)
  $stmt->execute();
  $result = $stmt->get-result();

  while($row = $result->fetch_assoc(){
    echo $row["column_name"]
  }
}

however I want $stmt statement to be

  $stmt = $con->prepare(" SELECT column_name FROM table WHERE column_name LIKE '%?%' ")

otherwise people have to type in the entire value of column_name to find it.

Answer 1

You can use CONCAT(), like this:

LIKE CONCAT ('%', ?, '%')