PHP code example of falseclock / dbd-php

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

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

    

falseclock / dbd-php example snippets

use DBD\Common\Config;
use DBD\Pg;

$config = new Config("127.0.0.1", 5432, "db_name", "user_name", "user_password");

$dbh =  new Pg($config);
$dbh->connect();

/*
... do some stuff
*/

$dbh->disconnect();

resource connect ()

int do ( string $statement [, mixed $params ] )

use DBD\Common\Config;
use DBD\Pg;

$config = new Config("127.0.0.1", 5432, "db_name", "user_name", "user_password");

$db =  new Pg($config);
$db->connect();

// The following example is insecure against SQL injections
$param = "'must be null'";
$result = $db->do("UPDATE table SET column1 = NULL WHERE column2 = $param");

// more easiest, simple and safe for SQL injections example.
// Number of affected tuples will be stored in $result variable
$result = $db->do("UPDATE table SET column1 = ? WHERE column2 = ?", NULL, 'must be null');

resource query ( string $statement [, mixed $params ] )

use DBD\Pg;
/** @var Pg $db */
$sth = $db->query("SELECT * FROM invoices");

while ($row = $sth->fetchRow()) {
	echo($row['invoice_id']);
}

$sth = $db->query("UPDATE invoices SET invoice_uuid = ?",'550e8400-e29b-41d4-a716-446655440000');

echo($sth->affectedRows());

resource prepare ( string $statement )

use DBD\Pg;
/** @var Pg $db */
// Common usage for repeatedly SELECT queries
$sth = $db->prepare("UPDATE table SET column1 = ? WHERE column2 = ?");

$fruits = array('apple','banana','apricot');

foreach ($fruits as $fruit) {
	$sth->execute(NULL,$fruit);
}

/*
this code will execute three statements
UPDATE table SET column1 = NULL WHERE column2 = 'apple';
UPDATE table SET column1 = NULL WHERE column2 = 'banana';
UPDATE table SET column1 = NULL WHERE column2 = 'apricot';
*/

resource execute ( [ mixed $params ] )

use DBD\Pg;
/** @var Pg $db */

// Common usage for repeatedly UPDATE queries
$sth = $db->prepare("SELECT col1, col2, col3 FROM table1");
$std = $db->prepare("UPDATE table2 SET col2 =? WHERE col1=? AND col2=?");

$sth->execute();

while ($row = $sth->fetchRow()) {
	if ($row['col1'] == 'banana') {
    	$std->execute(FALSE, NULL, $row['col2']);
    }
}
/*
this code will execute this statement
UPDATE table2 SET col2 = FALSE WHERE col1 = NULL AND col2 = <value of col2 from table1>;
*/

mixed fetch ()

use DBD\Pg;
/** @var Pg $db */
$sth = $db->prepare("SELECT 'VIR-TEX LLC' AS company, generate_series AS wrh_id, 'Warehouse #'||trunc(random()*1000) AS wrh_name, trunc((random()*1000)::numeric, 2) AS wrh_volume FROM generate_series(1,3)");

/* select result example
   company   | wrh_id |    wrh_name    | wrh_volume
-------------+--------+----------------+------------
 VIR-TEX LLP |      1 | Warehouse #845 |     489.20
 VIR-TEX LLP |      2 | Warehouse #790 |     241.80
 VIR-TEX LLP |      3 | Warehouse #509 |     745.29
*/

$sth->execute();

$company_name = $sth->fetch(); // getting first column
$wrh_id = $sth->fetch(); // getting second column as an example of subsequent invoking
$wrh_name = $sth->fetch(); // getting third column

echo ("Company name: $company_name\n");

while ($row = $sth->fetchRow()) {
	echo("	{$row['wrh_name']} volume {$row['wrh_volume']}\n");
}

/* cycle above will produce following printout
Company name: VIR-TEX LLP
	Warehouse #845 volume: 489.20
	Warehouse #790 volume: 241.80
	Warehouse #509 volume: 745.29
*/

array fetchRow ()

use DBD\Pg;
/** @var Pg $db */
$sth = $db->prepare("SELECT *, 'orange' AS col1, 'apple' AS col2, 'tomato'  AS col3 FROM generate_series(1,3)");
$sth->execute();
print_r($sth->fetchrow());

/* code above will produce following printout
Array
(
    [generate_series] => 1
    [col1] => orange
    [col2] => apple
    [col3] => tomato
)
*/

array fetchRowSet ([ string $key ])

use DBD\Pg;
/** @var Pg $db */
$sth = $db->prepare("SELECT generate_series AS wrh_id, 'Warehouse #'||trunc(random()*1000) AS wrh_name, trunc((random()*1000)::numeric, 2) AS wrh_volume FROM generate_series(1,3)");
$sth->execute();
print_r($sth->fetchRowSet());

/* code above will produce following printout
Array
(
    [0] => Array
        (
            [wrh_id] => 1
            [wrh_name] => Warehouse #795
            [wrh_volume] => 809.73
        )

    [1] => Array
        (
            [wrh_id] => 2
            [wrh_name] => Warehouse #639
            [wrh_volume] => 894.50
        )

    [2] => Array
        (
            [wrh_id] => 3
            [wrh_name] => Warehouse #334
            [wrh_volume] => 13.77
        )

)
*/

$sth->execute();
print_r($sth->fetchRowSet('wrh_name'));

/*
Array
(
    [Warehouse #214] => Array
        (
            [wrh_id] => 1
            [wrh_name] => Warehouse #214
            [wrh_volume] => 462.10
        )

    [Warehouse #563] => Array
        (
            [wrh_id] => 2
            [wrh_name] => Warehouse #563
            [wrh_volume] => 8.88
        )

    [Warehouse #634] => Array
        (
            [wrh_id] => 3
            [wrh_name] => Warehouse #634
            [wrh_volume] => 338.18
        )

)
*/

mixed insert (string $table, array $values [, string $return])

use DBD\Pg;
/** @var Pg $db */
/** @var array $doc */
$record = [
	'invoice_uuid' => $doc['Ref'],
	'invoice_date' => $doc['Date'],
	'invoice_number' => $doc['Number'],
	'invoice_amount' => $doc['Amount'],
	'waybill_uuid' => $doc['reference']['uuid']
];
$sth = $db->insert('vatInvoices',$record);
echo ($sth->affectedRows());

use DBD\Pg;
/** @var Pg $db */
/** @var array $payment */
$record = [
	//'payment_id' => IS SERIAL, will be generated automatically
	'payment_uuid' => $payment['Ref'],
	'payment_date' => $payment['Date'],
	'payment_number' => $payment['Number'],
	'payment_amount' => $payment['Amount']
];

$sth = $db->insert('payments', $record, 'payment_id, payment_uuid');

while ($row = $sth->fetchrow()) {
	printf("We inserted new payment with ID=%d and UUID=%s\n",$row['payment_id'],$row['payment_uuid']);
}

mixed update (string $table, array $values [, mixed $where..., [ mixed $args...], [string $return]])

use DBD\Pg;
/** @var Pg $db */
/** @var array $doc */
$update = [
	'invoice_date' => $doc['Date'],
	'invoice_number' => $doc['Number'],
	'invoice_amount' => $doc['Amount']
];
/* this will update all rows in a table */
$sth = $db->update('invoices',$update);
echo ($sth->affectedRows());

use DBD\Pg;
/** @var Pg $db */
/** @var array $doc */
$update = [
	'invoice_date' => $doc['Date'],
	'invoice_number' => $doc['Number'],
	'invoice_amount' => $doc['Amount']
];
/* this will update all rows in a table where vat_invoice_uuid equals to some value */
$sth = $db->update('vat_invoices', $update, "vat_invoice_uuid=?", $doc['UUID']);
echo ($sth->affectedRows());

use DBD\Pg;
/** @var Pg $db */
/** @var array $doc */
$update = array(
	'vatinvoice_date' => $doc['Date'],
	'vatinvoice_number' => $doc['Number'],
	'vatinvoice_amount' => $doc['Amount']
);
/* 
this will update all rows in a table where vatinvoice_uuid is null
query will return vatinvoice_id
*/
$sth = $db->update('vatinvoices', $update, "vatinvoice_uuid IS NULL", "vatinvoice_id");
while ($row = $sth->fetchRow()) {
	printf("Updated vatinvoice with ID=%d\n", $row['vatinvoice_id']);
}

use DBD\Pg;
/** @var Pg $db */
/** @var array $doc */
$update = array(
	'vatinvoice_date' => $doc['Date'],
	'vatinvoice_number' => $doc['Number'],
	'vatinvoice_amount' => $doc['Amount']
);
// this will update all rows in a table where vatinvoice_uuid equals to some value
// query will return vatinvoice_id
$sth = $db->update('vatinvoices',$update,"vatinvoice_uuid =? ", $doc['UUID'], "vatinvoice_id, vatinvoice_uuid");
while ($row = $sth->fetchRow()) {
	printf("Updated vatinvoice with ID=%d and UUID=%s\n",$row['vatinvoice_id'],$row['vatinvoice_uuid']);
}

mixed begin ()

use DBD\Pg;
/** @var Pg $db */

$db->begin();

// Common usage for repeatedly UPDATE queries
$sth = $db->prepare("SELECT col1, col2, col3 FROM table1");
$std = $db->prepare("UPDATE table2 SET col2 =? WHERE col1=? AND col2=?");

$sth->execute();

while ($row = $sth->fetchrow()) {
	if ($row['col1'] == 'banana') {
    	$std->execute(FALSE,NULL,$row['col2']);
    }
}
$db->commit();

mixed commit ()

mixed rollback ()

mixed cache ()