[Loris-dev] Issue when trying to use "pselectOne" and "pselect" functions with an " IN " sql statement

[Ling Ma]

Hi Melanie and Rolando,

In fact, you can use pselect in this case. If you use pselect, you have to
pass each "in" clause item as a parameter, not as a whole. For example, the
following code could solve your problem.

$params = array();
$values = array();
foreach ($list as $key => $id) {
    $params[]         = ":id$key";
    $values["id$key"] = $id;
}
$results = $DB->pselect(
    "SELECT * FROM x WHERE ID in (" .
    implode(', ', $params) . ")",
    $values
)

Best regards,

Ling Ma
Development consultant

On Thu, Jan 16, 2020 at 11:15 AM Melanie Legault, Mrs <
melanie.legault2 at mcgill.ca> wrote:

> Hello Rolando,
>
> Sorry if I misled you on the wrong track. You can't use `in` using
> pselect() or pselectOne().
> In order to use `in` you have to use run().
>
> Mélanie
>
> ------------------------------
> *From:* loris-dev-bounces at bic.mni.mcgill.ca <
> loris-dev-bounces at bic.mni.mcgill.ca> on behalf of Rolando Acosta <
> rolando.acosta at cnbp.ca>
> *Sent:* January 16, 2020 10:55
> *To:* loris-dev at bic.mni.mcgill.ca <loris-dev at bic.mni.mcgill.ca>
> *Subject:* [Loris-dev] Issue when trying to use "pselectOne" and
> "pselect" functions with an " IN " sql statement
>
> Hi LORIS team,
>
> A while ago I faced the following issue in one of the PRs I was working on:
>
> /////////////////////////////////////////
>
> The structure:
>
> $recruitmentStartDate = $DB->pselectOne(
>     "SELECT MIN(Date_registered)
>      FROM candidate
>
> * WHERE RegistrationCenterID IN (:Sites)",     array('Sites' =>
> implode(",", array_keys($list_of_sites)))*
> );
>
> Doesn't seem to be working for me. Only the first element of the array
> $list_of_sites is been passed to the IN SQL clause. Not sure if I'm using
> correctly this way. For the moment I write it as:
>
> $recruitmentStartDate = $DB->pselectOne(
>     "SELECT MIN(Date_registered)
>      FROM candidate
>
> *WHERE RegistrationCenterID IN (" . $sitesString . ")",     array()*
> );
>
> //////////////////////////////////////////////////
>
>
> I'm having the same behavior with the function *pselect(string $query,
> array $params): array*
> from the *class Database*.
>
> I'm wondering if you could take a look and see if there is a way to pass
> an array as one of the elements in the *$params* array? The issue rises
> when need to do an IN statement as part of the query.
>
> Cheers,
> Rolando Acosta (CHU Sainte-Justine)
> _______________________________________________
> Loris-dev mailing list
> Loris-dev at bic.mni.mcgill.ca
> https://mailman.bic.mni.mcgill.ca/mailman/listinfo/loris-dev
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mailman.bic.mni.mcgill.ca/pipermail/loris-dev/attachments/20200116/dd8ef4ee/attachment-0001.html>