It would be really useful to have an easy way to pass arrays as bound parameters to queries or procedures from PHP. This would be especially useful if you’re letting Oracle handle most of your data manipulating (as you should).
One example I see time and time again about how to (mis)manage array functionality in PHP’s interaction with the database is the simple “insert more than one record” problem:

$userId = 'some user ID';
foreach($toys as $toy) {
$query = "insert into user_toy (user_id, toy_id) values (:user_id, :toy_id)";
$stmt = $dbh->prepare($query);
$stmt->bindParam(':user_id', $userId);
$stmt->bindParam(':toy_id', $toy['id']);
$stmt->execute();
}

Now let’s talk about what’s wrong with this implementation and how to fix it.

1. No queries in loops, please!
The first major problem here is doing queries in loops. Just don’t do it. Say no to queries in loops.
Here’s a slightly better version of this code:

$query = "insert into user_toy (user_id, toy_id) values (:user_id, :toy_id)";
$stmt = $dbh->prepare($query);
foreach($toys as $toy) {
$stmt->bindParam(':user_id', $userId);
$stmt->bindParam(':toy_id', $toy['id']);
$stmt->execute();
}

This is much nicer. Your query only needs to be parsed (prepared) once and then you just go through plugging in the new values. But this is still clunky – you’re still managing your loops from PHP and passing data to the database. Why not send it all to the database in one nice chunk?
2. In the ideal world…
Ideally, you want to do this:

$user_id = 'some user ID';
$toy_ids = array(1, 2, 3);
$query = "insert into user_toy (user_id, toy_id) values (:user_id, :toy_id)";
$stmt = $dbh->prepare($query);
$stmt->bindParam(':user_id', $user_id);
$stmt->bindParam(':toy_ids', $toy_ids);
$stmt->execute();

At this point though, this will not work. You’re trying to bind an array to a string parameter. What to do, what to do?
3. Put all your DML in stored procedures.
Any INSERT, UPDATE or DELETE statements should be handled from within your procedures. You will have to worry less about when a transaction ends and begins – you can just commit transactions upon $stmt->execute() in PHP and let your database worry about special cases. Also, as your application grows, you will most likely have to do something else in addition to your change when the change happens. If you have it all encapsulated in a procedure, your PHP code will not have to be changed to accommodate. Additionally, since PL/SQL is compiled, you will get a (small, but every bit counts) performance boost.
At this point, you have a package with a procedure to do what you want to do:

create or replace package body user_pkg as
procedure add_toy (
p_user_id users.id%type,
p_toy_id toy.id%type
)
is
begin
insert into user_toy (
user_id,
toy_id)
values (
p_user_id,
p_toy_id);
end;
end;

And your PHP code now looks like this:

$query =
"begin
user_pkg.add_toy(
p_user_id => :user_id,
p_toy_id => :toy_id
);
end;";
$stmt = $dbh->prepare($query);
foreach($toys as $toy) {
$stmt->bindParam(':user_id', $userId);
$stmt->bindParam(':toy_id', $toy['id']);
$stmt->execute();
}

4. str2tbl
Now you’re ready for some lists. Oracle documentation is notoriously humongous. When you try to search for “array” or “list”, you will get a pile of results that mention VARRAY, BULK COLLECT, OCIBindArrayOfStruct()… It can be pretty overwhelming. Ask Tom contains a lot of information as well, but it is more practical. There I found an interesting article which explains how to create a table of elements on the fly based on a passed string.
Why would a table of elements made from a string be interesting? First, it’s surprisingly difficult to convince Oracle to have an array of a number of elements where the number is not predetermined. In PHP all arrays are as big as you need them to be, so you’d want your database to understand that as well. Additionally, the easiest thing to bind is a string and frankly, if it isn’t easy, people won’t use it.
5. The list_pkg package.
Based on the example from AskTom, I created list_pkg defined as follows:

create or replace package list_pkg as
type list_string is table of varchar2(255) index by binary_integer;
function parse_string_to_list_string(p_list varchar2) return list_string;
end;
/
show errors

And the body is defined as:

create or replace package body list_pkg as
function parse_string_to_list_string(p_list varchar2) return list_string
is
v_len   constant integer := nvl(length(p_list), 0);
v_out   list_string;
v_i     integer := 1;
v_j     integer;
begin
loop
exit when v_i > v_len;
v_j := instr(p_list, ',', v_i);
if v_j = 0 then v_j := v_len + 1; end if;
v_out(v_out.count + 1) := substr(p_list, v_i, v_j-v_i);
v_i := v_j + 1;
end loop;
return v_out;
end;
end;
/
show errors

This packages basically says: Give me a string of all your array values concatenated into one giant string and I’ll make it into an “array” that PL/SQL can understand.
6. list_pkg in your procedure.
At this point, you can add a new procedure ‘add_toys’ (notice the plural) to your user_pkg package:

procedure add_toys (
p_user_id users.id%type,
p_toy_ids list_pkg.list_string
)
is
begin
for i in p_toy_ids.first .. p_toy_ids.last loop
insert into user_toy (
user_id,
toy_id)
values (
p_user_id,
p_toy_id);
end loop;
exception
-- handle any exceptions
end;

Nice. Now giving a bunch of toys to a user is done in one swift transaction.
7. list_pkg in your PHP
And here’s how your PHP will call this procedure:

$query =
"begin
user_pkg.add_toys(
p_user_id => :user_id,
p_toy_ids => list_pkg.parse_string_to_list_string(:toy_ids)
);
end;";
$stmt = $dbh->prepare($query);
$stmt->bindParam(':user_id', $userId);
$stmt->bindParam(':toy_id', implode(',', $toys));

8. Leveraging list_pkg
Now you know how to pass a list of elements to your procedure. The examples I have provided are for strings only, but you could (and I have) make separate lists for other datatypes: number, raw (good for GUIDs), etc. And since you have all lists encapsulated in procedures, you can do things like wrap them in quotes (very useful for IN clauses). PL/SQL is very robust and graceful at handling sets of data. Passing the data to PL/SQL and letting it handle it is just using the best tool for the job.

TAGS: None