Implement Like Query in Pdo

implement LIKE query in PDO

You have to include the % signs in the $params, not in the query:

$query = "SELECT * FROM tbl WHERE address LIKE ? OR address LIKE ?";
$params = array("%$var1%", "%$var2%");
$stmt = $handle->prepare($query);
$stmt->execute($params);

If you'd look at the generated query in your previous code, you'd see something like SELECT * FROM tbl WHERE address LIKE '%"foo"%' OR address LIKE '%"bar"%', because the prepared statement is quoting your values inside of an already quoted string.

LIKE query in PDO not working

The statement should be prepared

if($_POST['searchFilter']){
    $searchFilter = $_POST['searchFilter'];
    echo $searchFilter; 
    try {
        $conn = new PDO('mysql:host=localhost;dbname=houserentsystem;charset=utf8', 'root', 'admin');
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $stmt = $conn->prepare("SELECT roomName FROM roomnames WHERE roomName LIKE ?");
        $stmt->execute(array('%'.$searchFilter.'%')); 
        $results = $stmt->fetchAll(PDO::FETCH_ASSOC);
        //print_r($results);
        echo json_encode($result);
    } catch(PDOException $e) {
        echo 'ERROR: ' . $e->getMessage();
    }
}

How do I create a PDO parameterized query with a LIKE statement?

Figured it out right after I posted:

$query = $database->prepare('SELECT * FROM table WHERE column LIKE ?');
$query->execute(array('value%'));

while ($results = $query->fetch())
{
    echo $results['column'];
}

LIKE query not working PDO

Your SQL-Syntax is wrong, not sure what you want to obtain, but correct would be:

SELECT * FROM hotels WHERE h_country LIKE '%' || :c || '%';


--OR

SELECT * FROM hotels WHERE h_country  = :c  AND <your_column_here> LIKE '%m%';

How to use bindValue with LIKE operator in SQL query?

You probably want :

$query = "SELECT * 
          FROM books
          WHERE title LIKE CONCAT( '%', :title, '%')";
...
...
statement->bindValue(':title', $title, PDO::PARAM_STR);

The bind parameter should be used as a litteral string. CONCAT can be used to concatenate the parameter with percent signs on both ends.

Using LIKE in bindParam for a MySQL PDO Query

No, you don't need the inner single quotes so just $term = "$term%";

The statement you're running now would try to match 'a%' instead of a%

bindParam will make sure that all string data is automatically properly quoted when given to the SQL statement.

MySql search using LIKE clause not working

Since you are using prepared statements, you don't need the single quotes around your expression. Change your code, removing those quotes, to

$query="SELECT * FROM posts WHERE title LIKE ?";
$keywordArray[0]="%".$keywordArray[0]."%";
for($i=1;$i<$n;$i++){
  $keywordArray[$i]="%".$keywordArray[$i]."%";
  $query.=" OR title LIKE ?";
}

It was treating the quote marks as being part of the value inside the parameter. So you would have ended up with SQL something like

SELECT * FROM posts WHERE title LIKE '\'%Something%\''

and clearly this won't match, because the values in the database won't have single quotes at the start and end in most cases.

With the changes, it should translate into SQL like this

SELECT * FROM posts WHERE title LIKE '%Something%'

This is because the parameterisation process handles the quoting and escaping job automatically for you - it's one way in which it protects against SQL injection attacks ( and also, incidentally, against syntax errors caused by erroneous / unescaped quote marks).

---

P.S. If a request is ever submitted to this code where no keyword at all was provided, then the code will crash because it assumes there is always a value in $keywordArray[0]. Consider revising this to either validate that a keyword was provided, or just loop the whole array and, if no keywords are submitted, simply don't add a WHERE clause to the query at all.

---

Related Topics