32

My SQL looks something like this:

$sql = "select * from user where id in (:userId) and status = :status";

$em = $this->getEntityManager();
$stmt = $em->getConnection()->prepare($sql);
$stmt->bindValue(':userId', $accounts, \Doctrine\DBAL\Connection::PARAM_INT_ARRAY);
$stmt->bindValue(':status', 'declined');
$stmt->execute();

$result = $stmt->fetchAll();

But it returns:

An exception occurred while executing (...)

with params [[1,2,3,4,5,6,7,8,11,12,13,14], "declined"]

Notice: Array to string conversion

I cannot user queryBuilder because my real SQL is more complicated (ex. contains joined select, unions and so on)

Improve this question
1
  • Can you use foreach? foreach($accounts as $key => $val) { $stmt->bindValue(':userId', $val); } Commented May 13, 2016 at 12:16

3 Answers 3

Reset to default
52

You can't use prepared statements with arrays simply because sql itself does not support arrays. Which is a real shame. Somewhere along the line you actually need to determine if your data contains say three items and emit a IN (?,?,?). The Doctrine ORM entity manager does this for you automatically.

Fortunately, the DBAL has you covered. You just don't use bind or prepare. The manual has an example: https://www.doctrine-project.org/projects/doctrine-dbal/en/latest/reference/data-retrieval-and-manipulation.html#list-of-parameters-conversion

In your case it would look something like:

$sql = "select * from user where id in (?) and status = ?";
$values = [$accounts,'declined'];
$types = [Connection::PARAM_INT_ARRAY, \PDO::PARAM_STR];
$stmt = $conn->executeQuery($sql,$values,$types);
$result = $stmt->fetchAll();

The above code is untested but you should get the idea. (Make sure you use Doctrine\DBAL\Connection; for Connection::PARAM_INT_ARRAY)

Note for people using named parameters:

If you are using named parameters (:param instead of ?), you should respect the parameter names when providing types. For example:

$sql = "select * from user where id in (:accounts) and status = :status";
$values = ['accounts' => $accounts, 'status' => 'declined'];
$types = ['accounts' => Connection::PARAM_INT_ARRAY, 'status' => \PDO::PARAM_STR];
Improve this answer
Sign up to request clarification or add additional context in comments.

13 Comments

ok but in this situation I need to be careful to bind data in the right order :( This is "not cool" :\
I understand the concern and named parameters are nice but think about it, you only have two parameters. Not difficult to keep them in order. When dealing with queries with a larger number of parameters, I use the dbal query builder to generate the sql and basically add values to my $values array as I go along. So it's not so bad once you get used to it. And it saves you from having to decide what the parameters should be named in the first place. Naming is always hard.
But mu real SQL is more complex pastebin.com/1JiRHFdc, any ideas how to do this 'right'? It will also contains more variables and in where clausure if user decide to more filter results
Start by using the DBAL query builder. In this case, you would have two $qb objects. One for the inner select and one for the outer select. Use $qb->toSQL() to feed the inner select into the outer one. Then it's just a question of keeping your $values and $types array in sync. As far as additional additional where clauses, maybe take a look here: sitepoint.com/community/t/….
First decide if you are using DQL or SQL. Big difference. For SQL use $conn->createQueryBuilder();
|
2

If you want to stick to the :param syntax where order does not matter, you have to do a bit of extra work, but I'll show you an easier way to bind the parameters:

// store all your parameters in one array
$params = array(
    ':status' => 'declined'
);

// then, using your arbitrary array of id's ...
$array_of_ids = array(5, 6, 12, 14);

// ... we're going to build an array of corresponding parameter names
$id_params = array();
foreach ($array_of_ids as $i => $id) {
    // generate a unique name for this parameter
    $name = ":id_$i"; // ":id_0", ":id_1", etc.

    // set the value
    $params[$name] = $id;

    // and keep track of the name
    $id_params[] = $name;
}

// next prepare the parameter names for placement in the query string
$id_params = implode(',', $id_params); // ":id_0,:id_1,..."
$sql = "select * from user where id in ($id_params) and status = :status";

In this case we end up with: "select * from user where id in (:id_0,:id_1,:id_2,:id_3) and status = :status"

// now prepare your statement like before...
$stmt = $em->getConnection()->prepare($sql);

// ...bind all the params in one go...
$stmt->execute($params);

// ...and get your results!
$result = $stmt->fetchAll();

This approach will also work with an array of strings.

Improve this answer

Comments

0

You need to wrap them in an array

$stmt->bindValue(
  ':userId', 
  array($accounts), 
  array(\Doctrine\DBAL\Connection::PARAM_INT_ARRAY)
);

http://doctrine-dbal.readthedocs.io/en/latest/reference/data-retrieval-and-manipulation.html#list-of-parameters-conversion

edit

I should have elaborated more. You cannot bind an array like that, dont prepare the sql execute directly as the example in the docs.

$stmt = $conn->executeQuery(
  'SELECT * FROM articles WHERE id IN (?)',
  array(array(1, 2, 3, 4, 5, 6)),
  array(\Doctrine\DBAL\Connection::PARAM_INT_ARRAY)
);

You cannot bind an array of values into a single prepared statement parameter

Improve this answer

1 Comment

I don't think so... Warning: PDOStatement::bindValue() expects parameter 3 to be integer, array given. I am using prepare method, not executeQuery

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.