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
JavaScript
x
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
JavaScript
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
JavaScript
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
JavaScript
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;
}
Advertisement
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:
JavaScript
<?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;
}
?>