I'm trying to query Access via ODBC. Most queries work fine, but I'm having trouble with one I'm generating.
If I do this:
<pre>
$patientlist = "'NAME1', 'NAME2', 'NAME3', 'NAME4', 'NAME5', 'NAME6'";
$sqlquery = "SELECT * FROM Patient WHERE FullName IN ($patientlist)";
echo $sqlquery . "<hr>";
$cur = odbc_exec($cx, $sqlquery);
</pre>
I get 4 rows back, which is correct.
But if I do this, expanding and formatting the list from a form variable:
<pre>
$patientlist = "";
foreach (explode("\n", $names) as $eachname) {
if (preg_match("/\.?(.*), (.*) +([0-9]+\/[0-9]+)/", $eachname, $matches)) {
if ($patientlist == "") {
$patientlist = "'".$matches[2]." ".$matches[1]."'";
} else {
$patientlist .= ", '" . $matches[2] . " " . $matches[1] . "'";
};
};
}
echo $patientlist . "<hr>";
$sqlquery = "SELECT * FROM Patient WHERE FullName IN ($patientlist)";
echo $sqlquery . "<hr>";
$cur = odbc_exec($cx, $sqlquery);</pre>
$patientlist looks right as:
<pre>
'NAME1', 'NAME2', 'NAME3', 'NAME4', 'NAME5', 'NAME6'
</pre>
but the query comes back with zero results.
I assume I'm doing something wrong with the string concatenation, but I can't figure it out.
Any idea what's going on here?
If I do this:
<pre>
$patientlist = "'NAME1', 'NAME2', 'NAME3', 'NAME4', 'NAME5', 'NAME6'";
$sqlquery = "SELECT * FROM Patient WHERE FullName IN ($patientlist)";
echo $sqlquery . "<hr>";
$cur = odbc_exec($cx, $sqlquery);
</pre>
I get 4 rows back, which is correct.
But if I do this, expanding and formatting the list from a form variable:
<pre>
$patientlist = "";
foreach (explode("\n", $names) as $eachname) {
if (preg_match("/\.?(.*), (.*) +([0-9]+\/[0-9]+)/", $eachname, $matches)) {
if ($patientlist == "") {
$patientlist = "'".$matches[2]." ".$matches[1]."'";
} else {
$patientlist .= ", '" . $matches[2] . " " . $matches[1] . "'";
};
};
}
echo $patientlist . "<hr>";
$sqlquery = "SELECT * FROM Patient WHERE FullName IN ($patientlist)";
echo $sqlquery . "<hr>";
$cur = odbc_exec($cx, $sqlquery);</pre>
$patientlist looks right as:
<pre>
'NAME1', 'NAME2', 'NAME3', 'NAME4', 'NAME5', 'NAME6'
</pre>
but the query comes back with zero results.
I assume I'm doing something wrong with the string concatenation, but I can't figure it out.
Any idea what's going on here?