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

[Rolando Acosta]

Hi Melanie and Ling,

Thank you very much for helping me with this. It seems that both approaches
do work.

Nice one @Ling!

Best,
Rolando Acosta (CHU Sainte-Justine)

On Thu, Jan 16, 2020 at 11:26 AM Ling Ma <lingyun.ma at mcin.ca> wrote:

> 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/20200117/98aec226/attachment.html>