Skip to main content
Version: Current

Direct Database Access

The packages\base\db class is used to interact with the database. For ease of use, the methods of this class are written as static methods, each of which represents one operation in the SQL language.

The get method​

This method lets you retrieve data from the database in bulk. The return type of this method is an array of rows from the requested table. The first parameter of this method is the table name, the second parameter is the number of rows, and the third parameter is the name of the column or columns requested from the table.

db::get(tablename, numrows, columns);

Example 1

$users = db::get("users");
// Query: SELECT * FROM `users`;

/*
$users = array(
    array(
        "id" => 1,
        "name" => "Sam smith",
        "email" => "[email protected]"
    ),
    array(
        "id" => 2,
        "name" => "Sam smith",
        "email" => "[email protected]"
    )
);
*/

You can also call this method in the following ways:

$users = db::get("users", null, array("id"));
// Query: SELECT `id` FROM `users`;

/*
$users = array(
    array(
        "id" => 1
    ),
    array(
        "id" => 2
    )
);
*/
$users = db::get("users", 1);
// Query: SELECT * FROM `users` LIMIT 1;

/*
$users = array(
    array(
        "id" => 1,
        "name" => "Sam smith",
        "email" => "[email protected]"
    )
);
*/

The getOne method​

This method lets you retrieve the data of only a single row from the database. The return type of this method is an array in which the column names are the keys of the array and the value at each index is that column's value. The first parameter of this method is the table name, and the second parameter is the name of the column or columns requested from the table.

db::getOne(tablename, columns);

Example 1

$user = db::getOne("users");
// Query: SELECT * FROM `users` LIMIT 1;

/*
$user = array(
    "id" => 1,
    "name" => "Sam smith",
    "email" => "[email protected]"
);
*/

Example 2

$user = db::getOne("users", array("name"));
// Query: SELECT `name` FROM `users` LIMIT 1;

/*
$user = array(
    "name" => "Sam smith",
);
*/

The getValue method​

This method retrieves only the value of a single column of a table 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 depending on the column type, the type of the value will differ. The first parameter of this method is the table name, the second parameter is the column name, and the third parameter is the number of returned values.

db::getValue(tablename, column, limit);

Example 1

$count = db::getValue("users", "COUNT(*)");
// Query: SELECT COUNT(*) as `val` FROM `users` LIMIT 1;

echo "{$count} users found";

You can also call this method in the following ways:

$logins = db::getValue("users", "login"); 
// Query: SELECT `login` as `val` FROM `users` LIMIT 1;

$logins = db::getValue("posts", "title", null);
// Query: SELECT `title` as `val` FROM `posts`;
/*
Array
(
    [0] => Announcing GitHub Desktop 1.2
    [1] => GitHub Marketplace celebrates one year
    .
    .
    .
)
*/

$logins = db::getValue("posts", "title", 5);
// Query: SELECT `title` as `val` FROM `posts` LIMIT 5;
/*
Array
(
    [0] => Announcing GitHub Desktop 1.2
    [1] => GitHub Marketplace celebrates one year
    [2] => New improvements to the Slack and GitHub integration
    [3] => Ludum Dare 41—Games to play, hack on, and learn from
    [4] => GitHub contributes to UN free speech expert's report on content moderation
)
*/

The where method​

This method lets you retrieve, from a table, only the rows that match the condition specified in this method. The first parameter of this method is the column name, and the second parameter is the condition value. The third parameter is the condition operator, and the fourth parameter specifies how this condition is combined with other conditions.

db::where(column, value, operator, cond);

Example 1

db::where("id", 1);
$user = db::getOne("users");
// Query: SELECT * FROM `users` WHERE `id` = 1 LIMIT 1;

/*
$user = array(
    "id" => 1,
    "name" => "Sam smith",
    "email" => "[email protected]"
);
*/

Example 2

db::where("id", 1);
db::where("name", "John", "=", "OR");
$users = db::get("users");
// Query: SELECT * FROM `users` WHERE `id` = 1 OR `name` = "John";

/*
$users = array(
    array(
        "id" => 1,
        "name" => "Sam smith",
        "email" => "[email protected]"
    ),
    array(
        "id" => 5,
        "name" => "John",
        "email" => "[email protected]"
    )
);
*/

Example 3

db::where("id", 2, ">=");
$users = db::get("users");
// Query: SELECT * FROM `users` WHERE `id` >= 2;

/*
$users = array(
    array(
        "id" => 2,
        "name" => "Sam smith",
        "email" => "[email protected]"
    ),
    array(
        "id" => 5,
        "name" => "John",
        "email" => "[email protected]"
    )
);
*/

Example 4

db::where("id", array(2, 5), "IN");
$users = db::get("users");
// Query: SELECT * FROM `users` WHERE `id` IN (2, 5);

/*
$users = array(
    array(
        "id" => 2,
        "name" => "Sam smith",
        "email" => "[email protected]"
    ),
    array(
        "id" => 5,
        "name" => "John",
        "email" => "[email protected]"
    )
);
*/

The orWhere method​

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

db::orWhere(column, value, operator);

Example 1

db::where("id", 1);
db::orWhere("name", "John");
$users = db::get("users");
// Query: SELECT * FROM `users` WHERE `id` = 1 OR `name` = "John";

/*
$users = array(
    array(
        "id" => 1,
        "name" => "Sam smith",
        "email" => "[email protected]"
    ),
    array(
        "id" => 5,
        "name" => "John",
        "email" => "[email protected]"
    )
);
*/

The insert method​

This method is used to store data in the database. The first parameter of this method is the table name, and the second parameter is an array of data. The keys of this array are the table's column names, and their values are the values of the corresponding columns. On success, the return value of this method is the value of the table's auto increment column; on error, it is false.

db::insert(tablename, insertdata);

Example 1

$data = array(
    "name" => "Peter",
    "email" => "[email protected]"
);
$result = db::insert("users", $data);
// Query: INSERT INTO `users` (`name`, `email`) VALUES ("Petter", "[email protected]");

if (!$result) {
    echo "insert failed";
}

The update method​

This method is used to change the value of a column or columns of a database table's data. The first parameter of this method is the table name, the second parameter is an array of values, and the third parameter specifies the maximum number of rows whose data is changed. The return value of this method is true if the data is updated, and false otherwise.

db::update(tablename, updatedata, limit);

Example 1

db::where("id", 1);
$user = db::getOne("users", array("name"));
// Query: SELECT `name` FROM `users` WHERE `id` = 1;

/*
$user = array(
    "name" => "Sam smith",
);
*/
$data = array(
    "name" => "Peter"
);
db::where("id", 1);
$result = db::update("users", $data, null);
// Query: UPDATE `users` SET `name` = "Peter" WHERE `id` = 1;
if (!$result) {
    throw new \Exception("update Failed");
}
db::where("id", 1);
$user = db::getOne("users", array("name"));
// Query: SELECT `name` FROM `users` WHERE `id` = 1;
/*
$user = array(
    "name" => "Peter",
);
*/

The delete method​

This method is used to delete rows from a table. The first parameter of this method is the table name, and the second parameter is the maximum number of rows to delete. If no condition is set using the where method before this method, all rows of that table will be deleted. The return value of this method is true if a row is deleted, and false otherwise.

db::delete(tablename, limit);

Example 1

db::where("id", 1);
$result = db::delete("users");
// Query: DELETE FROM `users` WHERE `id` = 1;
if ($result) {
    echo "successfully deleted";
}

The orderBy method​

This method is used to sort the rows retrieved from the database. The first parameter of this method is the column name, and the second parameter is the sort order of the rows, ascending or descending.

db::orderBy(orderByField, orderbyDirection);

Example 1

db::orderBy("id", "DESC");
$users = db::get("users");
// Query: SELECT * FROM `users` ORDER BY `id` DESC;
/*
$users = array(
	array(
        "id" => 5,
        "name" => "John",
        "email" => "[email protected]"
    ),
    array(
        "id" => 1,
        "name" => "Sam smith",
        "email" => "[email protected]"
    )
);
*/

Example 2

db::orderBy("name", "ASC");
$users = db::get("users", null, array("name"));
// Query: SELECT `name` FROM `users` ORDER BY `name` ASC;
/*
$users = array(
	array(
        "name" => "John",
    ),
    array(
        "name" => "Sam smith",
    )
);
*/

The groupBy method​

This method is used to consolidate the data retrieved from the database (removing rows with duplicate values in the specified column). Its only parameter is the column name.

db::groupBy(groupByField);

Example 1

$users = db::get("users", null, array("name"));
// Query: SELECT `name` FROM `users`;
/*
$users = array(
	array(
        "name" => "Sam smith",
    ),
    array(
        "name" => "Sam smith",
    )
);
*/

db::groupBy("name");
$users = db::get("users", null, array("name"));
// Query: SELECT `name` FROM `users` GROUP BY `name`;
/*
$users = array(
	array(
        "name" => "Sam smith",
    )
);
*/

The has method​

This method is used to check for the existence or non-existence of data in the database. Its parameter is the table name. The output of this method is true if a row matching the conditions defined before it is found, and false otherwise.

db::has(tableName);

Example 1

db::where("email", "[email protected]");
$has = db::has("users");
if ($has) {
	return "the email address already taken";
}

The join method​

This method lets you join two tables from the database together. The first parameter of this method is the name of the table you want to join, the second parameter is the join condition, and the third parameter is the join type.

db::join(jointable, joincondition, jointype);
db::join("users u", "p.tenantID=u.tenantID", "LEFT");
db::where("u.id", 6);
$products = db::get ("products p", null, array("u.name", "p.productName"));
// Query: SELECT `u`.`name`, `p`.`productNumber` FROM `products` AS `p` LEFT JOIN `users` `u` ON `p`.`tenantID` = `u`.`tenantID` WHERE `u`.`id` = 6;

The joinWhere method​

Joins two tables from the database with the specified condition.

db::join("users u", "p.tenantID=u.tenantID", "LEFT");
db::joinWhere("users u", "u.tenantID", 5);
$products = db::get ("products p", null, "u.name, p.productName");
// Query: SELECT `u`.`name`, `p`.`productNumber` FROM `products` AS `p` LEFT JOIN `users` `u` ON `p`.`tenantID` = `u`.`tenantID` AND `u`.`tenantID` = 5;

The joinOrWhere method​

Joins two tables from the database with the specified condition, when present.

db::join("users u", "p.tenantID=u.tenantID", "LEFT");
db::joinOrWhere("users u", "u.tenantID", 5);
$products = db::get ("products p", null, "u.name, p.productName");
// Query: SELECT `u`.`name`, `p`.`productNumber` FROM `products` AS `p` LEFT JOIN `users` `u` ON `p`.`tenantID` = `u`.`tenantID` OR `u`.`tenantID` = 5;

The getLastQuery method​

This method lets you view the last query sent to the database.

db::getLastQuery();

db::get("users");
echo db::getLastQuery();
// Query: SELECT * FROM `users`;

The getLastError method​

Shows the last error received from the database.

db::getLastError();

The getLastErrno method​

Shows the code of the last error received from the database.

db::getLastErrno();