PHP code example of pontikis / dacapo

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

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

    

pontikis / dacapo example snippets

$sql = 'SELECT procuct_name FROM products WHERE manufacturer = ? and type IN (?,?,?)';

use Pontikis\Database\Dacapo;
use Pontikis\Database\DacapoErrorException;

$db_settings = [
	'rdbms' => Dacapo::RDBMS_POSTGRES, // or Dacapo::RDBMS_MYSQLI for MySQL/MariaDB
	'db_server' => 'localhost',
	'db_user' => 'foo',
	'db_passwd' => 'bar',
	'db_name' => 'baz',
];

try {
	$ds = new Dacapo($db_settings);	
} catch (Exception $e) {
	// your code here
}

$sql = 'SELECT id, firstname, lastname FROM customers WHERE lastname LIKE ?';
$bind_params = ['%' . $str . '%'];
try {
	$ds->select($sql, $bind_params);
	$customers = $ds->getData();
} catch (DacapoErrorException $e) {
	// your code here
}

if($ds->getNumRows() > 0) {
	foreach($customers as $customer) {
		$id = $customer['id'];
		$lastname = $customer['lastname'];
		$firstname = $customer['firstname'];
	}	
}

$sql = 'SELECT firstname, lastname FROM customers WHERE id = ?';
$bind_params = [$id];
try {
	$ds->select($sql, $bind_params);
	if(1 === $ds->getNumRows()) {
		$customer = $ds->getRow();		
		$firstname = $customer['firstname'];
		$lastname = $customer['lastname'];
	}
} catch (DacapoErrorException $e) {
	// your code here
}

$sql = 'INSERT INTO customers (firstname, lastname) VALUES (?,?)';
$bind_params = [$firstname, $lastname];
try {
	$ds->insert($sql, $bind_params);
	$new_customer_id = $ds->getInsertId();
} catch (DacapoErrorException $e) {
	// your code here
}

$sql = 'UPDATE customers SET category = ? WHERE balance > ?';
$bind_params = [$category, $balance];
try {
	$ds->update($sql, $bind_params);
	$affected_rows = $ds->getAffectedRows();
} catch (DacapoErrorException $e) {
	// your code here
}

$sql = 'DELETE FROM customers WHERE category = ?';
$bind_params = [$category];
try {
	$ds->delete($sql, $bind_params);
	$affected_rows = $ds->getAffectedRows();	
} catch (DacapoErrorException $e) {
	// your code here
}

try {
	$ds->beginTrans();

	// delete from customers
	$sql = 'DELETE FROM customers WHERE id = ?';
	$bind_params = [$customers_id];
	$ds->delete($sql, $bind_params);

	// delete from demographics
	$sql = 'DELETE FROM demographics WHERE id = ?';
	$bind_params = [$customer_demographics_id];
	$ds->delete($sql, $bind_params);

	$ds->commitTrans();
} catch (DacapoErrorException $e) {
	$ds->rollbackTrans();
	// your code here
}

// check for unique username (CASE IN-SENSITIVE)
$sql = "SELECT count('id') as total_rows FROM users WHERE {$ds->lower('username')} = ?";
$bind_params = [mb_strtolower($username)];
$ds->select($sql, $bind_params);
if($ds->getNumRows() > 0) {
	echo 'Username in use...';
}

$limitSQL = $ds->limit($rows_per_page, ($page_num - 1) * $rows_per_page);

./vendor/bin/phpunit --configuration tests/phpunit.xml tests/MySQLTest.php

./vendor/bin/phpunit --enforce-time-limit --configuration tests/phpunit.xml tests/MySQLTest.php

./vendor/bin/phpunit --configuration tests/phpunit.xml tests/MySQLCUDTest.php

./vendor/bin/phpunit  --configuration tests/phpunit.xml tests/PostgresqlTest.php

./vendor/bin/phpunit  --configuration tests/phpunit.xml tests/PostgresqlCUDTest.php

./vendor/bin/phpunit  --configuration tests/phpunit.xml tests/PostgresqlTest.php --filter '/testConnectFails1$/'