10 Inserting Multiple Data Values

PHP OCI8 can insert arrays of characters or integers in one call. This reduces network traffic and database system overhead when inserting multiple values into a table.

This chapter contains the following topics:

• Creating the Multiple Insert Form

• Running the Multiple Insert Form

10.1 Creating the Multiple Insert Form

The example in this chapter shows a form allowing three data values to be inserted in one operation.

The array insert is done using a PL/SQL bulk FORALL command. Login to SQL*Plus as HR and create a PL/SQL package:

CREATE OR REPLACE PACKAGE equip_pkg AS
    TYPE arrtype IS TABLE OF VARCHAR2(20) INDEX BY PLS_INTEGER;
    PROCEDURE insert_equip(eid_p IN NUMBER, eqa_p IN arrtype);
END equip_pkg;
/

CREATE OR REPLACE PACKAGE BODY equip_pkg AS
    PROCEDURE insert_equip(eid_p IN NUMBER, eqa_p IN arrtype) IS
    BEGIN
        FORALL i IN INDICES OF eqa_p
            INSERT INTO equipment (employee_id, equip_name) 
                                   VALUES (eid_p, eqa_p(i));
    END insert_equip;
END equip_pkg;
/

Note:

The "/" tokens are needed only in SQL*Plus.

The insert_equip() procedure accepts an array of equipment names and inserts them in to the EQUIPMENT table.

Create a new PHP file ac_add_multi.php and copy the contents of ac_add_one.php to it. Carefully make the following changes to convert it to handle an array of values.

In the HTML form in ac_add_multi.php, change the one input field from:

<div>
    Equipment name <input type="text" name="equip"><br>
    <input type="hidden" name="empid" value="$empid">
...

to three input fields:

...
<div>
    Equipment name <input type="text" name="equip[]"><br>
    Equipment name <input type="text" name="equip[]"><br>
    Equipment name <input type="text" name="equip[]"><br>
    <input type="hidden" name="empid" value="$empid">
...

Note the [] tokens to return an array, which were not needed in ac_add_one.php.

Replace the getcleanequip() function in ac_add_multi.php so it handles the array of returned form values:

/**
 * Perform validation and data cleaning so empty strings are not inserted
 *
 * @return array The array of new data to enter
 */
function getcleanequip() {
    if (!isset($_POST['equip'])) {
        return array();
    } else {
        $equiparr = array();
        foreach ($_POST['equip'] as $v) {     // Strip out unset values
            $v = trim($v);
            if (!empty($v))
                $equiparr[] = $v;
        }
        return($equiparr);
    }
}

This loops along each of the array entries and only returns non empty strings.

Finally, replace doinsert() in ac_add_multi.php with:

/**
 * Insert an array of equipment values for an employee
 *
 * @param Db $db
 * @param array $equiparr array of string values to be inserted
 * @param string $empid Employee identifier
 */
function doinsert($db, $equiparr, $empid) {
    $arraybinds = array(array("eqa", $equiparr, SQLT_CHR));
    $otherbinds = array(array("eid", $empid, -1));
    $sql = "BEGIN equip_pkg.insert_equip(:eid, :eqa); END;";
    $db->arrayInsert($sql, "Insert Equipment List", $arraybinds, $otherbinds);
}

This uses a new arrayInsert() method in the Db class to call the PL/SQL insert_equip() procedure. The data value arrays needs to be bound differently from normal scalar PHP OCI8 binds, so the bind parameters to arrayInsert() are separated into two kinds.

Edit ac_db.inc.php and add the new method:

    /**
     * Insert an array of values by calling a PL/SQL procedure
     *
     * Call like Db::arrayinsert("begin myproc(:arn, :p); end",
     *                               "Insert stuff",
     *                               array(array(":arn", $dataarray, SQLT_CHR)),
     *                               array(array(":p", $p, -1)))
     *
     * @param string $sql PL/SQL anonymous block
     * @param string $action Action text for End-to-End Application Tracing
     * @param array $arraybindvars Bind variables. An array of tuples
     * @param array $otherbindvars  Bind variables. An array of tuples
     */
    public function arrayInsert($sql, $action, $arraybindvars, 
    $otherbindvars = array()) {
        $this->stid = oci_parse($this->conn, $sql);
        foreach ($arraybindvars as $a) {
            // oci_bind_array_by_name(resource, bv_name, 
            // php_array, php_array_length, max_item_length, datatype)
            oci_bind_array_by_name($this->stid, $a[0], $a[1], 
            count($a[1]), -1, $a[2]);
        }
        foreach ($otherbindvars as $bv) {
            // oci_bind_by_name(resource, bv_name, php_variable, length)
            oci_bind_by_name($this->stid, $bv[0], $bv[1], $bv[2]);
        }
        oci_set_action($this->conn, $action);
        oci_execute($this->stid);              // will auto commit
        	$this->stid = null;
    }

Binding in Db::arrayInsert() is similar to the example previously shown in this manual. The oci_bind_array_by_name() function takes slightly different arguments, since the number of elements in data array must now be passed in. In the AnyCo application oci_bind_array_by_name is being used only for inserting data from PHP so the maximum data length parameter can be passed as -1. This tells PHP to use the actual value lengths. The single oci_execute() call inserts all the data items into the database.

10.2 Running the Multiple Insert Form

Save the files and run the AnyCo application in a browser. Log in as Administrator and click the Add Multiple link for Steven King.

Add some data items such as Computer, Monitor, and Keyboard.

Click Submit and then click Show next to Steven King to check that the data items are inserted.

Array binding also works for fetching data. PL/SQL procedures using the efficient BULK COLLECT syntax can return data to PHP in one OCI8 oci_execute() call. For retrieving data from Oracle the oci_bind_array_by_name() call would need to know how many items and what the maximum data size is so PHP can allocate the memory correctly.