CODEWITHSUNDEEP
mysqldatabasepdoprepared-statementbindparam
Jobbe Maas
Jobbe Maas

Reputation: 3

PHP PDO bindParam doesn't modify the query

I have the following code:

if(isset($_GET['q'])){
    $sql = "SELECT klantnr, persnr, naam, voornaam FROM gegevens WHERE voornaam LIKE % :voorwaarde % OR naam LIKE % :voorwaarde %";

    $stmt = $db->prepare($sql);
    $stmt->bindParam(':voorwaarde', $_GET['q']);
    $stmt->bindParam(':voorwaarde', $_GET['q']);
    echo $stmt->queryString;
    $stmt->execute();
    $pers = $stmt->fetchAll();
    print_r($pers);
}

But the query that shows up from the queryString function is still:

SELECT klantnr, persnr, naam, voornaam FROM gegevens WHERE voornaam LIKE % :voorwaarde % OR naam LIKE % :voorwaarde %

Upvotes: 0

Views: 224

Answers (2)

N.B.
N.B.

Reputation: 14071

If you want to use a LIKE search with PDO, you must be a little bit witty. What PDO does is that it quotes strings. What does that mean for you? It means that your % :term % won't be a valid SQL.

To get the proper SQL out and to correctly clean the input string, use the following:

$sql = "SELECT klantnr, persnr, naam, voornaam FROM gegevens WHERE voornaam LIKE CONCAT('%', :voorwaarde, '%') OR naam LIKE CONCAT('%',  :voorwaarde,  '%')";

What the above does is concatenating the wildcards for LIKE search to your, now clean, search string.

Upvotes: 0

Jens
Jens

Reputation: 69440

You are working with prepared statements. That means that the query has placeholder wich will replaced by the database. This will not modify your query string.

use $stmt->debugDumpParams(); to see wich parameter are binded.

Upvotes: 1

Related Questions