PHP PDO - 将数组用于SELECT SQL语句

I'm having a bit of an issue. Basically what I am trying to do is the following;

我有点问题。基本上我要做的是以下几点;

I am using PDO

我正在使用PDO

I want the ability to pass an array containing the column name in the db (the key) and the info I want to insert (value). The array may contain 1, or many fields that need to be updated.

我希望能够传递包含db(键)中的列名和我想要插入的信息(值)的数组。该数组可能包含1个或许多需要更新的字段。

I have designed a function that looks like this:

我设计了一个如下所示的函数:

Sample Array being passed:

传递的样本数组:

$columnsToRetrieve = array('column1' => 'info', 
                            'column2' => 'data', 
                            'column3' => 'data');

And a sample function (did not include the DB initialization part)

和一个示例函数(不包括DB初始化部分)

function updateInfo ($columnsToRetrieve, $whereClauseValue) {

    $counter = 1;
    $queryString = 'UPDATE table SET ';
    foreach ($columnsToRetrieve as $k => $V) {
        $queryString .= $k . ' = ?';    
    }
    $queryString .= 'WHERE column4 = ?'
    $stmt = $dbc->dbConnection->prepare($queryString);
    foreach ($columnsToRetrieve as $k => $v) {
        $stmt->bindParam($counter, $v);
        $counter++;
    }
    $stmt->bindParam($counter, $whereClauseValue);
    $stmt->execute();
}

The problem is with the second foreach, where I am trying to use the bindParam($counter, $value). Although the right number and value populates, it doesn't seem to want to accept it.

问题出在第二个foreach,我试图使用bindParam($ counter,$ value)。尽管填充了正确的数字和值,但它似乎并不想接受它。

Does anyone have any ideas how this can be done, or what I'm doing wrong above?

有没有人有任何想法如何做到这一点,或者我上面做错了什么?

3 个解决方案

#1

After debugging your code I see two issues:

调试代码后,我看到两个问题:

missing semicolon after $queryString .= 'WHERE column4 = ?' part.

$ queryString之后缺少分号。='WHERE column4 =?'部分。

If I dump your resulting query, it would be something like

如果我转储结果查询,它会是这样的

UPDATE table SET column1 = ?column2 = ?column3 = ?WHERE column4 = ?

UPDATE表SET column1 =?column2 =?column3 =?WHERE column4 =?

see missing spaces. So what if you modify this line:

看到缺少的空间。那么如果你修改这一行怎么办:

$queryString .= $k . ' = ?';

$queryString .= $k . ' = ?,';

and (in order to strip the last comma)

和(为了删除最后一个逗号)

 $queryString .= ' WHERE column4 = ?'

replace with

$queryString = substr($queryString,0,-1) .  ' WHERE column4 = ?';

#2

95% sure that your issue is your use of bindParam. bindParam works by reference, not by value. As a result your foreach loop isn't binding your values to your query, but rather the $v variable. By the time you call execute all parameters are bound to the same $v variable, so when you execute your query does not behave as expected.

95%确定你的问题是你使用bindParam。 bindParam按引用而不是按值工作。因此,您的foreach循环不会将您的值绑定到查询,而是绑定$ v变量。当您调用execute时,所有参数都绑定到相同的$ v变量,因此当您执行查询时,其行为不符合预期。

The solution is simple: use bindValue() or execute(). Personally, I use execute() exclusively: it is simple to understand and read. Without a doubt though, the reference nature of bindParam() will cause plenty of trouble: I've been burned by it before.

解决方案很简单:使用bindValue()或execute()。就个人而言,我只使用execute():它易于理解和阅读。毫无疑问,bindParam()的引用性质会带来很多麻烦:我之前已被它烧过。

Edit to add specific example

编辑以添加特定示例

I've copied and pasted your above code, so any errors will remain here, but it is actually much simpler with execute()

我复制并粘贴了你上面的代码,所以任何错误都会保留在这里,但实际上使用execute()会更简单

function updateInfo ($columnsToRetrieve, $whereClauseValue) {

    $counter = 1;
    $queryString = 'UPDATE table SET ';
    foreach ($columnsToRetrieve as $k => $V) {
        $queryString .= $k . ' = ?';    
    }
    $queryString .= 'WHERE column4 = ?'
    $stmt = $dbc->dbConnection->prepare($queryString);

    $values = array_values( $columnsToRetrieve );
    $values[] = $whereClauseValue();

    $stmt->execute( $values );
}

#3

With the assistance of @RiggsFolly and @Sebastian Brosch, I was able to better understand the scenario. I looked up some past posts as well, and was able to tailor a possible solution:

在@RiggsFolly和@Sebastian Brosch的帮助下,我能够更好地理解这个场景。我查了一些过去的帖子,并且能够定制一个可能的解决方案:

$columnsToRetrieve = array('name' => 'Frank', 'phone' => '1112223333');
$email = 't@t.com';

$values = array();

$string = 'UPDATE users SET';

foreach ($columnsToRetrieve as $k => $v) {
    $string .= ' ' . $k . ' = :' . $k . ',';
    $values[':' . $k] = $v;
};

$string = substr($string, 0, -1);

$string .= ' WHERE email = :email;';

$values[':email'] = $email;

try {
    $stmt = $dbc->prepare($string);
    $stmt->execute($values);
} catch (PDOException $e) {
    echo $e->getMessage();
}

#1