PHP PDO和SQL搜索通配符绑定参数 [英] PHP PDO & SQL Search wildcard bind parameters

问题描述

我正在尝试为网站创建搜索功能，并创建了从PHPMYADMIN生成的sql代码，请参见下文；

$sql = "SELECT * FROM `bg85ow`.`sessions` WHERE CONVERT(`SessionName` USING utf8) LIKE '%:SessionName1%'";

这是处理查询的PHP代码；

//bind params
                $SessionName1 = filter_input(INPUT_GET, 'search');
                $stmt -> bindValue(':SessionName1', $SessionName1, PDO::PARAM_STR);

                //execute
                $success = $stmt -> execute();

                if (!$success){
                     print $stmt->errorInfo()[2]; //PDO driver error message
                }

                else{
                }

                //array
                $r = $stmt->fetchAll(PDO::FETCH_ASSOC);
                $dbh = null;

                if(!$r)
                {
                    echo "No Results";
                }      

                foreach ((array) $r as $row) {
                 echo $row['SessionId'];
                 echo $row['SessionName'];
                } 

由于某些原因，它不会返回任何结果，execute部分运行良好并通过了成功测试，但是一旦到达数组，它就不会返回任何结果.

我检查了一下，并且$SessionName1中包含了搜索词，因此该词被传递给了ok.

当我将%:SessionName1%更改为我正在测试的搜索项football时，代码可以很好地返回结果，但是一旦更改回:SessionName1，即使搜索项为，也不会返回结果完全一样.

谁能看到我在做什么错，我花了很长时间才看到这个错误，却看不到错误.

我到处都在搜索有关此问题的答案，但是我找不到特定于此问题的答案，而且我还是PHP和SQL的初学者.

解决方案

尝试以下示例:检查您在哪里错了.

// Get the keyword from query string
$keyword = $_GET['keyword'];
// Prepare the command
$sth = $dbh->prepare('SELECT * FROM `users` WHERE `firstname` LIKE :keyword');
// Put the percentage sing on the keyword
$keyword = "%".$keyword."%";
// Bind the parameter
$sth->bindParam(':keyword', $keyword, PDO::PARAM_STR);

I am trying to create a search function for a site and have created the sql code, which was generated from PHPMYADMIN, see below;

$sql = "SELECT * FROM `bg85ow`.`sessions` WHERE CONVERT(`SessionName` USING utf8) LIKE '%:SessionName1%'";

Here is the PHP code processing the query;

//bind params
                $SessionName1 = filter_input(INPUT_GET, 'search');
                $stmt -> bindValue(':SessionName1', $SessionName1, PDO::PARAM_STR);

                //execute
                $success = $stmt -> execute();

                if (!$success){
                     print $stmt->errorInfo()[2]; //PDO driver error message
                }

                else{
                }

                //array
                $r = $stmt->fetchAll(PDO::FETCH_ASSOC);
                $dbh = null;

                if(!$r)
                {
                    echo "No Results";
                }      

                foreach ((array) $r as $row) {
                 echo $row['SessionId'];
                 echo $row['SessionName'];
                } 

For some reason this will not return any results the execute part is working fine and passing the success test but then once it gets to the array it returns no results.

I checked and $SessionName1 has the term in it from search so it is being passed to the query ok.

When I change the %:SessionName1% to football which is the search term I am testing with, the code returns the results fine but once changed back to :SessionName1 it will not return the results even though the search term is exactly the same.

Can anybody see what I am doing wrong, I spent ages looking at this and cannot see the error.

I have searched everywhere for an answer to this but I couldn't find one specific to this issue, also I am a beginner with PHP and SQL.

解决方案

Try this Example : Check where is you are wrong.

// Get the keyword from query string
$keyword = $_GET['keyword'];
// Prepare the command
$sth = $dbh->prepare('SELECT * FROM `users` WHERE `firstname` LIKE :keyword');
// Put the percentage sing on the keyword
$keyword = "%".$keyword."%";
// Bind the parameter
$sth->bindParam(':keyword', $keyword, PDO::PARAM_STR);

这篇关于PHP PDO和SQL搜索通配符绑定参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！