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.
php bindParam not working with LIKE statement
The solution would be to not include the %
in your query but in your param, as it is part of the search expression and not a "flag" of LIKE
:
$stmt = $conn->prepare("SELECT * FROM movies WHERE movie_name LIKE :mName");
$stmt->bindParam(':mName', '%' . $moviename . '%');
Note that you don't have to put the simple-quotes around the parameter, since PDO will be dealing with this on its own.
mysql PDO how to bind LIKE
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);
Is it possible to bindParam WHERE name like %:name%
Use
LIKE CONCAT('%', :name, '%')
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.
PDO : prepare with bindvalue and like %
you did not execute()
.
after binding you need to execute then fetch:
$q->bindValue(':n',"%code%",PDO::PARAM_STR);
$q->execute();
while ($data = $q->fetch(PDO::FETCH_ASSOC))
you can bind like this with php variable:
$q->bindValue(':n','%'.$var.'%',PDO::PARAM_STR);
Mysql, PDO - Like statement not working using bindParam
You need to bind each parameter separately, you can do so with a second loop.
function retrieve_search_posts(PDO $pdo, $search_field) {
/*
* Get the PDO object as an argument, this function shouldn't care
* how the PDO object is created, that's the factory's job.
*/
/*
* Use $underscored_names or $camelCase for variable names, easier on the eye
*/
## Variable initializations ##
$where = array();
##Function start
$words = preg_split("/\s+/", $search_field);
for ($i = 0; $i < count($words); $i++) {
/*
* We don't need to have the word in here,
* so we aren't even using the foreach loop, just a normal for
*/
$where[] .= "`post_title` LIKE ?";
}
/*
* For cleaner code, use an array and implode the pieces with OR,
* this way, you don't get an OR at the beginning, nor the end.
*/
$where_string = implode(" OR ", $where);
$query = <<<MySQL
SELECT p.post_id, post_year, post_desc, post_title, post_date, img_file_name, p.cat_id
FROM mjbox_posts p
JOIN mjbox_images i
ON i.post_id = p.post_id
AND i.cat_id = p.cat_id
AND i.img_is_thumb = 1
AND post_active = 1
WHERE ?
ORDER BY post_date DESC
LIMIT 9
MySQL;
$sth = $pdo->prepare($query);
/*
* Iterate over the array again,
* this time, we're binding the values based on the index
*/
foreach ($words as $index => $word) {
$sth->bindValue($index+1, $word, PDO::PARAM_STR);
}
$sth->execute();
$result = $sth->fetchAll(PDO::FETCH_ASSOC); //Fetch all the results in associative array form
return $result;
}
See the comments on the code to explain the changes made.
bindParam for a GET-value for LIKE in a PDO MySQL query
Concatenate the wildcard symbols to the variable within SQL:
$stmt = $dbh->prepare("
SELECT *
FROM posts
WHERE heading LIKE CONCAT('%', :term, '%')
OR full_text LIKE CONCAT('%', :term, '%')
LIMIT 0 , 30
");
$stmt->bindParam(':term', $_GET['p']);
$stmt->execute();
$answer = $stmt->fetch(PDO::FETCH_ASSOC);
Like query with alphanum string bindParam
try isolate the param content in the like matching clause as
SELECT name, number FROM client WHERE number LIKE concat('%', '30', '%')
eg
SELECT name, number FROM client WHERE number LIKE concat('%', :your_param , '%')
or equivalent
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.
Related Topics
Passing JavaScript Variable to PHP Using Ajax
Concatenating a String and Primary Key Id While Inserting
How to Show a Where Clause Just for One Field in MySQL
Laravel Add a New Column to Existing Table in a Migration
Programmatically Access Currency Exchange Rates
Alter Table in Magento Setup Script Without Using SQL
How to Force Users to Access My Page Over Https Instead of Http
Laravel: Using Try...Catch with Db::Transaction()
PHP - Get Key Name of Array Value
Will Enabling Xdebug on a Production Server Make PHP Slower
Simplest Way to Detect Client Locale in PHP
PHP Gd Create a Transparent Png Image
Access Denied for User @ 'Localhost' to Database ''