Documentation

Class libraries

mysql

mysql class library to work with MySQL Server database

File: tokernel.framework/lib/mysql.lib.php

Connecting to MySQL Server Database

The MySQL Server connection configurations file located in /application/config/databases.ini

Before start to work with mysql library we have to setup a connection to server database.
It is possible to have more than one instances of mysql library and Database access.
For example, the object $db1 can access to database "A" and the $object2 can access another.
Let's make 2 connections to mysql server different databases.
Open the file /application/config/databases.ini and insert your connection values, where each connection is one section of configuration file.

; Connection 1 [Database1] server=mysql host=localhost port= database=database1 username=myusername password=mypassword table_prefix= log_errors=1 display_errors=1 log_benchmarking=0 ; Connection 2 to another database [Database2] server=mysql host=localhost port= database=database2 username=myusername password=mypassword table_prefix= log_errors=1 display_errors=1 log_benchmarking=0

Now after configuration set we have to connect to MySQL Server databases.
To initialize library instance let's define 2 objects with different connection values.

// As you can see, the 'Database1' is the section name in configuration file $db_obj1 = $this->lib->mysql->instance('Database1'); $db_obj2 = $this->lib->mysql->instance('Database2'); // Getting both Database tables results. $result1 = $db_obj1->query("select * from articles limit 5"); $result2 = $db_obj2->query("select * from articles limit 5"); // Fetching all records echo 'Database1'; while($row = $db_obj1->assoc($result1)) { print_r($row); } echo 'Database2'; while($row = $db_obj2->assoc($result2)) { print_r($row); }

So, the connection to dataabses is established and we're able to work with our data set. But what if we want to setup default connection to work without any special instance of mysql library? Of course it is possible. Let's setup the default instance of mysql library and work with Database.
Assume, we have already setup dataabase connection values in configuration file and the section name is 'Database1'.

// This line of code can be written in any place where you want, in addon class in module or so. // To call it only once, just put it in the application hook file. // Example hook file: /application/hooks/before_run.hook.php $this->lib->mysql->configure('Database1'); // After this In any place of project you're able to access mysql database as listed bellow. $result = $this->lib->mysql->query("select * from articles limit 5");

Library usage examples

As of many database libraries, mysql class libarary has all possiblities to work with database tables. The functionality listed below will present how to work with data and so. Assume, we have aleady connected to server database and the library object defined as $db_obj1 (see above).

Insert records

// Simple query option $db_obj1->query("insert into articles set title='" . $db_obj1->escape('The title') ."', content='". $db_obj1->escape('The content') ."' "); $last_id = $db_obj1->insert_id(); // Use CRUD insert method $data = array( 'title' => 'The title', 'content' => 'The content' ); $last_id = $db_obj1->insert('articles', $data);

Update records

// Simple query option $db_obj1->query("update articles set title='" . $db_obj1->escape('The title') ."', content='". $db_obj1->escape('The content') ."' WHERE id_article='". $db_obj1->escape(99) ."' "); // Use CRUD update method $data = array( 'title' => 'The title', 'content' => 'The content' ); $where = array( 'id_articles' => 99 ); $db_obj1->update('articles', $data, $where);

Delete records

// Simple query option $db_obj1->query("delete from articles WHERE id_article='". $db_obj1->escape(99) ."' "); // Use CRUD delete method $where = array( 'id_articles' => 99 ); $affected_rows = $db_obj1->delete('articles', $where);

Fetch records

// Fetch records with simple query method. // This will return mysqli (extension) return object with all records. $result1 = $db_obj1->query("select * from articles limit 5"); // Get troutg all records while($row = $db_obj1->assoc($result1)) { // Each $row will be fetched as assoc array of record. print_r($row); // Now you can use as (example): $row['title'] } // // Let's see same example but getting object instead of (assoc) array. while($row = $db_obj1->object($result1)) { // Each $row will be fetched as object of record. print_r($row); // Now you can use as (example): $row->title }

There is also another option to fetch records.

// Fetch all records into an array // Each record will be an assoc array $results = $db_obj1->fetch_all_assoc("select `title`, `content` from articles limit 5"); foreach($results as $row) { echo $row['title'] . ': ' . $row['content']; } // Another example to fetch all records as objects in array // Each record will be an object $results = $db_obj1->fetch_all_object("select `title`, `content` from articles limit 5"); foreach($results as $row) { echo $row->title . ': ' . $row->content; }

Now, it is time to fetch only one record from database table.

// Fetch record as assoc array $arr_record = $db_obj1->fetch_assoc("select * from articles where id_article = 99"); // Fetch record as object $arr_record = $db_obj1->fetch_object("select * from articles where id_article = 99");

Library methods (listed alphabetically)

int affected_rows()

Return Affected rows count after some operations

// In this example, the method will return updated records count. // Let's update first. $db_obj1->query('update articles set status = 1 where status >= 2 '); // Than get affected rows $affected_rows = $db_obj1->affected_rows();

array assoc(object)

Return assoc array from result object

$result = $db_obj1->query("select * from articles"); while($row = $db_obj1->assoc($result)) { print_r($row); }

bool begin_trans([bool $auto_commit = false])

Begin Transaction

bool commit_trans()

Commit transaction.

mixed config(string $item)

Get configuration value of instance

// Get database name of instance, defined in configuration file. $db_name = $db_obj1->config('database');

bool configure(string $conn_section)

Reconfigure database object instance by configuration section nane.

resource connect([string $conn_section = NULL])

Connect to MySQL Server.
This method is optional as the connection establishment automatically.
The optional parameter is the name of the connection credentials section name in configuration file.

mixed count(string $table[, array $where = NULL][, string $exp = 'AND'])

Return records count from table.

int delete(string $table, array $where)

Delete records from table with specified criterias and return affected rows count.

// Delete from 'articles' table where id_srticle = 99 and status = 2 $where = array( 'id_article' => 99, 'status' => 2 ); $records_deleted = $db_obj1->delete('articles', $where);

void disconnect()

Disconnect from MySQL Server

string error()

Get last MySQL error message

mixed escape(mixed $string)

Return MySQL escaped (sanitized) string.

array fetch_all_assoc(string $query)

Return array of records as assoc arrays

// This will return array with assoc arrays. $result = $db_obj1->fetch_all_assoc("select * from articles limit 5"); foreach($result as $row_arr) { // Now each row is an assoc array echo $row_arr['title']; }

array fetch_all_object(string $query)

Return array of records as objects

// This will return array with objects. $result = $db_obj1->fetch_all_object("select * from articles limit 5"); foreach($result as $row_obj) { // Now each row is an object echo $row_obj->title; }

array fetch_assoc(string $query)

Return row as assoc array.

// This will return only one array. $row_arr = $db_obj1->fetch_assoc("select * from articles where id_article = 5"); echo $row_arr['title'];

object fetch_object(string $query)

Return row as object.

// This will return only one object. $row_obj = $db_obj1->fetch_object("select * from articles where id_article = 5"); echo $row_obj->title;

array fetch_row(string $query)

Return row as indexed array.

// This will return only one indexed array. $row_arr = $db_obj1->fetch_row("select * from articles where id_article = 5"); echo $row_arr[0];

void free_result(object $result)

Free the memory associated with the result

int insert(string $table, array $data)

Insert records into table and return last insert id.

$data = array( 'title' => 'This is test title', 'content' => 'This is test content' ); $last_insert_id = $db_obj1->insert('articles', $data);

int insert_id()

Return last insert id

object instance([string $conn_section = null])

Return new instance of mysql class library.
The argument $conn_section is the name of connection credentials section in configuration file.

See: /application/config/databases.ini

int num_fields(object $result)

Return number of fields from mysql result object

object object(object $result)

Return Row object from mysql result object

$result = $db_obj1->query("select * from articles"); while($row = $db_obj1->object($result)) { print_r($row); // Object like: $row->title }

mixed query(string $query)

Return MySQL query result // update data (return mysql query result bool) $result = $db_obj1->query("update articles set title='Title edited' where id_article=55"); // Get results (return mysql query result object) $result = $db_obj1->query("select * from articles");

bool reconnect()

This is optional method which tries to reconnect (pint) to mysql server.

mixed result(string $query)

Return only one record value of mysql query result row

// This will return integer (i.e. 55. number of records). $result = $db_obj1->result("select count(*) as cnt from articles"); // This will return string (i.e. title of article). $result = $db_obj1->result("select title from articles limit 1 "); // This will return NULL because of record not found $result = $db_obj1->result("select title from articles where id_article = -99 limit 1 ");

bool rollback_trans()

Roll back the transaction

array row(object $result)

Return indexed array from result object

$result = $db_obj1->query("select * from articles"); while($row = $db_obj1->row($result)) { print_r($row); // i.e. echo $row[0]; }

array select_all_assoc(string $table [, array $where = NULL])

Return array with results as assoc arrays.
The Optional argument $where can be an assoc array defined as select criterias.

// Select all records without criteria // The MySQL Query will look like: select * from articles $result = $db_obj1->select_all_assoc('articles'); // Select records with criteria // The MySQL Query will look like: select * from articles where status = 1 $where = array( 'status' => 1 ); $result = $db_obj1->select_all_assoc('article', $where)

array select_all_object(string $table [, array $where = NULL])

Return array with results as objects.

array select_assoc(string $table [, array $where = NULL])

Return only one row as assoc array.

int select_count(string $table [, array $where = NULL])

Return records count

object select_object(string $table [, array $where = NULL])

Return only one row as object.

int update(string $table, array $data, array $where)

Update records and return affected rows count.

// Update data to $data = array( 'title' => 'The title edited', 'content' => 'The content edited' ); // Which data to update $where = array( 'id_article' => 55 ); // Update and get affected rows count $affected_rows = $db_obj1->update('articles', $data, $where);

See the file /application/addons/example/modules/db_example.module.php for usage examples.