Parameterising an IN clause in PHP with sqlsrv

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;
}

Answer

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;
}
?>