Example of IN statement

[aggarwaldev]

Hi,

I have the following statement:

$result = $this->db->exec(
                    " SELECT userImage FROM candid WHERE id = ? AND status IN ? ",
                    [
                        $userid,
                        $status
                    ]
                );

And $status = array('active','idle');

I am unable to fetch results from database.
I even tried changing $status from array to variable:
$status = "'interview','pending'";

Can you please help?

Note: This is a question and not an issue.

[ocram]

This is an excellent question, thank you!

The problem is that PHP’s built-in PDO, which is used internally in this library, does not support arrays as parameters but only scalars such as strings and integers.

Right now, the only workaround is that you create your custom placeholder, which is a sequence of ? wrapped in parentheses (instead of only a single ?):

$statusPlaceholder = '(' . \str_repeat('?,', \count($value) - 1) . '?)';

Then use that placeholder instead of the normal ? in the query, like this:

$myQuery = "SELECT userImage FROM candid WHERE id = ? AND status IN " . $statusPlaceholder;
$result = $this->db->exec(
	$myQuery,
        // ...
);

It gets worse – you also have to adjust your array of parameters, because the entries of $status must be in that array individually now:

$myParameters = \array_merge(
    [ $userid ],
    $status
);
$result = $this->db->exec(
	// ...
        $myParameters
);

This is really ugly, but unfortunately, it is necessary at the moment in order to have a correct and safe solution.

We don’t actually want that annoying way of writing these queries, of course. So, in the future, we should patch support for array parameters directly into this library, using the workaround above internally so that you don’t have to do this manually. But for now, no such solution is available. Sorry!

Does that help?

[aggarwaldev]

Hey,

Thanks for the workaround. I'll give it a try.

Since PHP-DB was last updated over an year ago, Is there any update releasing anytime soon?
I am seriously looking forward for one as this is one of the best database libraries for PDO and I would be depending upon IN clause heavily for my application.

Also, I was checking out the forks of this library, One of the users have created insertOnDuplicate function which you might consider for the master branch too. What do you think?

[ocram]

Thanks!

This library is still maintained and there will definitely be updates in the future. Just can’t give you any specific schedule yet. Sorry! By the way, the last update has not been over a year ago, but nine months ago.

This specific feature, IN clauses for conditions, will be one of the first we’ll work on, for sure. It’s quite important in everyday usage.

We’d love to have something like a insertOnDuplicate method, but the suggested implementation has been too simple, as discussed here: #4 If we have a proper solution, we’ll implement this as well.

[ocram]

Ugh, it’s been a while. Let’s keep this open, because it is still planned in fact, and so we can track it. Thank you!