phlib/db-helper

DB helpers to complement phlib/db

phlib/db-helper

, _(*1)

DB helpers to complement phlib/db, _(*2)

Installation

composer require phlib/db-helper

Usage

// Get an Adapter
$config = [
    'host' => 'localhost',
    'username' => 'myuser',
    'password' => 'mypassword',
    'dbname' => 'mydatabase'
];
$db = new \Phlib\Db\Adapter($config);

BulkInsert

Insert and/or update many rows into a table., _(*3)

This increases the write performance for adding large numbers (eg. thousands+) of rows, over the typical pseudo-prepared statements used by native PDO., _(*4)

$insertFields = [
    'product_id',
    'product_name',
    'product_qty'
];
$updateFields = [
    'product_name',
    'product_qty'
];
$bulkInsert = new BulkInsert($adapter, 'product', $insertFields, $updateFields);

// Many calls to add() will write to the DB in batches
$bulkInsert->add($singleProductData);

// One final manual call to write() to complete
$bulkInsert->write();

QueryPlanner

Test the number of rows that a SELECT statement will query., _(*5)

$queryPlanner = new QueryPlanner($adapter, $sqlSelect);
$queryPlanner->getNumberOfRowsInspected(); // eg. 46234

BigResult

Run a SELECT statement which is expected to be slow (eg. >5s) due to quantity of data., _(*6)

Query buffering is disabled to reduce the time to first row and avoid consuming PHP's memory for the statement result, and MySQL's query timeout is increased., _(*7)

$bigResult = new BigResult($adapter);
$pdoStmt = $bigResult->query($sqlSelect, $bind);

Optionally, prevent very large queries from running by using the QueryPlanner inspection:, _(*8)

$bigResult = new BigResult($adapter);
$queryRowLimit = 20000000;
$pdoStmt = $bigResult->query($sqlSelect, $bind, $queryRowLimit);

License

This package is free software: you can redistribute it and/or modify it under the terms of the GNU Lesser General Public License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later version., _(*9)

This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public License for more details., _(*10)

You should have received a copy of the GNU Lesser General Public License along with this program. If not, see http://www.gnu.org/licenses/., _(*11)