0

My PHP looks like this:

$diagSel = $_POST['diagSel'];
$search_crit = $_POST['criteria']; //this is an entry like "85054,85206" (no quotes)
$sql1 = "SELECT * FROM `myTable` where`Diagnosis` = :diagnosis and `zip_code` in (:placeHolder) group by `Provider Number`";
$stmt = $dbh->prepare($sql1);
$stmt->bindParam(':diagnosis', $diagSel, PDO::PARAM_STR);
$stmt->bindParam(':placeHolder', $search_crit, PDO::PARAM_STR);
$stmt->execute();
$result1 = $stmt->fetchAll(PDO::FETCH_ASSOC);
header('Content-type: application/json');
echo json_encode($result1);

Here's the problem...if the user enters multiple ZIP Codes (passed in criteria) that are comma separated, this ECHOs nothing. If they enter a single ZIP Code, it returns exactly what I'd expect.

Is there a way to pass a comma separated value by PDO such as 85054,85206 using prepared statements?

Thanks.

jonmrich
  • 4,233
  • 5
  • 42
  • 94
  • Use an ORM or PDO wrapper that supports array binding (Aura.SQL does, others allow Perl-style `??` list placeholders). Alternatively: use `FIND_IN_SET()` for a CSV list. – mario Jun 07 '15 at 18:21
  • Haven't used the `??` methods much before...can you give an example? – jonmrich Jun 07 '15 at 18:30
  • That's not a PDO thing. This requires an abstraction library on top which allows such placeholders. Else you'll have to resort to [hodgepodge `join()`/etc. workarounds](http://stackoverflow.com/questions/14767530/php-using-pdo-with-in-clause-array). – mario Jun 07 '15 at 18:47

2 Answers2

1

It is not, I'd recommend something like this:

$diagSel     = $_POST['diagSel'];
$search_crit = $_POST['criteria'];
$list        = explode(',', $search_crit);
array_map(array($dbh, 'quote'), $list);

$sql1 = sprintf('
  SELECT *
  FROM `myTable`
  WHERE `Diagnosis` = :diagnosis
  AND `zip_code` IN (%s)
  GROUP BY `Provider Number`', implode(',', $list));

$stmt = $dbh->prepare($sql1);
$stmt->bindParam(':diagnosis', $diagSel, PDO::PARAM_STR);
$stmt->execute();

$result1 = $stmt->fetchAll(PDO::FETCH_ASSOC);
header('Content-type: application/json');
echo json_encode($result1);
Martin
  • 6,632
  • 4
  • 25
  • 28
  • Thanks. Is something missing from this line? `array_map([$dbh, 'quote'], $list);` Getting error: `Call to undefined function sprtinf()` – jonmrich Jun 07 '15 at 18:27
  • Actually...missed one thing...getting this error now though: `syntax error, unexpected '[', expecting ')'` This is referring to the `array_map` line. – jonmrich Jun 07 '15 at 18:29
  • Sorry the first one was a typo, the second I believe you have an old version of PHP (pre 5.4). Try now! – Martin Jun 07 '15 at 18:55
  • I thought I was on PHP 5.4, but maybe not. I tried the above and now getting this error: " Uncaught exception 'PDOException' with message 'SQLSTATE[HY093]: Invalid parameter number'" referring to the `execute` line – jonmrich Jun 07 '15 at 19:05
  • Nevermind...missed the `(%s)` in the query. It's working perfectly! Thanks for the help. – jonmrich Jun 07 '15 at 19:07
  • One more question...this works perfectly when I pass in numbers like ZIP codes (45642,23245), but if I try to do it with states that are strings like (NY,AK), then it doesn't work. It seems like I need to add double quotes around each state name in the query to get it to work, but not sure how this would be done or if there's another alternative. – jonmrich Jun 07 '15 at 19:39
  • No...different query, but same idea. Replace `zip_code` with `state`. They are stored in different columns. – jonmrich Jun 07 '15 at 22:15
1

You can't use bindpram twice if you want to add multiple values to the SQL query have an array in the exec command to add in all the variables

x86bit-destroyer
  • 15
  • 5