In this query
select wrd from tablename WHERE wrd LIKE '$partial%'
I'm trying to bind the variable '$partial%'
with PDO. Not sure how this works with the %
at the end.
Would it be
select wrd from tablename WHERE wrd LIKE ':partial%'
where :partial
is bound to $partial="somet"
or would it be
select wrd from tablename WHERE wrd LIKE ':partial'
where :partial
is bound to $partial="somet%"
or would it be something entirely different?
Parameter binding is essential for protecting your web application from SQL-injection. Pretty much all data which is going to be used in SQL statement needs binding. Binding simply saying is just a way to tell engine that a particular piece of data is a string, number, character and so on.
Array Binding: As per our need, we simply need to bind the PHP array to IN() clause and to obtain this functionality, we first need to convert the given array to the form acceptable by the IN() clause, which is a job carried out by PHP implode() function.
PDO::PARAM_STR (int) Represents the SQL CHAR, VARCHAR, or other string data type. PDO::PARAM_STR_NATL (int) Flag to denote a string uses the national character set.
The bindValue() function binds a value to a named or question mark in the SQL statement. The bindValue() function is used to pass both value and variable.
You could also say:
SELECT wrd FROM tablename WHERE wrd LIKE CONCAT(:partial, '%')
to do the string joining at the MySQL end, not that there's any particular reason to in this case.
Things get a bit more tricky if the partial wrd
you are looking for can itself contain a percent or underscore character (since those have special meaning for the LIKE operator) or a backslash (which MySQL uses as another layer of escaping in the LIKE operator — incorrectly, according to the ANSI SQL standard).
Hopefully that doesn't affect you, but if you do need to get that case right, here's the messy solution:
$stmt= $db->prepare("SELECT wrd FROM tablename WHERE wrd LIKE :term ESCAPE '+'");
$escaped= str_replace(array('+', '%', '_'), array('++', '+%', '+_'), $var);
$stmt->bindParam(':term', $escaped);
$var = "partial%";
$stmt = $dbh->prepare("select wrd from tablename WHERE wrd LIKE :partial");
$stmt->bindParam(":partial", $var);
$stmt->execute(); // or $stmt->execute(array(':partial' => $var)); without
// first calling bindParam()
$rs = $stmt->fetchAll();
Using question mark parameters:
$stmt = $dbh->prepare('select wrd from tablename WHERE wrd LIKE ?');
$stmt->execute(array('partial%'));
$rs = $stmt->fetchAll();
http://www.php.net/manual/en/pdo.prepare.php
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With