CODEWITHSUNDEEP
phpsql-servert-sqlparameterized
Sebastian
Sebastian

Reputation: 471

PHP MSSQL (Transact-SQL) - parameterized query with IN clause and multiple values in array

I am stuck with a piece of PHP code (PHP 5) where I am running an SQL query (Transact SQL).

The Code (which is not working):

$query = "
  UPDATE my_table
  SET my_column = 'some_value'
  WHERE my_id IN (?);" // does not work :-(
sqlsrv_query($my_connection, $query, array('abc', 'def', 'ghi')); // does not work :-(

What I am trying to do: Update my_table in my_column for rows with my_id equal to abc, def and ghi.

However, when I write the same SQL query with no parameters (this works):

$query = "
  UPDATE my_table
  SET my_column = 'some_value'
  WHERE my_id IN ('abc', 'def', 'ghi');" // works :-)
sqlsrv_query($my_connection, $query); // works :-)

I've tried to execute the sqlsrv_query command like this

sqlsrv_query($my_connection, $query, array('abc', 'def', 'ghi'));

and like this

sqlsrv_query($my_connection, $query, array(array('abc', 'def', 'ghi')));

and like this

sqlsrv_query($my_connection, $query, 'abc', 'def', 'ghi');

None of them work.

Can someone please help me? I've had a good read on the manual. And writing the SQL like this

... WHERE my_id IN (?,?,?)...

is not an option since my array will contain a variable amount of values!

Thank you!

Upvotes: 0

Views: 1373

Answers (3)

Sebastian
Sebastian

Reputation: 471

The answer to my question was given by Colin and ESDictor:

$params = array('abc', 'def', 'ghi');
$query = "
  UPDATE my_table
  SET my_column = 'some_value'
  WHERE my_id IN (" . implode(',', array_fill(0, count($params), '?')) . ");" // works like a charm
sqlsrv_query($my_connection, $query, $params);

Thank you guys! I'd like to upvote your answer, Colin, in particular (but cannot since you 'just' commented. ESDictors answer is basically the same!

Upvotes: 1

Hackerman
Hackerman

Reputation: 12295

Try this:

$params = array('abc', 'def', 'ghi'); //assume it dinamic
$query = "
UPDATE my_table
SET my_column = 'some_value'
WHERE my_id IN (";
$inside = "";
foreach ($params as $key => $value) {

$inside = $inside."?,";
}

$inside = trim ($inside,",");
$query = $query.$inside.");";

sqlsrv_query($my_connection, $query, $params);

PS: Now the code can handle a dinamic array XD

Saludos ;)

Upvotes: 0

ESDictor
ESDictor

Reputation: 745

I just handled the same problem, only in Visual Studio. First I created a string of parameters to add into the SQL statement. You only have to deal with question marks (?), so much of what I did is more than you need:

string[] inClause = new string[keywordTerms.Length];

for (int i = 0; i < keywordTerms.Length; i++)
    inClause[i] = ":keyword" + i.ToString("00");

Then when creating my select, I put the following in:

sqlStatement += "WHERE kw.keyword IN (" + String.Join(", ", inClause) + ")"

Finally, I added the parameters in this code:

for (int i = 0; i < keywordTerms.Length; i++)
    cmd.Parameters.Add(inClause[i], OracleDbType.Varchar2, 20, "keyword").Value = keywordTerms[i];

Hope that helps!

Upvotes: 1

Related Questions