从PHP数组中搜索mySQL表中的多个列与已修复

Edit: Thanks to spencer7593 for helping me solve this. I have edited the main code to include the correct form for those who come later.

编辑:感谢spencer7593帮助我解决这个问题。我编辑了主要代码,以便为后来的人提供正确的表格。

I am currently wrapping my head around PHP and mySQL and have run into a roadblock on searching mySQL.

我目前正在围绕PHP和mySQL,并且在搜索mySQL时遇到了障碍。

I need to search multiple columns in a table for partial matches. I have got this working properly with a fixed set of columns to search, but when I try and use an array it fails, even though the output to mySQL seems identical.

我需要在表中搜索多个列以进行部分匹配。我已经使用一组固定的列进行搜索,但是当我尝试使用数组时它会失败,即使输出到mySQL看起来相同。

Can anyone point out the tiny silly error I have made somewhere? That, or an improved way of doing it?

任何人都可以指出我在某个地方犯过的那个小小的愚蠢错误吗?那,还是改进的方式呢?

<form name="search" method="post" action="<?php $PHP_SELF; ?>">
    Search for: <input type="text" name="find" />
    <input type="hidden" name="searching" value="yes" />
    <input type="submit" name="search" value="Search" />
</form>


<?php

$dbdata=array(
    'servername'    => "localhost",
    'username'      => "Username",
    'password'      => "Password",
    'dbname'        => "Membership",
    'table_Members' => "Members"
);
$fields=array(
                'MemberID',
                'FirstName',
                'LastName',
                'Nickname',
                'Interests',
                'Skills');

$output=array();
if( isset($_POST['searching']) ) {
    $output=read_db_search($dbdata['table_Members'],$_POST['find'],$fields);
}
echo ('<pre>');
var_dump($output);
echo ('</pre>');

function read_db_search ($table,$searchQuery,$fields)
{
    global  $dbdata;
    $servername=$dbdata['servername'];
    $dbname=$dbdata['dbname'];
    $dbtable=$dbdata['table_Members'];

    try {
        $conn = new PDO("mysql:host=$servername;dbname=$dbname", $dbdata['username'], $dbdata['password']);
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

/*This version works!!!!*/
$query = "SELECT * FROM `Members` WHERE `"
       . implode("` LIKE '%$searchQuery%' OR `",$fields) 
           . "` LIKE '%$searchQuery%' " ;

            /* This section should work but doesnt */
        //$query="SELECT * FROM `Members` WHERE`'.implode(' ` LIKE \'%$searchQuery%\' OR `',$fields).'` LIKE \'%$searchQuery%\'";
        //$stmt = $conn->prepare($query);



            /* This one works fine */
    /*  $stmt = $conn->prepare("SELECT * FROM `Members` WHERE
            `MemberID`  LIKE '%$searchQuery%' OR 
            `FirstName`     LIKE '%$searchQuery%' OR 
            `LastName`  LIKE '%$searchQuery%' OR 
            `Nickname`  LIKE '%$searchQuery%' OR 
            `Interests` LIKE '%$searchQuery%' OR 
            `Skills`    LIKE '%$searchQuery%'");
*/


        $stmt->execute();
            $result = $stmt->setFetchMode(PDO::FETCH_ASSOC);
            $data=$stmt->fetchAll();
        }
        catch(PDOException $e) {
            echo "Error: " . $e->getMessage();
        }
        $conn = null;
        return $data;
}
?>

A var_dump of $query returns:

$ query的var_dump返回:

string(89) "SELECT * FROM `Members` WHERE`'.implode(' ` LIKE \'%searchterm%\' OR `',Array).'` LIKE \'%searchterm%\'"

with an error of:

错误:

Error: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '\'%searchterm%\' OR `',Array).'` LIKE \'%searchterm%\'' at line 1

1 个解决方案

#1

As a first step in debugging this, do an echo or var_dump of $query before it's submitted to the database.

作为调试它的第一步,在提交到数据库之前执行$ query的echo或var_dump。

But as some suggestions... here:

但正如一些建议......在这里:

$fields=array( 
            `MemberID`,
            `FirstName`,

What's the deal with the backticks? Do you want MemberID to be executed, and have the return from that put into an array? Or did you want to put string literals into an array? For example:

与反叛有什么关系?您是否希望执行MemberID,并将其返回到数组中?或者你想把字符串文字放入数组?例如:

$fields=array(
           '`MemberID`',
           '`FirstName`',

-or-

$fields=array(
           'MemberID',
           'FirstName',

If the string literals include the backticks, then you wouldn't need to muck with adding backticks in the "implode", when you generate the SQL text into $query.

如果字符串文字包含反引号,那么当你在$ query中生成SQL文本时,你不需要在“implode”中添加反引号。

And here:

   WHERE`'.implode(' ` LIKE \'
        ^           ^

It looks like this is not leaving a space between the keyword WHERE and the identifier. And it looks like a space is added to the identifier, between the last character of the column name (e.g. MemberID) and that closing backtick. so your SQL is going to reference an identifier that includes a space character, e.g.

看起来这不会在关键字WHERE和标识符之间留下空格。并且看起来在列名称的最后一个字符(例如MemberID)和结束反引号之间添加了一个空格到标识符。所以你的SQL将引用一个包含空格字符的标识符,例如

  ... WHERE`MemberID ` LIKE '%...
           ^        ^

That looks like a problem to me. Looks like you want to generate

这对我来说似乎是一个问题。看起来你想要生成

  ... WHERE `MemberID` LIKE '%...
           ^         ^

If you've already included backticks around the identifiers in the array, then you don't need to add backticks in the generation of $query. If you do include the backticks, then they need to be "tight" to the value supplied for the identifier (the column name), and not include extra space characters inside the backticks.

如果您已经在数组中的标识符周围添加了反引号,那么您不需要在$ query的生成中添加反引号。如果你确实包含了反引号,那么它们需要“紧密”到为标识符(列名)提供的值,而不是在反引号中包含额外的空格字符。

To explain that a little more clearly with an example... this column name:

用一个例子来说明一点......这个列名:

`abc`

Is not the same as this column name:

与此列名称不同:

`abc `

Again, for debugging this, just do an echo or var_dump of your generated query string, and take a close look at the SQL that's going to be submitted to the database. If you can't spot an error, and try taking that SQL text and testing it against the database in another client.

同样,为了调试它,只需对生成的查询字符串执行echo或var_dump,并仔细查看将要提交给数据库的SQL。如果您无法发现错误,请尝试获取该SQL文本并针对另一个客户端中的数据库进行测试。

EDIT

There's several problems in the PHP string manipulation that's assigning a value to $query.

PHP字符串操作中存在一些问题,即为$ query分配值。

Try something like this:

尝试这样的事情:

$query = "SELECT * FROM `Members` WHERE `"
       . implode("` LIKE '%$searchQuery%' OR `",$fields) 
               . "` LIKE '%$searchQuery%' " ;

When that's complete, $query should contain something that looks like a valid SQL statement, something like this

完成后,$ query应该包含一些看似有效的SQL语句的东西,就像这样

SELECT *
  FROM `Member`
 WHERE `MemberID`  LIKE '%foo%'
    OR `FirstName` LIKE '%foo%'

(I've added line breaks and whitespace for formatting.)

(我为格式添加了换行符和空格。)

#1