1

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();
?>
Improve this question
16
  • 1
    Are you actually doing a POST somewhere in your android code? Commented Feb 5, 2019 at 15:46
  • @user3783243 can you explain more? you mean to have this statement before? bind_param('keyword', ?);. i used post method in another case and it gets the value. Commented Feb 5, 2019 at 16:06
  • 1
    $keyword = "%{$_POST['keyword']}%"; $pdoStmt->bindParam(':keyword', $keyword, PDO::PARAM_STR); or $pdoStmt->bindParam(1, $keyword,PDO::PARAM_STR); when using SQL name LIKE ? Commented Feb 5, 2019 at 16:20
  • 1
    or $mysli_stmt->bind_param('s', $keyword) Commented Feb 5, 2019 at 16:22
  • 1
    You forgot to execute the statement. before get_result. $stmt->execute(); Commented Feb 6, 2019 at 10:03

2 Answers 2

Reset to default
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();
Improve this answer
Sign up to request clarification or add additional context in comments.

9 Comments

$keyword = '%' . $_POST['keyword'] . '%'; stmt->bind_param("s",$keyword) $sql = "SELECT * FROM items WHERE name LIKE CONCAT ?"; but it my app still stops.
What is stmt? You should really use error reporting. Cut the java/android out of the question and just get the PHP working first. Inside the getting the PHP working get the mysqli working. These all require different error reporting.
I didn't understood what I have to do with the binding. I use mysqli
1. Use php.net/manual/en/mysqli.error.php for error reporting. 2. Prepare your query first, 3. bind 4. execute 5. fetch... Without the actual PHP execution there's no way for us to know how you run the query.
@JonAte Yes, only 1 fetch. See update. Also your app wasn't crashing previously? Really you should narrow down the issue. Stop using the app and confirm it is the PHP failing.
|
0

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%)";
Improve this answer

1 Comment

By applying this you can equalizing the case of query with the db entry like if someone write "MilK" in search box, strtoupper will convert it as "MILK" and store it in variable $keyword, then you have applied query upper(name) in db by which entries like milk,miLK,Milk etc will be converted in "MILK" and you will get a match a perfect one.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.