PostgreSQL in PHP

Summary

PostgreSQL is LoveTech's database of choice for most of our deployments due to its world class excellence and unbeatable price tag(FREE!).

We've built a convenience library in PHP for common database operations and are documenting it here and releasing it to the public for free.

Pay it forward, our friends

Example

$db = new Database($psqlHost, $psqlPort, $psqlDatabase, $psqlUser, $psqlPwd);

echo json_encode($db->GetTableList());

$db->close();

Download

PostgreSQL PHP Library

CONNECTION FUNCTIONS

$db->connect()

Initializes the PostgreSQL connection. Called automatically by constructor

$db->close()

Used to close the PostgreSQL connection when finished with querying

DATA QUERYING

$db->Get($table, $options)

Gets a single result from a database query

$db->GetOneResult($query, $params, $fields, $option)

Gets a single result from a database query

$db->GetAllResults($query, $params, $fields, $options)

Gets all results from a database query

$db->GetOneResultNested($query, $params, $fields)

Gets a single result from a database query with nested sub-objects embedded

$db->GetAllResultsNested($query, $params, $nestingStructure)

Gets all results from a with embedded noded. For example, client_id field can spawn client node with title

$db->UpsertOne($table, $fields, $options)

Upserts a single object/ document

$db->Insert($table, $fields, $options)

Inserts a single object/ document

$db->Update($table, $fields, $options)

Updates all object/ document that match the constraint

$db->UpsertMulti($table, $records, $options)

Upserts a series of objects/ document

$db->UpsertOneNested($table, $dataObj, $nestingStructure, $options)

Upserts a nested object/ document

$db->UpsertMultiNested($table, $dataObjs, $nestingStructure, $options)

Upserts multiple nested objects/ documents

$db->RunQuery($query)

Runs a PostgreSQL query directly against the database. Ideal for CREATE or ALTER TABLES, UPDATE, or INSERT statements

$db->RunQueryParams($query, $params)

Runs a PostgreSQL query directly against the database using the $1, $2, etc placeholder notation and query params array, $params. Ideal for UPDATE, or INSERT statements with escaping special characters

ASSOCIATION TABLES

$db->DoesAssociationExist($mappingTable, $propDefs, $associationToCheck)

Check if the objects/ documents are associated as specified

$db->GetAllAssociations($mappingTable, $propDefs, $matchRules)

Get all of the associations in accordance with the provided rules

$db->EnsureAssociations($mappingTable, $propDefs, $associations, $exhaustive)

Ensures that the specified associations exist. If $exhaustive is passed as true, all non referenced associations will be removed

$db->RemoveAssociations($mappingTable, $propDefs, $associationsToDelete)

Removes the associations specified in $associationsToDelete

$db->RemoveAllAssociations($mappingTable, $removeRules)

Removes the associations specified using the remove rules

TABLES AND THEIR STRUCTURES

$db->GetTableList()

Gets a list of all tables in the connected to database

$db->GetTableStructure($table)

Gets the column definitions of the table

$db->GetAllTableStructures()

Gets the column definitions of all tables in the connected to database

QUERY UTILITY FUNCTIONS

$db->prepResult($dataObj, $fields)

After data is fetched from the database, this function aligns all of the variable types to match the field definitions

$db->getFieldList($fields)

Used to dynamically generate PostgreSQL SELECT query field lists

$db->getWhereClauseFromQueryArray($query)

Used to dynamically generate PostgreSQL SELECT and UPDATE query WHERE clauses

$db->isMultiTableQuery($fields)

Used to determine if two or more tables are involved in the query

$db->doesTableExist($table)

Used to determine if the table exists

$db->getCurrentConnectDBName()

Returns the name of the database that the PHP5 PostgreSQL driver is currently connected to

DoesDatabaseExist($databaseName)

Checks if a PostgreSQL database with this name exists

Download

PostgreSQL PHP Library

LoveTech's PostgreSQL PHP Library