Dev.Module Database

Brief

This document introduce you how to use Pi API to operate database, the article include:

Connect Pi Install Database

Database queries

Pi have encapsulated a class for user to operate tables of the installed database. And the API is in Pi.php file, so you should include its namespace:

use Pi;

Getting table instance

The Pi class provides us a method named model to fetch a table instance, but this method can only operate database of Pi. This method takes two parameters, the first one is the name of table, and the second is the name of module which can be ignored. This method finally return a Pi\Application\Model\ModelAbstract instance.

$model = Pi::model('user');
$model = Pi::model('login/user');
$model = Pi::model('user', 'login');

Supposing the prefix of table is xe, and current module is login, hence, the first line will return an instance which relates to table xe_core_user. The xe_core_user table is a system table. The second line and third line will return same result, and its table operated is xe_login_user.

Note: you can think this method as GLOBAL API, you can call it everywhere to get the model.

The other method to operate table is getModel(), this method can only used in controller.

It also takes two parameters, first one for table to operate; and second one is module name, and it can be skip when operate table of current module. For example:

// In system module, operate core_route table
$model = $this->getModel('route');

// In system module, operate user_profile table
$model = $this->getModel('profile', 'user');

// In user module, operate user_profile table
$model = $this->getModel('profile');

// In user module, operate core_module table
$model = $this->getModel('module', 'system');

Note: we recommend you to use getModel() to fetch current module tables, because the installed module name will change when the module is installed multi-times (due to module clone). For example:

// Table is article_article when access page of article module
// Table is cms_article when access page of cms module that clone from article
$model = $this->getModel('article');

Select

Pi provides us a select() method to fetch data from table, this method inherits from select() method of Zend.

$select = $model->select();
$rowset = $model->selectWith($select);

$select = $model->select()->where(array('username' => 'root'));
$rowset = $model->selectWith($select);

// Alternative
$rowset = $model->select(array('username' => 'root'));

In the code, select() method is used to select data from table. The parameter of it is the condition, which represent by array.

The first block will return all data of table, the second and third block has the same effect, which will return rows that username equal to root. Here is some example of writing where array:

// 'username' != 'root'
$rowset = $model->select(array('username != ?' => 'root'));
$rowset = $model->select(array('username <> ?' => 'root'));
// 'age' > '23'
$rowset = $model->select(array('age > ?' => '23'));
// 'age' <= '45'
$rowset = $model->select(array('age <= ?' => '23'));

Print SQL:

Use getSqlString() method, you can print out your sql for reviewing:

$select = $model->select()->where(['username' => 'root']);
$sql    = $select->getSqlString();

Using SQL IN

Just give an array to a field:

$ids = array('1', '2', '4', '7');
$rowset = $model->select(array('id' => $ids));

This code will select the id equal to anyone in the $ids array.

Using order, limit

For example:

$select = $model->select()->where(array('username' => 'root'))
                        ->order(array('username ASC'));
$rowset = $model->selectWith($select);

$select = $model->select()->where(array('username' => 'root'))
                        ->offset(1)
                        ->limit(20);
$rowset = $model->selectWith($select);

Using group

For example:

$select = $model->select()->group(array('name', 'id'));
$select = $model->select()->group('name');
$select = $model->select()->group('name asc, time desc');

Selecting columns:

For example:

$select = $model->select()->where(array('username' => 'root'))
                        ->columns(array('username', 'age', 'email'));
$rowset = $model->selectWith($select);

Removing the repeat data when selects:

$select = $model->select()->where()->columns(array('username' => new \Zend\Db\Sql\Expression('distinct username')));
$rowset = $model->selectWith($select);

This method only can remove single field, if you add another field such as id and it does not have repeat value, this method will return all rows of field username and id.

For example, there is a table such as:

id	username	gender	age
1	root	male	23
2	root	female	45

If you use the following code to select data:

$select = $model->select()
                ->where()
                ->columns(array('username' => new \Zend\Db\Sql\Expression('distinct username'), 'id', 'gender'));
$rowset = $model->selectWith($select);

It will return:

id	username	gender
1	root	male
2	root	female

You may find there has repeat username root, if you want to remove the repeat data, using group() method as follows:

$select = $model->select()
                ->where()
                ->columns(array('*'))
                ->group('username');
$rowset = $model->selectWith($select);

Using AS

Sometime developer want to rename a field, you can use columns() method:

// select long_name_count as count ...
$select = $model->select()->columns([
    'count' => 'long_name_count',
]);

Using expression to assemble complex sql

use function in column

// select count(*) as count ...
$select = $model->select()->columns(['count' => new \Zend\Db\Sql\Expression('count(*)')]);
$expression = Pi::db()->expression('count(*)');
$select = $model->select()->columns(['count' => $expression]);
// select sum(total) as sum ...
$select = $model->select()->columns(['sum' => new \Zend\Db\Sql\Expression('sum(total)')]);

use function in where

// select * from ... where time > from_unixtime('2015-10-01')
$expression = Pi::db()->expression('from_unixtime(?)', ['2015-10-01']);
$select = $model->select()->where(['time > ?' => $expression]);

More information about expression, please refer Pi Db Expression

Assemble complex where condition

OR condition

// select * from ... where active = 1 or status = 1 or name = 'test'
$select = $model->select()->where([
    'active' => 1,
    'status' => 1,
    'name'   => 'test',
], 'OR');

OR and AND mixed condition

// select * from ... where active = 1 and status = 1 or (category = 1 AND name like 'test');
$where = Pi::db()->where(['active' => 1, 'status' => 1]);
$whereSub = Pi::db()->where("(category = 1 AND name like 'test')");
$where->orPredicate($whereSub);
$select = $model->select()->where($where);

Select from multi-table

Zend provides us a method call join() to join another table to compact.

$select = $select->join(array('abbreviation table name' => 'table full name'), 'where string');

In Pi, we can use $model->select() object to call the method.

$model = $this->getModel('table1');
$table1 = $model->getTable();
$table2 = $this->getModel('table2')->getTable();

$select = $model->select()->join(array('table2' => $table2), 'table2.element = ' . $table1 . '.element');
$rowset = $model->selectWith($select);

$data = array();
$data = $rowset->toArray();

The third parameter of join() method is the columns of the joined table to select, if you use $select() to select columns, it will select the elements of original table.

$model = $this->getModel('userid');
$userid = $model->getTable();
$userinfo = $this->getModel('userinfo')->getTable();

$select = $model->select()->join(array('info' => $userinfo), 'info.username = ' . $userid . '.username', array('email'));
$select->columns(array('id'));
$rowset = $model->selectWith($select);

The SQL statement of this code will be as same as:

'SELECT userid.id AS id, userinfo.email AS email FROM userid INNER JOIN userinfo AS info ON info.username = userid.username'

Sub-selection

Sometime we need to use sub-select to search items, just pass select object in where:

// Select * from core_route where module = (select name from core_module where id = 1 limit 1)
$subselect = Pi::model('module')->select()->columns(['name'])->where(['id' => 1])->limit(1);
$model  = Pi::model('route');
$select = $model->select()->where(['module' => $subselect]);
$rowset = $model->selectWith($select);

Insert

Now we have a model instance, it can be used to call methods for inserting, updating and deleting data. The following codes will insert data into table.

$row = $this->getModel('userinfo')->createRow($data);
$row->save();
if (!$row->id) {
    return false;
}

In the codes, $data parameter of createRow() method is an array which contain data to insert, the key of the array is same as that of table:

$data = array(
    'name'      => 'John',
    'password'  => $password,
);

Then a save() method is called to insert data into table, and finally use $row->id to assure the data is insert correctly.

Insert data from other table

If you want to insert data which from other table, you can use select object, such as:

// Insert into `table_insert` (id, name) select id, name from demo where id < 100
$select = Pi::model('demo')->select()->columns(['id', 'name'])->where(['id < ?' => 100]);
$model  = Pi::model('table_insert');
$table  = $model->getTable();
$insert = Pi::db()->insert($table)->columns(['id', 'name']);
$insert->values($select);
$model->insertWith($insert);

If the two tables have same structure, the columns can be ignored:

// Insert into `table_insert` select * from demo where id < 100
$select = Pi::model('demo')->select()->where(['id < ?' => 100]);
$model  = Pi::model('table_insert');
$table  = $model->getTable();
$insert = Pi::db()->insert($table);
$insert->values($select);
$model->insertWith($insert);

Update

The save() method also allow user to update data of tables. But something should be done before using save() method. Such as fetching the id of the data you want to update and getting instance of the rowset.

Supposing you create fields id, username and password in your table userinfo, you want to change the password according to username field.

// fetching id from table by username
$model = $this->getModel('userinfo');
$rowset = $model->select(array('username' => 'root'));
foreach ($rowset as $row) {
    $id = $row['id'];
}

// updating password
$row = $model->find($id);
$row->password = $password;
$row->save();

Certainly you can also use a simple method update() to update data.

$model->update(array('password' => $password), array('username' => 'root'));

Delete

In Pi, delete() method is used to delete data, this method also need you to call find() method.

$row = $this->getModel('userinfo')->find($id);
$row->delete();

You can also use delete() method provide by Zend:

$this->getModel('userinfo')->delete(array('id' => $id));

Connect Outer Database

Execute query directly

Operate via sql

Sometimes developers want to connect other database to do some database operation. You can use the following codes to achieve this task:

$options = [
    'connection'    => [
        'driver'    => 'pdo',
        'dsn'       => 'mysql:host=localhost;dbname=test',
        'username'  => 'test',
        'password'  => 'test',

        // Recommend you to add this config
        'driver_options'   => [
            \PDO::MYSQL_ATTR_INIT_COMMAND    => 'SET NAMES utf8 COLLATE utf8_general_ci',
            \PDO::ATTR_PERSISTENT            => false,
        ],
    ],
];
$count = 0;
$result = Pi::db($options)->query('select count(*) as `count` from `demo`');
foreach ($result as $row) {
    $count = $row->count;
}

// Update
Pi::db($options)->query('update `demo` set `name` = 'test' where `id` = 1');

// Insert
$sql = "insert into `demo` (`name`, `description`) values ('test', '')";
Pi::db($options)->query($sql);

// Delete
Pi::db($options)->delete('delete from `demo` where `id` = 1');

Operate with prepare

Select with prepare will make the PDO send sql query and parameter values separately, one of the benefit is that mysql will help us to escape the parameter values:

$sql = 'select * from `demo` where `id` = :id';
// $options is as same as above case
$resultSet = Pi::db($options)->adapter()->query($sql, ['id' => 1]);
$arraySet  = $resultSet->toArray();
// $arraySet is an array type

// Update
Pi::db($options)->query('update `demo` set `name` = 'test' where `id` = :id', ['id' => 1]);

// Insert
$sql = "insert into `demo` (`name`, `description`) values (':name', ':desc')";
Pi::db($options)->query($sql, [
    'name' => 'test',
    'desc' => '',
]);

// Delete
Pi::db($options)->delete('delete from `demo` where `id` = :id', ['id' => 1]);

More about query API, please refer Pi Db Adapter

Execute query by Provided API

Non Pi database

Developers can also connect and operate outer database by Pi API, such as where(), columns, etc. The only thing you need to do is get the Pi\Application\Model\Model instance:

$options = [
    'connection'    => [
        'driver'    => 'pdo',
        'dsn'       => 'mysql:host=localhost;dbname=test',
        'username'  => 'test',
        'password'  => 'test',

        // Recommend you to add this config
        'driver_options'   => [
            \PDO::MYSQL_ATTR_INIT_COMMAND    => 'SET NAMES utf8 COLLATE utf8_general_ci',
            \PDO::ATTR_PERSISTENT            => false,
        ],
    ],
    // This is required if it is not a Pi database
    'core_prefix' => '',
];
$model = Pi::db($options)->model('demo');
$select = $model->select()->columns(['id'])->where(['id' => 1])->order('id asc')->limit(1);
$rowset = $model->selectWith($select)->toArray();

// Update
$model->update(['name' => 'test'], ['id' => 1]);

// Insert
$row = $model->createRow(['name' => 'test']);
$row->save();
$id = $row->id;

// Delete
$model->delete(['id' => 1]);

Pi database

If you want to connect to anther Pi database, and do not want to use site API, it is recommends you to add table prefix and core prefix to the connection configuration:

 $options = [
    'connection'    => [
        'driver'    => 'pdo',
        'dsn'       => 'mysql:host=localhost;dbname=test',
        'username'  => 'test',
        'password'  => 'test',

        // Recommend you to add this config
        'driver_options'   => [
            \PDO::MYSQL_ATTR_INIT_COMMAND    => 'SET NAMES utf8 COLLATE utf8_general_ci',
            \PDO::ATTR_PERSISTENT            => false,
        ],
    ],
    // This is table prefix of table of another Pi database
    'table_prefix'  => 'pi_',
    'core_prefix' => 'core_',
];
// select things from pi_core_module
$model = Pi::db($options)->model('module');
$select = $model->select()->columns(['id'])->where(['id' => 1])->order('id asc')->limit(1);
$rowset = $model->selectWith($select)->toArray();

// select things from module table, pi_page_page
$model = Pi::db($options)->model('page/page');
$row   = $model->find('about', 'name');
$name  = $row->name;

hiii

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Dev.Module Database

Brief

Connect Pi Install Database

Database queries

Select

Insert

Update

Delete

Connect Outer Database

Execute query directly

Execute query by Provided API

Clone this wiki locally