Home

Awesome

CSDB Data Access Layer

A PHP library that provides a portable, cross-platform, cross-database, lightweight, debuggable, replication-aware, migration-friendly, transaction-capable, data access layer (DAL) for PHP.

A collection of useful, lightweight classes intended to be dropped into projects that need database access. CSDB wraps the PHP PDO layer with useful functionality for cross-database application development. Minimal dependencies allows for only including the files needed for a project.

Donate Discord

Features

Basic Usage

Connecting to a database:

<?php
	// Replace '_mysql' references below with the correct database class and use the relevant Connect() call.
	require_once "support/db.php";
	require_once "support/db_mysql.php";

	try
	{
		$db = new CSDB_mysql();

		// Enable debug mode for testing only.
//		$db->SetDebug(fopen("out.txt", "wb"));

		// MySQL/Maria DB.
		$db->Connect("mysql:host=127.0.0.1", "username", "*******");
//		$db->SetMaster("mysql:host=remotehost", "username", "*******");

		// Postgres.
		$db->Connect("pgsql:host=127.0.0.1", "username", "*******");
//		$db->SetMaster("pgsql:host=remotehost", "username", "*******");

		// SQLite.
		$db->Connect("sqlite:/var/path/to/sqlite.db");

		// Oracle.
		$db->Connect("oci:dbname=//localhost/ORCL", "username", "*******");
//		$db->SetMaster("oci:dbname=//remotehost/ORCL", "username", "*******");

		// Assumes 'testdb' exists.
		$db->Query("USE", "testdb");
	}
	catch (Exception $e)
	{
		echo "Unable to connect to the database.  " . htmlspecialchars($e->getMessage()) . "\n";
		exit();
	}
?>

Standard queries:

<?php
	try
	{
		// SELECT multiple rows.
		$result = $db->Query("SELECT", array(
			"*",
			"FROM" => "?",
			"WHERE" => "created >= ?",
			"ORDER BY" => "id DESC",
			"LIMIT" => 100
		), "app_users", CSDB::ConvertToDBTime(time() - 3 * 24 * 60 * 60));

		while ($row = $result->NextRow())
		{
			echo $row->id . " | " . $row->user . " | " . $row->email . " | " . date("M, j Y @ H:i", CSDB::ConvertFromDBTime($row->created)) . "\n";
		}

		// SELECT one row.
		$row = $db->GetRow("SELECT", array(
			"*",
			"FROM" => "?",
			"WHERE" => "user = ?",
		), "app_users", $_REQUEST["username"]);

		// SELECT the first column of the first row.
		$row = (int)$db->GetOne("SELECT", array(
			"COUNT(*) AS c",
			"FROM" => "?",
		), "app_users");

		// Transaction support.
		$db->BeginTransaction();

		// INSERT row.
		$db->Query("INSERT", array("app_users", array(
			"user" => $_REQUEST["username"],
			"email" => $_REQUEST["email"],
			"created" => CSDB::ConvertToDBTime(time()),
			"updated" => CSDB::ConvertToDBTime(time()),
		), "AUTO INCREMENT" => "id"));

		$id = $db->GetInsertID();

		// UPDATE row.
		$db->Query("UPDATE", array("app_users", array(
			"email" => $_REQUEST["email"],
			"updated" => CSDB::ConvertToDBTime(time()),
		), "WHERE" => "id = ?"), $id);

		// DELETE row.
		$db->Query("DELETE", array("app_users", "WHERE" => "id = ?"), $id);

		// Finalize transaction.
		$db->Commit();
	}
	catch (Exception $e)
	{
		// Rollback transaction.
		$db->Rollback();

		echo "An error occurred while running a database query.  " . htmlspecialchars($e->getMessage()) . "\n";
		exit();
	}
?>

Creating a database and tables (e.g. for an installer):

<?php
	// Create/Use the database.
	try
	{
		$db->Query("USE", "testdb");
	}
	catch (Exception $e)
	{
		try
		{
			$db->Query("CREATE DATABASE", array("testdb", "CHARACTER SET" => "utf8", "COLLATE" => "utf8_general_ci"));
			$db->Query("USE", "testdb");
		}
		catch (Exception $e)
		{
			echo "Unable to create/use database 'testdb'.  " . htmlspecialchars($e->getMessage()) . "\n";
			exit();
		}
	}

	// Create missing database tables.
	try
	{
		$appusersfound = $db->TableExists("app_users");
	}
	catch (Exception $e)
	{
		echo "Unable to determine the existence of a database table.  " . htmlspecialchars($e->getMessage()) . "\n";
		exit();
	}

	if (!$appusersfound)
	{
		try
		{
			$db->Query("CREATE TABLE", array("app_users", array(
				"id" => array("INTEGER", 8, "UNSIGNED" => true, "NOT NULL" => true, "PRIMARY KEY" => true, "AUTO INCREMENT" => true),
				"user" => array("STRING", 1, 255, "NOT NULL" => true),
				"email" => array("STRING", 1, 255, "NOT NULL" => true),
				"created" => array("DATETIME", "NOT NULL" => true),
				"updated" => array("DATETIME", "NOT NULL" => true),
			),
			array(
				array("UNIQUE", array("user"), "NAME" => "app_users_user"),
				array("UNIQUE", array("email"), "NAME" => "app_users_email"),
				array("KEY", array("created"), "NAME" => "app_users_created"),
			)));
		}
		catch (Exception $e)
		{
			echo "Unable to create the database table 'app_users'.  " . htmlspecialchars($e->getMessage()) . "\n";
			exit();
		}
	}
?>

Remember that PHP function and method calls are case-insensitive. So it's fine if you prefer connect(), query(), nextRow(), getRow(), etc. Just be consistent.

More Information

Full documentation and examples can be found in the 'docs' directory of this repository.