Parameterising an IN clause in PHP with sqlsrv

Question

I have a method which selects records based on an array of integers which works when specifically typed. However, when I try to replace that array with one passed in from the method parameter sqlsrv_query() returns false. I am sure this is something very simple, but the 'obvious' possibilities to me do not work.

Working Version

public function FindLocationRecords($locationIds)
{
    require("./Location.php");
    $resource = sqlsrv_connect($this->Server,$this->ConnectionInfo);
    $tsql = "select Date, PlaceName from rde_613949.dbo.Locations where id  in (14, 15, 16, 17); SELECT SCOPE_IDENTITY() as id";
    $results = sqlsrv_query($resource, $tsql);
    $locations = array();
    while($row = sqlsrv_fetch_array($results, SQLSRV_FETCH_ASSOC))
    {
        $location = new Location($row['Date'], $row['PlaceName']);
        array_push($locations, $location);
    }
    return $locations;
}

Not Working Version 1

public function FindLocationRecords($locationIds)
{
    require("./Location.php");
    $resource = sqlsrv_connect($this->Server,$this->ConnectionInfo);
    $tsql = "select Date, PlaceName from rde_613949.dbo.Locations where id  in ?; SELECT SCOPE_IDENTITY() as id";
    $results = sqlsrv_query($resource, $tsql, array($locationIds));
    $locations = array();
    while($row = sqlsrv_fetch_array($results, SQLSRV_FETCH_ASSOC))
    {
        $location = new Location($row['Date'], $row['PlaceName']);
        array_push($locations, $location);
    }
    return $locations;
}

Not Working Version 2

public function FindLocationRecords($locationIds)
{
    require("./Location.php");
    $resource = sqlsrv_connect($this->Server,$this->ConnectionInfo);
    $tsql = "select Date, PlaceName from rde_613949.dbo.Locations where id  in (?); SELECT SCOPE_IDENTITY() as id";
    $results = sqlsrv_query($resource, $tsql, array($locationIds));
    $locations = array();
    while($row = sqlsrv_fetch_array($results, SQLSRV_FETCH_ASSOC))
    {
        $location = new Location($row['Date'], $row['PlaceName']);
        array_push($locations, $location);
    }
    return $locations;
}

Not Working Version 3

public function FindLocationRecords($locationIds)
{
    require("./Location.php");
    $resource = sqlsrv_connect($this->Server,$this->ConnectionInfo);
    $tsql = "select Date, PlaceName from rde_613949.dbo.Locations where id  in ?; SELECT SCOPE_IDENTITY() as id";
    $results = sqlsrv_query($resource, $tsql, $locationIds);
    $locations = array();
    while($row = sqlsrv_fetch_array($results, SQLSRV_FETCH_ASSOC))
    {
        $location = new Location($row['Date'], $row['PlaceName']);
        array_push($locations, $location);
    }
    return $locations;
}

You need to have the SQL something like `where id in (?, ?, ?);` with the same amount of ?'s as values you want to check. — Nigel Ren, Nov 20 '20 at 16:30
Correct me if I am wrong, please, but I thought the notation your suggesting would be used if I wanted to do something like 'where field1 = ?, where friend2=? etc. And how could I make that work for an array of unknown size? Do I need to pass lots of searches to the database? Surely not? — Sara Payne, Nov 20 '20 at 16:34
It sort of reflects the same method as for mysql (shown in https://stackoverflow.com/questions/3703180/a-prepared-statement-where-in-query-and-sorting-with-mysql). — Nigel Ren, Nov 20 '20 at 16:39
Raw SQL does not really have the concept of lists or arrays. You can implement your own pure-PHP parsing or use raw SQL Server parameters as suggested. — Álvaro González, Nov 20 '20 at 17:29
@Zhorov I am using that because without it the result is false, so the following section doesn't work. As I understand it this is something specific to SQL Server not found in other SQL databases. — Sara Payne, Nov 21 '20 at 17:15

score 3 · Accepted Answer · answered Nov 23 '20 at 07:52

You need to generate a dynamic list of placeholders (?) and inlcude it in the SQL statement. Also, always check the result from sqlsrv_connect() and sqlsrv_query() execution.

This example, based on the code in the question, is a possible solution to your problem:

<?php
public function FindLocationRecords($locationIds) {
    require("./Location.php");

    // Connection   
    $resource = sqlsrv_connect($this->Server, $this->ConnectionInfo);
    if ($resource === false) {
        echo "Error (sqlsrv_connect): ".print_r(sqlsrv_errors(), true); 
        return false;
    }   
    
    // Statement
    $tsql = "
        SELECT [Date], PlaceName 
        FROM rde_613949.dbo.Locations 
        WHERE id IN (".substr(str_repeat(',?', count($locationIds)), 1).")"
    ;
    $results = sqlsrv_query($resource, $tsql, $locationIds);
    if ($results === false) {
        echo "Error (sqlsrv_query): ".print_r(sqlsrv_errors(), true);   
        return false;
    }   

    // Data
    $locations = array();
    while ($row = sqlsrv_fetch_array($results, SQLSRV_FETCH_ASSOC)) {
        $location = new Location($row['Date'], $row['PlaceName']);
        array_push($locations, $location);
    }
    return $locations;
}
?>

For some reason I was under the impression, like the OP, that the PHP driver would be able to parse PHP arrays into a comma-separated list for the query. While I have not really found anything explicitly stating that it can or cannot take an array as a param — I have found this [list of SQLSRV_PHPTYPE_* constants](https://learn.microsoft.com/en-us/sql/connect/php/constants-microsoft-drivers-for-php-for-sql-server?view=sql-server-ver15#phptype-constants) that can be defined along with the param, which does not list an array as an accepted data type. — Brennan, May 18 '22 at 21:22

Parameterising an IN clause in PHP with sqlsrv

1 Answers1

Linked