Re: [patch] DB::common::autoPrepareMultiple,autoExecuteMultiple

File it as a bug as well, so it doesnt get forgotten. Regards Alan Aaron S. Hawley wrote:

The following is a patch that simply extends the power of autoPrepare and autoExecute. Now a $data array can be an array of associative arrays where keys are column names and values are update or insert values. $fields_and_values = array(

                          array('column1' => 1,
                                'column2' => 'one',
                                'column3' => 'en'),
                          array('column3' => 'tre',
                                'column2' => 'two',
                                'column1' => 2));

$dbh->autoExecuteMultiple($table_name, $field_and_values,

                         DB_AUTOQUERY_INSERT);

More importantly, /any/ of the parameters can be arrays where a respective index is used to retrieve its value. thus the following more complicated example: $updates_and_inserts = array(

                            array('column1' => 4,
                                  'column2' => 'four',
                                  'column3' => 'fire'),
                            array('column3' => 'to'));

// $table_name could be an array to, but it doesn't have to. $dbh->autoExecuteMultiple($table_name, $updates_and_inserts,

                         array(DB_AUTOQUERY_INSERT,
                               DB_AUTOQUERY_UPDATE),
                         array(false, 'column1 = 2');

Not sure if this sort of thing has been proposed or not. Its not a lot of code because PEAR::DB is pretty powerful. I'd be more than willing to help with documentation if it's considered a worthy patch. /a Index: common.php =================================================================== RCS file: /repository/pear/DB/DB/common.php,v retrieving revision 1.26 diff -c -r1.26 common.php *** common.php 1 Oct 2003 16:48:49 -0000 1.26 --- common.php 13 Nov 2003 23:22:57 -0000 *************** *** 457,462 **** --- 457,497 ----

         return $this->prepare($query);
     }

+     // }}}
+     // {{{ autoPrepareMultiple()

+

+     /**
+     * Create multiple insert or update queries and call prepare() on each,
+     * the result can be passed onto executeMultiple().
+     *
+     * @param string $table name of the table
+     * @param array $table_fields ordered array of assoc array
+     *              containing the fields names as keys
+     * @param int $mode type of query to make (DB_AUTOQUERY_INSERT or
+                        DB_AUTOQUERY_UPDATE)
+     * @param string $where in case of update queries, this string will
+                            be put after the sql WHERE statement
+     * @return array of resource handles for the query
+     * @see autoPrepare
+     * @access public
+     */
+     function autoPrepareMultiple($table, $data, $mode = DB_AUTOQUERY_INSERT, $where = false)
+     {
+         for ($i = 0; $i < sizeof( $data ); $i++) {
+             if (!is_array($data[$i])) {
+                 return $this->raiseError(DB_ERROR_INVALID);
+             }
+             $stmts[$i] = $this->autoPrepare($this->_arrayElementOrScalar($table, $i),
+                                             array_keys($data[$i]),
+                                             $this->_arrayElementOrScalar($mode, $i),
+                                             $this->_arrayElementOrScalar($where_stmt, $i));
+             if (DB::isError($stmts[$i])) {
+                 return $stmts;
+             }
+         }
+         return $stmts;
+     }

+

     // {{{
     // }}} autoExecute()

*************** *** 653,660 ****

     /**
     * This function does several execute() calls on the same
!     * statement handle.  $data must be an array indexed numerically
!     * from 0, one execute call is done for every "row" in the array.
     *
     * If an error occurs during execute(), executeMultiple() does not
     * execute the unfinished rows, but rather returns that error.

--- 688,696 ----

     /**
     * This function does several execute() calls on the same
!     * statement handle, or an array of statement handles.  $data must
!     * be an array indexed numerically from 0, one execute call is done
!     * for every "row" in the array.
     *
     * If an error occurs during execute(), executeMultiple() does not
     * execute the unfinished rows, but rather returns that error.

*************** *** 672,678 ****

     function executeMultiple( $stmt, &$data )
     {
         for($i = 0; $i < sizeof( $data ); $i++) {
!             $res = $this->execute($stmt, $data[$i]);
             if (DB::isError($res)) {
                 return $res;
             }

--- 708,718 ----

     function executeMultiple( $stmt, &$data )
     {
         for($i = 0; $i < sizeof( $data ); $i++) {
!             if (is_array($stmt)) {
!                 $res = $this->execute($stmt[$i], array_values($data[$i]));
!             } else {
!                 $res = $this->execute($stmt, $data[$i]);
!             }
             if (DB::isError($res)) {
                 return $res;
             }

*************** *** 681,686 **** --- 721,762 ----

     }

     // }}}
+     // {{{ autoExecuteMultiple()

+

+     /**
+     * Creates insert or update queries for a numerically indexed array
+     * of associative arrays.  Uses the keys of each associative array
+     * to determine the columns in automatically creating query
+     * statements.  All arguments including the data array can be
+     * arrays, but scalars can be used for all variables.
+     *
+     * @param mixed $table string or array of names of the table
+     * @param array $data array of assoc ($key=>$value) where $key is a
+     *                    field name and $value its value
+     * @param mixed $mode int or array of modes representing type of
+     *                    query to make (DB_AUTOQUERY_INSERT or
+     *                    DB_AUTOQUERY_UPDATE)
+     * @param mixed $where string or array of strings for case of
+     *              update queries, each string will be put after its
+     *              respective SQL WHERE statement.
+     * @see autoExecute
+     * @see autoPrepareMultiple
+     * @see executeMultiple
+     * @access public
+     *
+     * @return mixed DB_OK or DB_Error
+     */

+

+     function autoExecuteMultiple($table, &$data, $mode = DB_AUTOQUERY_INSERT, $where_stmt = false)
+     {
+         $stmts = $this->autoPrepareMultiple($table, $data, $mode, $where_stmt);
+         if (DB::isError($stmts)) {
+             return $stmts;
+         }
+         return $this->executeMultiple($stmts, $data);
+     }

+

+     // }}}
     // {{{ freePrepared()

     /*

*************** *** 1385,1390 **** --- 1461,1479 ----

     {
         return sprintf($this->getOption("seqname_format"),
                        preg_replace('/[^a-z0-9_]/i', '_', $sqn));
+     }

+

+     // }}}
+     // {{{ _arrayElementOrScalar()

+

+     /**
+     * Returns ith element of array if array else return as scalar.
+     *
+     * @access public
+     */
+     function _arrayElementOrScalar($a, $i)
+     {
+         return is_array($a) ? $a[$i] : $a;
     }

     // }}}

------------------------------------------------------------------------ Index: common.php =================================================================== RCS file: /repository/pear/DB/DB/common.php,v retrieving revision 1.26 diff -c -r1.26 common.php *** common.php 1 Oct 2003 16:48:49 -0000 1.26 --- common.php 13 Nov 2003 23:22:57 -0000 *************** *** 457,462 **** --- 457,497 ----

         return $this->prepare($query);
     }
 +     // }}}
+     // {{{ autoPrepareMultiple()
+ +     /**
+     * Create multiple insert or update queries and call prepare() on each,
+     * the result can be passed onto executeMultiple().
+     *
+     * @param string $table name of the table
+     * @param array $table_fields ordered array of assoc array
+     *              containing the fields names as keys
+     * @param int $mode type of query to make (DB_AUTOQUERY_INSERT or
+                        DB_AUTOQUERY_UPDATE)
+     * @param string $where in case of update queries, this string will
+                            be put after the sql WHERE statement
+     * @return array of resource handles for the query
+     * @see autoPrepare
+     * @access public
+     */
+     function autoPrepareMultiple($table, $data, $mode = DB_AUTOQUERY_INSERT, $where = false)
+     {
+         for ($i = 0; $i < sizeof( $data ); $i++) {
+             if (!is_array($data[$i])) {
+                 return $this->raiseError(DB_ERROR_INVALID);
+             }
+             $stmts[$i] = $this->autoPrepare($this->_arrayElementOrScalar($table, $i),
+                                             array_keys($data[$i]),
+                                             $this->_arrayElementOrScalar($mode, $i),
+                                             $this->_arrayElementOrScalar($where_stmt, $i));
+             if (DB::isError($stmts[$i])) {
+                 return $stmts;
+             }
+         }
+         return $stmts;
+     }
+      // {{{
     // }}} autoExecute()
 ***************

*** 653,660 ****

     /**
     * This function does several execute() calls on the same
!     * statement handle.  $data must be an array indexed numerically
!     * from 0, one execute call is done for every "row" in the array.
     *
     * If an error occurs during execute(), executeMultiple() does not
     * execute the unfinished rows, but rather returns that error.

--- 688,696 ----

     /**
     * This function does several execute() calls on the same
!     * statement handle, or an array of statement handles.  $data must
!     * be an array indexed numerically from 0, one execute call is done
!     * for every "row" in the array.
     *
     * If an error occurs during execute(), executeMultiple() does not
     * execute the unfinished rows, but rather returns that error.

*************** *** 672,678 ****

     function executeMultiple( $stmt, &$data )
     {
         for($i = 0; $i < sizeof( $data ); $i++) {
!             $res = $this->execute($stmt, $data[$i]);
             if (DB::isError($res)) {
                 return $res;
             }

--- 708,718 ----

     function executeMultiple( $stmt, &$data )
     {
         for($i = 0; $i < sizeof( $data ); $i++) {
!             if (is_array($stmt)) {
!                 $res = $this->execute($stmt[$i], array_values($data[$i]));
!             } else {
!                 $res = $this->execute($stmt, $data[$i]);
!             }
             if (DB::isError($res)) {
                 return $res;
             }

*************** *** 681,686 **** --- 721,762 ----

     }
      // }}}
+     // {{{ autoExecuteMultiple()
+ +     /**
+     * Creates insert or update queries for a numerically indexed array
+     * of associative arrays.  Uses the keys of each associative array
+     * to determine the columns in automatically creating query
+     * statements.  All arguments including the data array can be
+     * arrays, but scalars can be used for all variables.
+     *
+     * @param mixed $table string or array of names of the table
+     * @param array $data array of assoc ($key=>$value) where $key is a
+     *                    field name and $value its value
+     * @param mixed $mode int or array of modes representing type of
+     *                    query to make (DB_AUTOQUERY_INSERT or
+     *                    DB_AUTOQUERY_UPDATE)
+     * @param mixed $where string or array of strings for case of
+     *              update queries, each string will be put after its
+     *              respective SQL WHERE statement.
+     * @see autoExecute
+     * @see autoPrepareMultiple
+     * @see executeMultiple
+     * @access public
+     *
+     * @return mixed DB_OK or DB_Error
+     */
+ +     function autoExecuteMultiple($table, &$data, $mode = DB_AUTOQUERY_INSERT, $where_stmt = false)
+     {
+         $stmts = $this->autoPrepareMultiple($table, $data, $mode, $where_stmt);
+         if (DB::isError($stmts)) {
+             return $stmts;
+         }
+         return $this->executeMultiple($stmts, $data);
+     }
+ +     // }}}
     // {{{ freePrepared()
      /*

*************** *** 1385,1390 **** --- 1461,1479 ----

     {
         return sprintf($this->getOption("seqname_format"),
                        preg_replace('/[^a-z0-9_]/i', '_', $sqn));
+     }
+ +     // }}}
+     // {{{ _arrayElementOrScalar()
+ +     /**
+     * Returns ith element of array if array else return as scalar.
+     *
+     * @access public
+     */
+     function _arrayElementOrScalar($a, $i)
+     {
+         return is_array($a) ? $a[$i] : $a;
     }
      // }}}