0

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!

Improve this question
4
  • 2
    $query = "UPDATE ... WHERE my_id IN (" . implode(',', array_fill(0, count($ids), '?')) . ")"; Easily handles multiple bound parameters. Be weary of the maximum limit. Commented Feb 21, 2013 at 19:30
  • Hi Colin, thank you for your answer! By imploding my array of ids - do I still get an SQL-injection safe "parameterized query" out of your solution? Does not look like it to me (maybe I'm wrong) - can you please explain?! Thank you! Commented Feb 21, 2013 at 19:34
  • 1
    I'm not imploding your array of IDs. I'm creating a string of question marks (placeholders) separated by commas. You still get all the benefits of bound parameters if you bind as usual! Commented Feb 21, 2013 at 19:36
  • aaaah, I've just now tried it, and it works! Thank you very much! As I will in no case have more than 100 ids, your solution will work (maximum limit will not be breached). I had hoped to get a solution with ONE ? and SQL being smart enough to convert my array() to the apropriate string needed to fill the IN()... Thanks a lot!! Commented Feb 21, 2013 at 19:44

3 Answers 3

Reset to default
1

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!

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

2 Comments

Hi ESDictor! Thank you very much! That helps, as it is basically the same solution as Colin has pointed out. Basically you are adding the same number of ":keyword" placeholders in your SQL statement which you later fill by adding the parameters to the code. I was hoping to find a solution with only ONE ":keyword" in my SQL statement and was hoping that MSSQL is smart enough to popluate that ONE :keyword with multiple values! But hey, your solution works, too! Thanks :-)
I know how you feel, but after a lot of searching I came up with this approach, and it works as well as I could hope. Good luck with it!
1

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!

Improve this answer

Comments

0

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

Improve this answer

2 Comments

Hi Robert, thank you very much for your help! I know, this works - but does not work if my params array holds a dynamically changing amount of keys (i.e.: if $params = array('abc', 'def'); I'd have to change the ? to (?,?);"... ;)
Robert, thank you for your help - I've figured it out already (see my summary of Colins and ESDictors answers above).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.