PHP code example of vielhuber / dbhelper

    
        
<?php
require_once('vendor/autoload.php');

/* Start to develop here. Best regards https://php-download.com/ */

    

vielhuber / dbhelper example snippets

use vielhuber\dbhelper\dbhelper;
$db = new dbhelper();

/* connect to database */
$db->connect('pdo', 'mysql', '127.0.0.1', 'username', 'password', 'database', 3306);
$db->connect('pdo', 'postgres', '127.0.0.1', 'username', 'password', 'database', 5432);
$db->connect('pdo', 'sqlite', 'database.db');
$db->connect('pdo', 'sqlite', 'database.db', null, null, null, null, 120); // specify a manual timeout of 120 seconds
$db->connect('pdo', 'mysql', '127.0.0.1', 'username', 'password', null, 3306); // database must not be available

/* disconnect from database */
$db->disconnect();

/* insert/update/delete */
$id = $db->insert('tablename', ['col1' => 'foo']);
$db->update('tablename', ['col1' => 'bar'], ['id' => $id]);
$db->delete('tablename', ['id' => $id]);

/* select */
$db->fetch_all('SELECT * FROM tablename WHERE name = ? AND number > ?', 'foo', 42);
$db->fetch_row('SELECT * FROM tablename WHERE ID = ?', 1);
$db->fetch_col('SELECT col FROM tablename WHERE ID > ?', 1);
$db->fetch_var('SELECT col FROM tablename WHERE ID = ?', 1);

/* count */
$db->count('tablename') // 42
$db->count('tablename', ['col1' => 'foo']) // 7

/* automatic flattened arguments */
$db->fetch_all('SELECT * FROM tablename WHERE ID = ?', [1], 2, [3], [4,[5,6]]);
// gets transformed to
$db->fetch_all('SELECT * FROM tablename WHERE ID = ?', 1, 2, 3, 4, 5, 6);

/* automatic in-expansion */
$db->fetch_all('SELECT * FROM tablename WHERE col1 = ? AND col2 IN (?)', 1, [2,3,4]);

/* support for null values */
$db->query('UPDATE tablename SET col1 = ? WHERE col2 = ? AND col3 != ?', null, null, null);
// gets transformed to
$db->query('UPDATE tablename SET col1 = NULL WHERE col2 IS NULL AND col3 IS NOT NULL');

/* clean up */
$db->clear(); // delete all tables (without dropping the whole database)
$db->clear('tablename'); // delete all rows in a table

/* delete table */
$db->delete_table('tablename');

/* create table */
$db->create_table('tablename', [
    'id' => 'SERIAL PRIMARY KEY', // use INTEGER instead of SERIAL on sqlite to get auto ids
    'col1' => 'varchar(255)',
    'col2' => 'varchar(255)',
    'col3' => 'varchar(255)'
]);

/* create if not exists and connect to database */
$db->connect_with_create('pdo', 'mysql', '127.0.0.1', 'username', 'password', 'database', 3306);
    // this is a shorthand for
    $db->connect('pdo', 'mysql', '127.0.0.1', 'username', 'password', null, 3306);
    $db->create_database('database');
    $db->disconnect();
    $db->connect('pdo', 'mysql', '127.0.0.1', 'username', 'password', 'database', 3306);

/* delete database */
$db->disconnect_with_delete();
    // this is a shorthand for
    $db->disconnect();
    $db->connect('pdo', 'mysql', '127.0.0.1', 'username', 'password', null, 3306);
    $db->delete_database('database');
    $db->disconnect();

/* raw queries */
$db->query('INSERT INTO tablename(row1, row2) VALUES(?, ?, ?)', 1, 2, 3);
$db->query('UPDATE tablename SET row1 = ? WHERE ID = ?', 1, 2);
$db->query('DELETE FROM tablename WHERE ID = ?', 1);

/* quickly debug raw queries */
$db->debug('DELETE FROM tablename WHERE row1 = ?', null); // DELETE FROM tablename WHERE row1 IS NULL

/* last insert id */
$db->insert('tablename', ['col1' => 'foo']);
$db->last_insert_id();

/* some more little helpers */
$db->get_tables() // ['tablename', ...]
$db->has_table('tablename') // true
$db->get_columns('tablename') // ['col1', 'col2', ...]
$db->has_column('tablename', 'col1') // true
$db->get_datatype('tablename', 'col1') // varchar
$db->get_primary_key('tablename') // id
$db->uuid() // generate uuid (v4) from inside the database
$db->get_foreign_keys('users') // [['address_id' => ['addresses','id'], ...]
$db->is_foreign_key('users', 'address_id') // true
$db->get_foreign_tables_out('users') // [['addresses' => [['address_id','id']], ...]
$db->get_foreign_tables_in('addresses') // [['users' => [['address_id','id']], ...]

/* handle duplicates */
$db->get_duplicates() // ['count' => ['tbl1' => 3, 'tbl2' => 17], 'data' => ['tbl1' => [...], 'tbl2' => [...]]
$db->delete_duplicates('tablename') // delete duplicates based on all columns except the primary key
$db->delete_duplicates('tablename', ['common_col1','common_col1','common_col1']) // based on specific columns
$db->delete_duplicates('tablename', ['common_col1','common_col1','common_col1'], false) // null values are considered equal by default; you can disable this untypical behaviour for sql with "false"
$db->delete_duplicates('tablename', ['common_col1','common_col1','common_col1'], true, ['id' => 'asc']) // keep row with lowest primary key "id" (normally this is 'id' => 'desc')
$db->delete_duplicates('tablename', ['common_col1','common_col1','common_col1'], true, ['id' => 'asc'], false) // case insensitive match (normally this is case sensitive)

/* globally trim values */
$db->trim_values() // [['table' => 'tbl1', 'column' => 'col1', 'id' => 1, 'before' => ' foo', 'after' => 'foo'], ...]
$db->trim_values(false) // by default trim_values does a dry run (no updates)
$db->trim_values(true) // do real updates
$db->trim_values(false, ['table1', 'table2' => ['col1', 'col2']]) // ignore tables and columns

/* batch functions (they create only one query) */
$db->insert('tablename', [
    ['id' => 1, 'name' => 'foo1'],
    ['id' => 2, 'name' => 'foo2'],
    ['id' => 3, 'name' => 'foo3']
]);
$db->delete('tablename', [
    ['id' => 1],
    ['id' => 7],
    ['id' => 42]
]);
$db->update('tablename', [
    [['col1' => 'var1', 'col2' => 1], ['id' => 1, 'key' => '1']],
    [['col1' => 'var2', 'col2' => 2], ['id' => 2, 'key' => '2']],
    [['col1' => 'var3', 'col2' => 3], ['id' => 3, 'key' => '3']]
]);
/*
this generates the following query:
UPDATE tablename SET
col1 = CASE WHEN (id = 1 AND key = '1') THEN 'var1' WHEN (id = 2 AND key = '2') THEN 'var2' WHEN (id = 3 AND key = '3') THEN 'var3' END,
col2 = CASE WHEN (id = 1 AND key = '1') THEN 1 WHEN (id = 2 AND key = '2') THEN 2 WHEN (id = 3 AND key = '3') THEN 3 END
WHERE id IN (1,2,3) AND key IN ('1','2','3');
*/

$db = new dbhelper([
    'logging_table' => 'logs',
    'exclude' => [
        'tables' => ['table1'],
        'columns' => ['table2' => ['col1', 'col2', 'col3']]
    ],
    'delete_older' => 12, // months
    'updated_by' => get_current_user_id()
]);
$db->connect('...');
$db->setup_logging();

$db->insert('tablename', ['col1' => 'foo', 'updated_by' => get_current_user_id()]);

$db->update('tablename', ['col1' => 'foo', 'updated_by' => get_current_user_id()], ['id' => 42]);

$db->insert('logs', [
    'log_event' => 'delete',
    'log_table' => 'tablename',
    'log_key' => 42,
    'log_uuid' => $db->uuid(),
    'updated_by' => get_current_user_id()
]);
$db->delete('tablename', ['id' => 42]);

$db->enable_auto_inject();

$db->disable_logging();
$db->query('DELETE * FROM mega_big_table');
$db->enable_logging();

$db->connect('wordpress');
$db->fetch_var('SELECT col FROM tablename WHERE ID = ?', 1);

$db = new dbhelper();
sql', '127.0.0.1', 'username', 'password', 'database', 3306);
db_fetch_var('SELECT col FROM tablename WHERE ID = ?', 1);