3
$sSql = "SELECT * FROM table1 where field1 > 0 and field2 IN (:buzzGroups) and active = 1";
$arrParams = array('buzzGroups' => $vBuzzGroups);
$stmt = $this->tableGateway->getAdapter()->createStatement($sSql);
$stmt->prepare($sSql);
$data = $stmt->execute($arrParams);

Here is the value of $vBuzzGroups = '10,12';

when I pass only single value then it works but when I try with multiple values..that of comma separated it gives me the error,

Conversion failed when converting the nvarchar value '10,12' to data type int

Is there any one who faced this issue?

Wilt
  • 41,477
  • 12
  • 152
  • 203
kumar
  • 1,796
  • 2
  • 15
  • 37
  • You need to identify each IN value as a separate value, not as a comma-separated string.... otherwise it will be treated as a string – Mark Baker Jul 27 '15 at 10:25
  • What's the type of the `field2`? – vhu Jul 27 '15 at 10:39
  • @vhu - the type is INT, but still it should work as similar to normal SQL query with IN query in which the values as passed with comma separated. – kumar Jul 27 '15 at 11:22
  • @GordonLinoff - it is SQL Server.. – kumar Jul 27 '15 at 11:23
  • In Doctrine I pass Parameter to IN as an Array `$vBuzzGroups = array(10,12)`. Maybe its the same here? – danopz Jul 27 '15 at 11:31
  • If you look carefully it is a duplicate : http://stackoverflow.com/questions/920353/can-i-bind-an-array-to-an-in-condition – PaulF Jul 27 '15 at 11:50
  • @PaulF - I've seen this..but there is an issue with that process, they have taken the array directly to the execute statement(I mean replacing the '?' of IN query with a single array of variables that need to be send)..if that is the case, what happens if I have two IN statements in the same query. – kumar Jul 27 '15 at 11:59
  • @kumar: maybe you could adapt the solution by alan_mm (about 1/2 way down with two upvotes) & use a different prefix for each of the IN statements. – PaulF Jul 27 '15 at 12:47

1 Answers1

0

Use implode() function to split values.

Try this updated code

$arrParams = array('buzzGroups' => $vBuzzGroups);
$sSql = "SELECT * FROM table1 where field1 > 0 and field2 IN 
(".implode(',',$arrParams).") and active = 1";
Ugur Altay
  • 81
  • 3