PHP PDO - Using an array for a SELECT SQL Statement

Question

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

I am using 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.
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)

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.

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

"it doesn't seem to want to accept it." Can you be more specific? — Matt S
– Matt S, Commented Jun 13, 2017 at 14:36
You do know with PDO you can do ->execute( array(....) ); dont you? And therefore you can loose the bindParam() — RiggsFolly
– RiggsFolly, Commented Jun 13, 2017 at 14:36
And in your query you need , after each ? and a space before WHERE. But who outputs variables now, pfffff. — u_mulder
– u_mulder, Commented Jun 13, 2017 at 14:39
A simple echo $queryString; should show you a few obvious errors — RiggsFolly
– RiggsFolly, Commented Jun 13, 2017 at 14:45

Alexey · Accepted Answer · 2017-06-13 14:46:04Z

1

After debugging your code I see two issues:

missing semicolon after $queryString .= 'WHERE column4 = ?' part.
If I dump your resulting query, it would be something like

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

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

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

to

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

and (in order to strip the last comma)

 $queryString .= ' WHERE column4 = ?'

replace with

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

answered Jun 13, 2017 at 14:46

Alexey

3,4847 gold badges32 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

tserio Over a year ago

thanks for pointing that out.. i just quickly wrote this an know theres a few errors in the query string, but even if the string is correct it still has the problem

Conor Mancone · Accepted Answer · 2017-06-13 16:02:00Z

1

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.

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.

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()

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 );
}

answered Jun 13, 2017 at 16:02

Conor Mancone

2,03017 silver badges23 bronze badges

1 Comment

tserio Over a year ago

Hey Conor, appreciated the input. Thanks

tserio · Accepted Answer · 2017-06-13 15:52:58Z

0

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:

$columnsToRetrieve = array('name' => 'Frank', 'phone' => '1112223333');
$email = '[email protected]';

$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();
}

edited Jun 13, 2017 at 15:52

answered Jun 13, 2017 at 15:46

tserio

355 bronze badges

5 Comments

Conor Mancone Over a year ago

One mistake: you need the colon before the name of your placeholder in your sql query, but when you build your array you want to just use the placeholder name as the key: you don't include the colon in the keys of the array that gets passed to execute. I.e.: $values['email'] = $email and $values[$k] = $v;. In this case though, I would say that using positional arguments is easier. Either way, you've ditched the bindParam that was causing the original bug, so this will work.

tserio Over a year ago

Thanks for the input again Conor.. I actually was able to successfully run the above code without error. Do you mind explaining a bit more what it looks like I've done wrong?

Conor Mancone Over a year ago

After a little more digging I realized what is going on, which is that nothing is wrong. The PHP documentation shows using a colon in the array key name. However, this colon is not actually required. In actual use I have only seen it executed without the colon, so I presumed that the version with the colon was a mistake. Turns out either one works. You learn something new every day!

tserio Over a year ago

Awesome. Appreciate the input though. So im guessing you were referenced to the $values[':' $key] part?

Conor Mancone Over a year ago

Yes. $values[':' . $k] = $v; becomes $values[$k] = $v;.

Collectives™ on Stack Overflow

PHP PDO - Using an array for a SELECT SQL Statement

3 Answers 3

1 Comment

1 Comment

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related