Configurable table class

pritaeas 4 451 Views

In addition to my post in this thread I've decided to post an extended example, which also implements functions for inserts and updates. I hope the code is straightforward enough. If not, reply and let me know.

The table structure I've used for my test is the following:

CREATE TABLE `contacts` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(64) NOT NULL,
  `password` varchar(64) NOT NULL,
  `first_name` varchar(64) DEFAULT NULL,
  `last_name` varchar(64) DEFAULT NULL,
  `email` varchar(64) NOT NULL,
  PRIMARY KEY (`id`)
)

And finally, here is how to use it:

<?php
# Definition of my table structure
$tableConfig = array (
    'contacts' => array (
        'id' => PDO::PARAM_INT,
        'username' => PDO::PARAM_STR,
        'password' => PDO::PARAM_STR,
        'first_name' => PDO::PARAM_STR,
        'last_name' => PDO::PARAM_STR,
        'email' => PDO::PARAM_STR
    )
);

# Create a class instance
include 'MyDB.class.php';
$myDB = new MyDB($tableConfig);

# Create a data array for insert
$newContact = array (
    'username' => 'pritaeas',
    'password' => 'XXX',
    'first_name' => 'Hans'
);
$id = $myDB->insert('contacts', $newContact);

# Create a data array for an update
$updContact = array (
    'last_name' => 'Pollaerts',
    'email' => 'pritaeas@example.com'
);
$myDB->update('contacts', $updContact, array ('id' => $id));

# Show the recently inserted and updated record
print_r($myDB->getRecord('contacts', array ('id' => $id)));
?>

Finally: a lot of room for improvement. Sanity checks, error checking, better/more where conditions, defining joins, extend configuration to allow table creation, and a lot more... I'll leave that to you.

Squidge commented: amazing :) +6 
<?php
	class MyDB {
		protected $configuration;
		protected $pdo;

		/**
		 * MyDB constructor
		 * @param array $tableConfig Configuration data for the database tables
		 */
		public function __construct($tableConfig) {
			$this->configuration = $tableConfig;
			$this->pdo = new PDO('mysql:host=localhost;dbname=test', 'root', '');
		}

		/**
		 * getRecord returns a single record from the specified table with conditions
		 * @param string $table Table name
		 * @param array $conditions Conditions to use
		 * @return array Zero or one record from the specified table
		 */
		public function getRecord($table, $conditions = array ()) {
			return $this->getRecords($table, $conditions, 1);
		}

		/**
		 * getRecords returns (limited) records from the specified table with conditions
		 * @param string $table Table name
		 * @param array $conditions Conditions to use
		 * @param int $limit Number of result to return, 0 for all
		 * @return array Zero or more records from the specified table
		 */
		public function getRecords($table, $conditions = array (), $limit = 0) {
			$result = array ();

			$fields = implode(',', array_keys($this->configuration[$table]));
			$query = "SELECT $fields FROM $table ";

			$where = array ();
			foreach ($conditions as $column => $value) {
				$where[] = "$column = :$column";
			}
			if (count($where) > 0) {
				$where = implode(' AND ', $where);
				$query .= "WHERE $where ";
			}

			if ($limit > 0) {
				$query .= "LIMIT $limit";
			}

			$statement = $this->pdo->prepare($query);
			foreach ($conditions as $column => $value) {
				$statement->bindValue(":$column", $value, $this->configuration[$table][$column]);
			}
			if ($statement->execute()) {
				if ($row = $statement->fetchAll()) {
					$result = $row;
				}
			}

			return $result;
		}

		/**
		 * @param string $table Table name
		 * @param array $data Array with columns and values
		 * @return mixed Last inserted ID if insert succeeded, false otherwise
		 */
		public function insert($table, $data) {
			$result = false;

			$columns = array ();
			$values = array ();
			foreach ($data as $column => $value) {
				$columns[] = $column;
				$values[] = ":$column";
			}
			$columns = implode(',', $columns);
			$values = implode(',', $values);
			$query = "INSERT INTO $table ($columns) VALUES ($values)";

			$statement = $this->pdo->prepare($query);
			foreach ($data as $column => $value) {
				$statement->bindValue(":$column", $value, $this->configuration[$table][$column]);
			}

			if ($statement->execute()) {
				$result = $this->pdo->lastInsertId();
			}
			return $result;
		}

		/**
		 * @param string $table Table name
		 * @param $data Array with columns and values
		 * @param array $conditions Conditions to use
		 * @return int Number of affected rows
		 */
		public function update($table, $data, $conditions = array ()) {
			$result = 0;

			$query = "UPDATE $table SET ";

			$columns = array ();
			foreach ($data as $column => $value) {
				$columns[] = "$column = :$column";
			}
			$columns = implode(',', $columns);
			$query .= "$columns ";

			$where = array ();
			foreach ($conditions as $column => $value) {
				$where[] = "$column = :w$column";
			}
			if (count($where) > 0) {
				$where = implode(' AND ', $where);
				$query .= "WHERE $where ";
			}

			$statement = $this->pdo->prepare($query);
			foreach ($data as $column => $value) {
				$statement->bindValue(":$column", $value, $this->configuration[$table][$column]);
			}
			foreach ($conditions as $column => $value) {
				$statement->bindValue(":w$column", $value, $this->configuration[$table][$column]);
			}

			if ($statement->execute()) {
				$result = $statement->rowCount();
			}
			return $result;
		}
	}
?>
Member Avatar for extjac
extjac 0 Newbie Poster

very nice! Thanks!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

Edit Preview