SQL in php, make partial search using LIKE

Question

I am trying to make SQL in php to return all the entries that matches a keyword that is entered by the user (from search bar). I want to return all the entries that their name "partial" matches with the keyword. I want at least to match the keyword, if an entry name in database before has space and after maybe another letter/space. For example I have three entries with names "Milk", "Semi skimmed Milk" and "Full Milk 2". If the keyword is "Milk" or "milk" or "MiLK", I want to get all these three entries. The only case I am thinking it might be the problem is case sensitive. I tried with a keyword that exists exactly in database, but my app (on android) stops .

Based on user3783243 answer.

PHP FILE

<?php
$servername = "";
$username = "";
$password = "";
$dbname = "";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

$sql = "SELECT name FROM items WHERE name LIKE CONCAT ('%', ?, '%')";
$stmt = $conn->prepare($sql);
$stmt->bind_param("s", $keyword);
$res = $stmt->get_result();
while($row = $res->fetch_assoc()) {
     echo $row["name"] . ",";
}

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
        echo $row["name"] . ",";
    }
} else {
    echo "0";
}
$conn->close();
?>

Answer 1

Your query should be:

$sql = "SELECT * FROM items WHERE name LIKE CONCAT ('%', ?, '%')";

and then $keyword should be bound with whatever syntax the driver you are using supports.

As is your query would have been:

SELECT * FROM items WHERE name LIKE CONCAT ('%', Milk, '%')

and you wanted Milk to be a string so it needed to be quoted. As is mysql would have thought that was a column.

Alternatively you could do:

$keyword = '%' . $_POST['keyword'] . '%';
$sql = "SELECT * FROM items WHERE name LIKE CONCAT ?";

that is the same and still requires the binding though.

The binding also takes away the SQL injection. See How can I prevent SQL injection in PHP? and/or https://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet#Defense_Option_1:_Prepared_Statements_.28with_Parameterized_Queries.29

Per update.. replace:

$keyword =$_POST['keyword']; 
$sql = "SELECT * FROM items WHERE name LIKE '%$keyword%)";
$result = $conn->query($sql);

with:

$sql = "SELECT name FROM items WHERE name LIKE CONCAT ('%', ?, '%')";
$stmt = $conn->prepare($sql);
$stmt->bind_param("s", $keyword);
$stmt->execute();
$res = $stmt->get_result();
if(empty($conn->errno) && !empty($res)) { 
     while($row = $res->fetch_assoc()) {
          echo $row["name"] . ",";
     }
} else {
     echo '0';
     //print_r($conn->errno);
}
$conn->close();

...

also remove

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
        echo $row["name"] . ",";
    }
} else {
    echo "0";
}
$conn->close();

Answer 2

In this case you can convert the input in search bar to either upper or lower case by default then apply query in db like

For Upper case:

$keyword =strtoupper($_POST['keyword']); 
$sql = "SELECT * FROM items WHERE upper(name) LIKE '%$keyword%)";

Or for lower case:

$keyword =strtolower($_POST['keyword']); 
$sql = "SELECT * FROM items WHERE lower(name) LIKE '%$keyword%)";