PHP SQLSRV: passing a string of values to IN clause

Question

I need a way to perform the following query:

SELECT *
FROM myTable
WHERE column NOT IN ('val1', 'val2')

Now, val1 and val2 are inside an array that I'm imploding into a string like this:

$inClause = "'" . implode("','", $inClauseArr) . "'";

If I put the string in the query declaration like this it works:

$sql = "[...]WHERE column NOT IN ($inClause)";

But if I pass it as a sqlsrv_query parameter like this, the query is not working:

$stmt = sqlsrv_query($conn, $sql, array($inClause));

I'm not getting any error. $stmt is true but while cycle is not returning anything.

I absolutely need to pass it through sqlsrv_query, how can i do this?

UPDATE

Here's the $sql value:

SELECT *
FROM orders(NOLOCK)
WHERE order_id NOT IN (?)

Can you show us the value of $sql when you attempt to use the parameters? — ImClarky
– ImClarky, Commented Dec 14, 2016 at 10:22
@ImClarky can't see how this can help, but I've updated the question (actually it's quite the same of the first code quote) — Mark
– Mark, Commented Dec 14, 2016 at 13:21
You should also populate question marks in your sql query according to $inClauseArr length. — alpakyol
– alpakyol, Commented Dec 15, 2016 at 6:02
@alalp it would look exactly like the first query i've posted...am I misunderstaing something in your requests? — Mark
– Mark, Commented Dec 15, 2016 at 14:04

alpakyol · Accepted Answer · 2016-12-16 06:06:34Z

1

Count of question marks in your original SQL query must match with the count of parameters you have passed into the query.

Supposing $inClauseArr is like:

$inClauseArr = array('val1', 'val2');

There are two elements. Actually, it is not important how much element it has. As long as $inClauseArr is an array, there will be no problem.

So, the query should be constructed as ([...] is the beginning of the query):

$sql = "[...]WHERE column NOT IN ("
    . implode(',', array_fill(0, count($inClauseArr), '?'))
    . ")";

// For $inClauseArr = array('val1', 'val2');
// Output should be [...]WHERE column NOT IN (?, ?)

And execution should be like:

$stmt = sqlsrv_query($conn, $sql, $inClauseArr);

References:

answered Dec 16, 2016 at 6:06

alpakyol

2,4542 gold badges32 silver badges40 bronze badges

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

2 Comments

Mark Over a year ago

That's exactly what i'm doing now, but i need to avoid generating pieces of query in the code because the query will have to be stored in a db table. Then i'll need to retrieve it from there AS STRING and execute it just passing the values to the sqlsrv_query, without the need of manipulate it. It seems this method doesn't allow an easy way of using the SQL IN clause

alpakyol Over a year ago

@Mark I think you can parse comma separated $inClause string in your sql query to populate a dynamic table. Then you can use that table in IN clause (or maybe with INNER JOIN).

Gregg · Accepted Answer · 2016-12-13 22:21:37Z

0

Pass it in a a comma delimited string (varchar) then use a split function to create a table:

     declare @vars varchar(14) =  'G' + ',' + 'H'

     select * from syscode_detail 
     where code in (select value from dbo.Split(@vars, ','))

     using this function:

     CREATE function [dbo].[Split]
     (
      @List nvarchar(2000),
      @SplitOn nvarchar(5)
      )  

    returns @tblReturn table 
    (
    id int identity(1,1),
    value nvarchar(100)
    ) 
 as  
Begin

    While (Charindex(@SplitOn, @List) > 0)
    Begin
        Insert Into @tblReturn (value)
        Select value = ltrim(rtrim(Substring(@List, 1, Charindex(@SplitOn,
        @List) - 1)))

        Set @List = Substring(@List, Charindex(@SplitOn, @List) + 
        len(@SplitOn), len(@List))
    End

    Insert Into @tblReturn (value)
    Select Value = ltrim(rtrim(@List))

    Return

End

answered Dec 13, 2016 at 22:21

Gregg

6256 silver badges7 bronze badges

1 Comment

Mark Over a year ago

Thanks @Gregg, to create or modify stored/functions is not allowed in this project...i would need a solution on the PHP side

Collectives™ on Stack Overflow

PHP SQLSRV: passing a string of values to IN clause

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related