Version: 2.0.0

Object-Oriented Database Access

The dbObject class is used to model database tables. In this approach, a corresponding class is defined for each table. The column names are the keys of an array, and each value holds information about the column, such as its type and whether it is required and unique.

The advantage of this approach over direct interaction is that it is object-oriented and makes the type, the required constraint, and the unique constraint of each column explicit. It prevents repeatedly re-checking column values: if a column value conflicts with what has been specified, or a value is not provided for a required column, or the value of a unique column is duplicated, this is checked automatically before the request is sent to the database, and the process is halted by throwing the exceptions packages\base\db\InputRequired, packages\base\db\InputDataType, and packages\base\db\duplicateRecord respectively.

The users Table Class

<?php
namespace packages\packagename;
use packages\base\db\dbObject;
class user extends dbObject {
    protected $dbTable = "users";
    protected $primaryKey = "id";
    protected $dbFields = array(
        "firstname" => array("type" => "text", "required" => true),
        "lastname" => array("type" => "text"),
        "email" => array("type" => "text", "unique" => "true"),
        "cellphone" => array("type" => "text", "required" => true, "unique" => "true"),
        "password" => array("type" => "text", "required" => true),
        "city" => array("type" => "int"),
        "status" => array("type" => "int", "required" => true)
    );
    protected $relations = array(
        "city" => array("hasOne", state\city::class, "type")
    );
}

The get Method

Using this method, you can fetch data in bulk from the database. The return type of this method is an array of objects corresponding to the requested table. The first parameter takes the number of rows, and the second parameter takes the name of the column or columns requested from the table.

$user = new user();
$user->get(limit, columns);

user::get(limit, columns);

Example 1

$user = new user();
$users = $user->get(); // or user::get()
foreach ($users as $user) {
	print_r($user->toArray());
}
/* output
array(
	"id"=> 1,
	"firstname" => "Sam smith",
	"email" => "alex@smith.com"
);
array(
	"id"=> 2,
	"firstname" => "John",
	"email" => "John@some.com"
);
*/

Example 2

$user = new user();
$users = $user->get(null, array("firstname")); // or user::get(null, array("firstname"))
foreach ($users as $user) {
	echo $user->firstname;
}
/* output
Sam smith
John
*/

The getOne Method

Using this method, you can fetch the data of only one row from the database. The return type of this method is an object of the corresponding class. This method takes the name of the column or columns requested from the table as its parameter.

$user = new user();
$user = $user->getOne(columns);

Example 1

$user = new user();
$user = $user->getOne();
echo $user->id;
echo $user->firstname;
echo $user->email;
/* output
1
Sam smith
alex@smith.com
*/

Example 2

$user = new user();
$user = $user->getOne(array("firstname", "email"));
echo $user->firstname;
echo $user->email;
/* output
Sam smith
alex@smith.com
*/

The getValue Method

This method fetches only the value of a single table column from the database. Depending on the number of returned values, the return type of this method can be a single scalar value or an array, and the value type differs based on the column type. This method takes only the column name as its single parameter.

$user = new user();
$user->getValue(column);

Example 1

$user = new user();
$count = $user->getValue("count(*)");
echo "{$count} users found";

$user = new user();
$username = $user->getValue("firstname");
echo "firstname is : {$username}";

The where Method

Using this method, you can fetch only the rows of a table that match the condition stated in this method. The first parameter of this method takes the table column name, the second parameter takes the condition value, the third parameter takes the operator relating the column to its value, and the fourth parameter specifies how this condition relates to the other conditions.

$user = new user();
$user->where(column, value, operator, cond);

Example 1

$user = new user();
$user->where("id", 1);
$user = $user->getOne(array("firstname"));
/* output
Sam smith
*/

Example 2

$user = new user();
$user->where("id", 1);
$user->where("firstname", "John", "=", "OR");
$users = $user->get();
foreach ($users as $user) {
	echo $user->id;
	echo $user->firstname;
	echo $user->email;
}
/* output
1
Sam smith
alex@smith.com

2
John
John@some.com
*/

Example 3

$user = new user();
$user->where("id", 2, ">=");
$users = $user->get(array("id", "firstname"));
foreach ($users as $user) {
	echo $user->id;
	echo $user->firstname;
}
/* output
2
John
*/

The byId Method

This method is used to fetch a single row with the specified identifier. It can be reproduced by combining the where and getOne methods.

$user = new user();
$user->byId(id);

user::byId(id);

Example

$user = new user();
$user->where("id", 1);
$user = $user->getOne();
echo $user->firstname; // Sam smith

$user = user::byId(1);
echo $user->firstname; // Sam smith

The orWhere Method

This method is equivalent to the where method with the value OR in its fourth parameter.

$user = new user();
$user->orWhere(column, value, operator);

Example

$user = new user();
$user->where("id", 1);
$user->orWhere("firstname", "John", "=");
$users = $user->get();
foreach ($users as $user) {
	echo $user->id;
	echo $user->firstname;
	echo $user->email;
}
/* output
1
Sam smith
alex@smith.com

2
John
John@some.com
*/

The save Method

When you call this method, if the data was previously fetched from the database, the changes are updated in the database; otherwise, a new row is created in the corresponding table with the data. The output of this method is true if the data is successfully updated or saved in the database, and false otherwise.

$user = new user();
$user->save();

Example 1

$user = new user();
$user->where("id", 1);
$user = $user->getOne();
echo $user->firstname; // Sam smith
$user->firstname = "something else";
$user->save();
// update users set firstname = "something else" where id = 1
echo $user->firstname; // something else

Example 2

$user = new user();
$user->firstname = "Jack";
$user->email = "Jack@email.com";
$user->save();
// insert new user with Jack for firstname

The delete Method

This method is used to delete a table row. If no condition is set beforehand using the where method, all rows of that table are deleted. The return type of this method is true if the row is deleted, and false otherwise.

$user = new user();
$user->delete();

Example 1

$user = new user();
$user->where("id", 1);
$result = $user->delete();
if ($result) {
    echo "successfully deleted";
}

The orderBy Method

This method is used to sort the rows fetched from the database. Its first parameter takes the column name, and its second parameter takes the sort direction, ascending or descending.

$user->orderBy(orderByField, orderbyDirection);

Example 1

$user = new user();
$user->orderBy("id", "DESC");
$users = $user->get();
foreach ($users as $user) {
	echo $user->id;
	echo $user->firstname;
}
/* output
5
Jack

4
John

1
Sam smith
*/

Example 2

$user = new user();
$user->orderBy("name", "ASC");
$users = $user->get(null, array("firstname"));
foreach ($users as $user) {
	echo $user->firstname;
}
/* output
John
Sam smith
*/

The groupBy Method

This method is used to deduplicate the fetched data (removing rows with duplicate values in the specified column) from the database. Its parameter takes the column name.

$user = new user();
$user->groupBy(groupByField);

Example 1

$users = user::get(null, array("firstname"));
foreach ($users as $user) {
	echo $user->firstname;
}
/* output
Sam smith
Sam smith
*/

$user = new user();
$user->groupBy("firstname");
$users = $user->get(null, array("firstname"));
/* output
Sam smith
*/

The has Method

This method is used to check whether or not data exists in the database. The output of this method is true if a row matching the conditions defined beforehand is found, and false otherwise.

$user = new user();
$user->has();

Example 1

$user = new user();
$user->where("email", "John@some.com");
$has = $user->has();
if ($has) {
    return "the email address already taken";
}

The users Table Class​

The get Method​

The getOne Method​

The getValue Method​

The where Method​

The byId Method​

The orWhere Method​

The save Method​

The delete Method​

The orderBy Method​

The groupBy Method​

The has Method​

The users Table Class

The get Method

The getOne Method

The getValue Method

The where Method

The byId Method

The orWhere Method

The save Method

The delete Method

The orderBy Method

The groupBy Method

The has Method