I had a query that was working properly at first sight, no errors on execute, nothing, but there were simply no results returned at runtime.
Be careful when putting the database commands into a function and binding your variables there while using oci_fetch_xxx() outside the function.
function sql($conn, $stmt, $var) {
$stid = oci_parse($conn, $stmt);
...
oci_bind_by_name($stid, ':val', $var);
...
}
sql($conn, $q, $var);
$row = oci_fetch_array($stid, OCI_ASSOC+OCI_RETURN_NULLS);
As you see from the definition of oci_bind_by_name(), $var needs to be passed as reference, so your function has to have this reference ready like this:
function sql($conn, $stmt, &$var) {
$stid = oci_parse($conn, $stmt);
...
oci_bind_by_name($stid, ':val', $var);
...
}
The background is that if you don't pass by reference (in which case $var inside the function is a copy of $var outside the function), then oci_bind_by_name() will work just fine at first glance.
However, since the oci_fetch statements that you use to actually get the data will reference the $var that has ceased to exist when the function finished. In fact, since the varbind seems to be a pointer, that pointer will point to an invalid location at that point and your variables won't be substitued in the SQL.
All this also means that:
1) You have to pass a variable, and not just a value
This doesn't work:
$stid = sql($conn, $q, array('bla'=>'blubb'));
This is better:
$vars = array('bla'=>'blubb');
$stid = sql($conn, $q, $vars);
2) Even when passing as reference to your helper function you cannot use e.g. foreach:
This doesn't work:
function sql($conn, $q, $vars) {
...
foreach ($vars as $k => $v) {
oci_bind_by_name($stid, $k, $v);
}
...
}
Again, because $k and $v are local variables that will have disappeared once you perform an oci_fetch outside the function.
Instead you have to work the array in a more low-level way like this:
function sql($conn, $q, &$vars) {
...
$stid = oci_parse($conn, $q);
...
reset($vars);
do {
if (current($vars)===FALSE) { // end of array
break;
}
$b = oci_bind_by_name($stid, key($vars), current($vars));
if ($b === FALSE) {
DIE('Could not bind var');
}
} while (each($vars) !== FALSE);
}
$binds = array(':bla1' => 'blubb1',
':bla2' => 'blubb2');
$stid = sql($conn, $q, $binds);
$row = oci_fetch_array($stid, OCI_ASSOC+OCI_RETURN_NULLS);
Wherever you oci_bind_by_name(), the pointer to the initial data has to exist from beginning to end.