Overview

Database class was designed to minimize code typing and provides simple and easy in use methods to read and write to a database.

Connecting to a database

// Constructor
$DB = new DB($host, $dbName, $username, $password);

// Establishes connection to the database
$DB->Connect();

// Sets names to UTF-8.
$DB->SetUtf8();

By reason of all PHP files in Lightnote CMS saved in UTF-8 encoding (to avoid many problems caused by special characters), it is recommended to use "SET NAMES utf8'' command, to tell MySQL that all SQL statements will be sent in UTF-8 character set.

Note: In your extensions you don't need to connect each time to MySQL, because connection has already been made by CMS. You can use global $DB variable instead.

Executing SQL queries

$result = $DB->DoQuery("SELECT * FROM users");
while($row = mysql_fetch_assoc($result))
{
    // Do something with data
}
mysql_free_result($result);

The benefit of using DoQuery() method instead of mysql_query() command is that all executed queries will be tracked by DB class, and in case it contains an SQL error, the error will be reported to you (due e-mail or directly to browser, depends on settings) with short debug info: line number in PHP file where query has been executed, MySQL error message, and complete query.

Insert, Update, Delete

Assume you have database table named "users" with following fields:

"id" - INT(11) autoincrement
"firstname" - VARCHAR(255)
"lastname" - VARCHAR(255)
"birthdate" - DATE
"created" - DATETIME
"modified" - DATETIME

Following example inserts a new user to a database, updates it, and then deletes it from database:

// Table name
$tableName = "users";

// --- INSERT ---

// Associative array of fields to insert
$insertArray = array(
    "firstname" => "Dmitry",
    "lastname" => "Monin",
    "birthdate" => "1986-04-22",
    "created" => "NOW()"
);

// If table has an autoincrement field (i.e. "id"), than last insert id will be 
// returned, otherwise -1.
$insertId = $DB->Insert($tableName, $insertArray);

// --- UPDATE ---

$whereArray = array(
     "id" => $insertId
);

$updateArray = array(
     "modified" => "NOW()"
);

$DB->Update($tableName, $updateArray, $whereArray);

// --- DELETE ---
$DB->Delete($tableName, $whereArray);

It is also interesting to note that you can specify date or MySQL date function like NOW(), CURDATE() etc. for MySQL table fields of type "DATE" and "DATETIME"

Another interesting method is Replace($tableName, $updateArray, $whereArray). It checks if there is an entry in table which matches to $whereArray, if yes - it makes an Update, if no it makes an Insert.

$updateArray = array(
    "birthdate" => "1974-06-10"
);

$whereArray = array(
    "firstname" => "Dmitry",
    "lastname" => "Monin"
);

$DB->Replace($tableName, $updateArray, $whereArray);

Fetching data

There are several convinient methods, which save a lot of typing:

// Returns a value in first cell in first column, as an integer value
$maxAge = $DB->GetScalar("SELECT MAX(age) FROM users");

/*  Result:
    $maxAge = 50; // for example
*/


// Returns first column as an indexed array
$countries = $DB->GetColumn("SELECT DISTINCT country FROM users ORDER BY country");

/*  Result:
    $countries = array("Brazil", "France", "Germany", ...);
*/


// Retrieves first row of result as an associative array
$userData = $DB->GetRow("SELECT * FROM users WHERE id=1");

/*  Result:
    $userData = array("firstname" => "Dmitry", "lastname" => "Monin", ...);
*/


// Retrieves all rows as an indexed array of associative arrays
$users = $DB->GetRows("SELECT * FROM users");

/*  Result:
    $users = array(
         0 => array("firstname" => "Dmitry", "lastname" => "Monin", ...),
         1 => array("firstname" => "John", "lastname" => "Smith", ...),
         ...
    );
*/

Searching database tables

Assume you have table with following fields:

id INTEGER autoincrement
header VARCHAR(255)
subheader VARCHAR(255)
text TEXT

To search header, subheader and text fields with LIKE statement you can use GetSearchSql() method.

// Search string
$searchString = "some string";

// Fields to search
$fields = array("header", "subheader", "text");

// Generating WHERE sql,  second parameter "1" means that all words should match
$searchWhereSql = $DB->GetSearchSql($fields, $searchString, 1);

// Query to database
$query = "SELECT * FROM news WHERE " . $searchWhereSql;

// Executing query
$DB->DoQuery($query);

// $query = "SELECT * FROM news 
// WHERE ( (header LIKE '%some%' OR subheader LIKE '%some%' OR text LIKE '%some%' ) 
// AND (header LIKE '%string%' OR subheader LIKE '%string%' OR text LIKE '%string%' ) 
// )";