SQL Chained Queries

This page content is only applicable to "Version 1.7 and higher"!

Basic Concepts

Demo 1:

// Generate SQL statement using chained syntax
$sql = $zbp->db->sql->get()
               ->select($zbp->table['Post'])
               ->where(array('=', 'log_ID', "1"))
               ->sql;
// Execute SQL statement and return result
$array = $zbp->db->Query($sql);
print_r($array);

Demo 2:

// Directly return query result in chained read
$array = $zbp->db->sql->get()
               ->select($zbp->table['Comment'])
               ->count(array('comm_ID' => 'num'))
               ->query;
print_r($array);

Demo 3:

For the following methods or functions, the `$select` or `$sql` parameter can be a "SQL chain object":

• `$zbp->GetPostList()`, `$zbp->GetCommentList()`, `$zbp->GetTagList()`, etc., the first parameter is `$select`;

• `$zbp->GetListType()`, `$zbp->GetListOrigin()`;

# First define a SQL chain object, then get the result
$posts = $zbp->GetPostList(
                         $zbp->db->sql->get()->select($zbp->table['Post'])->where('=', 'log_CateID', 1)
                                                 );

SQL SELECT

I. Using the `select` instruction

// Get all data from the category table
$sql = $zbp->db->sql->get()->select($zbp->table['Category'])->sql;
$array = $zbp->GetListType('Category', $sql);
// Iterate through data for output, etc.
foreach ($array as $a) {
    ...
}

SQL statement output:

SELECT * FROM zbp_category

II. Using `selectany` and `from` instruction combination

// Get an array of IDs from the category table
$sql = $zbp->db->sql->get()->selectany('cate_ID')->from($zbp->table['Category'])->sql;
$array = $zbp->db->query($sql);

SQL statement output:

SELECT cate_ID FROM zbp_category

SQL WHERE Clause

Get posts with ID 1

$sql = $zbp->db->sql->get()->select($zbp->table['Post'])
                         ->where(array('=', 'log_ID', "1"))
                         ->sql;
$articles = GetListType('Post', $sql);

// `where(array('=', 'log_ID', "1"))` = can be replaced with <>, LIKE, >=,

SQL statement output:

SELECT * FROM zbp_post WHERE log_ID = '1'

Important: If you must concatenate strings in `where`, always escape single quotes with `addslashes`:

where('log_Title LIKE \'' . addslashes('string') . '\'')

AND Operator

Continuously calling the `where()` method implements an `AND` query:

$sql = $zbp->db->sql->get()->select($zbp->table['Post'])
                         ->where(array('=', 'log_Status', "1"))
                         ->where(array('=', 'log_Type', "0"))
                         ->sql;
$articles = GetListType('Post', $sql);

SELECT * FROM zbp_post WHERE log_Status = '1' AND log_Type = '0'

// `where` can also define an array first
$w = array();
$w[] = array('=', 'log_Type', '0');
$w[] = array('=', 'log_AuthorID', '1');
$w[] = array('=', 'log_CateID', '2');
// Use as a parameter
->where($w)

OR Operator

$sql = $zbp->db->sql->get()->select($zbp->table['Post'])
                         ->where(
                                 array('or',
                                        array(
                                              array('log_ID', '1'),
                                              array('log_Title', '2'),
                                             ),
                                        )
                                )
                         ->sql;
// Note: The conditions here can also be written as ->where('or', array('=', 'log_ID', '1'), array('=', 'log_Title', '2'))

SELECT * FROM zbp_post WHERE ((1 = 1) AND ( log_ID = '1' OR log_Title = '2' ))

Non-default operators:

// The default operator is =, other operators can also be used
$sql = $zbp->db->sql->get()->select($zbp->table['Post'])
                         ->where(
                                 array('or',
                                        array(
                                              array('<>', 'log_ID', '1'),
                                              array('LIKE', 'log_Title', '2'),
                                             ),
                                        )
                                )
                         ->sql;

SELECT * FROM zbp_post WHERE ((1 = 1) AND ( log_ID <> '1' OR log_Title LIKE '2' ))

LIKE Operator

Generally used in searches. The difference between `search` and `like` is that `search` automatically adds '%' to both ends of the string.

$sql = $zbp->db->sql->get()->select($zbp->table['Post'])
                         ->where(array('search', 'log_Title', "Test"))
                         ->sql;

SELECT * FROM zbp_post WHERE ((1 = 1) AND ( (log_Title LIKE '%Test%') ))

IN Operator

$sql = $zbp->db->sql->get()->select($zbp->table['Post'])
                         ->where(array('IN', 'log_ID', array(1, 2, 3, 4)))
                         ->sql

SELECT * FROM zbp_post WHERE ((1 = 1) AND (log_ID IN ( '1' , '2' , '3' , '4' )))

$sql = $zbp->db->sql->get()->select($zbp->table['Post'])
                         ->where(array('NOT IN', 'log_ID', '(1, 2, 3)'))
                         ->sql

SELECT * FROM zbp_post WHERE (log_ID NOT IN (1, 2, 3))

BETWEEN Operator

$sql = $zbp->db->sql->get()->select($zbp->table['Post'])
                         ->where(array('between', 'log_ID', "1", "3"))
                         ->sql

SELECT * FROM zbp_post WHERE (log_ID BETWEEN '1' AND '3')

EXISTS and NOT EXISTS Operators

$sql = $zbp->db->sql->get()->select($zbp->table['Post'])
                         ->where(array('exists', 'SELECT 1'))
                         ->sql;

SELECT * FROM zbp_post WHERE EXISTS ( SELECT 1 )

SQL Wildcards

Available wildcards: `%`, `_`, `[charlist]`, `[^charlist]`, `[!charlist]`

$sql = $zbp->db->sql->get()->select($zbp->table['Post'])
                         ->where(array('like', 'log_Title', "_aidu"))
                         ->sql;

SELECT * FROM zbp_post WHERE ((1 = 1) AND ( (log_Title LIKE '_aidu') ))

SELECT LIMIT

If only one parameter is given, it represents the maximum number of records to return. LIMIT n is equivalent to LIMIT 0,n.

$sql = $zbp->db->sql->get()->select($zbp->table['Post'])
                         ->limit(5)
                         ->sql;

SELECT * FROM zbp_post LIMIT 5
// Equals
SELECT * FROM zbp_post LIMIT 5 OFFSET 0

Retrieve records starting from the 6th row, returning a maximum of 10 rows

$sql = $zbp->db->sql->get()->select($zbp->table['Post'])
                         ->limit(5, 10)
                         ->sql;

SELECT * FROM zbp_post LIMIT 10 OFFSET 5

SELECT DISTINCT

The DISTINCT keyword is used to return only different values.

$sql = $zbp->db->sql->get()->select($zbp->table['Post'])
                         ->distinct(array('log_Title'=>'t'), 'log_ID')
                         ->sql;

SELECT DISTINCT log_Title AS t,log_ID FROM zbp_post

SQL ORDER BY

The ORDER BY statement is used to sort the result set in ascending order by the specified column by default:

$sql = $zbp->db->sql->get()->select($zbp->table['Post'])
                         ->orderBy(array('log_PostTime' => 'desc'), array('log_ID' => 'asc'))
                         ->sql;

SELECT * FROM zbp_post ORDER BY log_PostTime DESC, log_ID ASC

SQL GROUP BY Statement

Get the total number of comments for all posts by each author:

$sql = $zbp->db->sql->get()->select($zbp->table['Post'])
                         ->sum('log_CommNums')
                         ->groupBy('log_AuthorID')
                         ->sql;

SELECT SUM(log_CommNums) FROM zbp_post GROUP BY log_AuthorID

SQL HAVING Clause

The HAVING clause is added in SQL because the WHERE keyword cannot be used with aggregate functions;

The following example retrieves all categories where the total number of comments for posts in that category is greater than 100:

$sql = $zbp->db->sql->get()->select($zbp->table['Post'])
                         ->column('log_CateID')
                         ->column('SUM(log_CommNums)')
                         ->groupby('log_CateID')
                         ->having(array('>', 'SUM(log_CommNums)', '100'))
                         ->sql;

SELECT log_CateID,SUM(log_CommNums) FROM zbp_post GROUP BY log_CateID HAVING SUM(log_CommNums) > 100

SQL JOIN (LEFT JOIN, RIGHT JOIN, FULL JOIN, INNER JOIN)

$sql = $zbp->db->sql->get()->selectany('log_ID')
                         ->from(array($zbp->table['Post']=>'p'))
                         ->leftjoin(array('zbp_postrelation'=>'pr'))
                         ->on('p.log_ID = pr.pr_PostID')
                         ->where('1 = 1')
                         ->sql;

// This example only demonstrates LEFT JOIN. Aliases are set for the two tables respectively.

SELECT log_ID
FROM zbp_post AS p
LEFT JOIN zbp_postrelation AS pr
    ON p.log_ID = pr.pr_PostID
WHERE 1 = 1

// If you want to use STRAIGHT_JOIN in MySQL, add the `option` parameter to the chained SQL
->option(array('straight_join' => true))

UNION, UNION ALL Operators

When using UNION, MySQL removes duplicate records from the result set. When using UNION ALL, MySQL returns all records, and it is more efficient than UNION.

$sql = $zbp->db->sql->get()->union(
                                 $zbp->db->sql->get()->select('zbp_table')->sql,
                                 $zbp->db->sql->get()->select('zbp_table2')->sql
                                 )
                         ->sql;

SELECT * FROM zbp_table UNION SELECT * FROM zbp_table2

SQL Aliases

This is an example of using table and column aliases:

$sql = $zbp->db->sql->get()->select(array($zbp->table['Post']=>'p'))
                         ->column(array('log_ID'=>'id'))
                         ->column('log_Type AS type')
                         ->sql;

SELECT log_ID AS id,log_Type AS type FROM zbp_post AS p

USEINDEX, FORCEINDEX, IGNOREINDEX

Note: This instruction is specific to MySQL, and it refers to using an index, forcing the use of an index, and skipping an index respectively.

$sql = $zbp->db->sql->get()->select($zbp->table['Post'])
                         ->useindex('zbp_log_TPISC', 'zbp_log_VTSC')
                         ->sql;

SELECT * FROM zbp_post USE INDEX ( zbp_log_TPISC ,zbp_log_VTSC )

SQL INSERT INTO Statement

$sql = $zbp->db->sql->get()->insert($zbp->table['Post'])
                         ->data(array('log_Title' => 'test','log_Type' => '0'))
                         ->sql;

INSERT INTO zbp_post (log_Title,log_Type) VALUES ( 'test' , '0' )

SQL UPDATE Statement

$sql = $zbp->db->sql->get()->update($zbp->table['Post'])
                         ->where('=', 'log_ID', 1)
                         ->data(array('log_Title' => 'test','log_Type' => '1'))
                         ->sql;

UPDATE zbp_post SET log_Title = 'test', log_Type = '1' WHERE log_ID = '1'

SQL DELETE Statement

$sql = $zbp->db->sql->get()->delete($zbp->table['Post'])
                         ->where('=', 'log_ID', 1)
                         ->sql;

DELETE FROM zbp_post WHERE log_ID = '1'

CREATE DATABASE, TABLE, INDEX

CREATE DATABASE

You can create databases in mysql and pgsql:

$sql = $zbp->db->sql->get()->create()->database('zbp')->ifnotexists()->sql;

CREATE DATABASE IF NOT EXISTS zbp

CREATE TABLE

The example here is just to demonstrate the usage of SQL chains. In reality, we have already wrapped the SQL chain methods, and using the `$zbp->db->CreateTable` method would be more convenient.

Create a table, first define the data structure:

$tableData = array(
    'a' => array('a', 'integer', '', 0, 'Primary ID'),
    'i' => array('i', 'boolean', '', false),
    'k' => array('k', 'string', 250, ''),
    'o' => array('o', 'string', 'longtext', '', 'Remark for a certain field'),
    'r' => array('r', 'float', '', ''),
);
$sql = $zbp->db->sql->get()->create('zbp_table')
                         ->data($tableData)
                         ->option(array('engine' => 'InnoDB'))
                         ->option(array('charset' => 'utf8mb4'))
                         ->option(array('collate' => 'utf8mb4_general_ci'))
                         ->sql;
$zbp->db->query($sql);
echo $sql;

SQL statement output:

CREATE TABLE IF NOT EXISTS zbp_table ( a int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primary ID', i tinyint(1) NOT NULL DEFAULT '0', k varchar(250) NOT NULL DEFAULT '', o longtext NOT NULL COMMENT 'Remark for a certain field', r float NOT NULL DEFAULT 0, PRIMARY KEY (a) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci AUTO_INCREMENT=1

Note: The table engine and character set can be specified. If not specified, the system configuration will be read from the option file.

CREATE INDEX

$sql = $zbp->db->sql->get()->create($zbp->table['Post'])
  ->index(
    array('zbp_post_index_stt'=>array('log_Status','log_Type','log_Tag'))
  )
  ->sql;

CREATE INDEX zbp_post_index_stt ON zbp_post ( log_Status , log_Type , log_Tag )

DROP DATABASE, TABLE, INDEX

DROP DATABASE

$sql = $zbp->db->sql->get()->drop()->database('zbp')->sql;

DROP DATABASE zbp

DROP TABLE

$sql = $zbp->db->sql->get()->drop()->table('zbp_table2')->ifexists()->sql;

DROP TABLE IF EXISTS zbp_table2

DROP INDEX

$sql = $zbp->db->sql->get()->drop('zbp_table')->index('zbp_table_id')->sql;

DROP INDEX zbp_table_id ON zbp_table

ALTER Modify, Add Fields

Add Field

$sql = $zbp->db->sql->get()->ALTER($zbp->table['Post'])
->ADDCOLUMN('log_IsHide integer NOT NULL DEFAULT \'0\'')
->ADDCOLUMN('log_CreateTime', 'integer NOT NULL DEFAULT \'0\'')
->ADDCOLUMN('log_Tel', 'varchar(250)')
->ADDCOLUMN('log_Note', 'text', 'NOT NULL')
->sql;
// Parameters within ADDCOLUMN can be written as one or multiple

ALTER TABLE zbp_post ADD COLUMN log_IsHide integer NOT NULL DEFAULT '0' ,ADD COLUMN log_CreateTime integer NOT NULL DEFAULT '0' ,ADD COLUMN log_Note text NOT NULL

Modify Field

Note: Only MySQL and PostgreSQL support this. SQLite cannot modify fields;

$sql = $zbp->db->sql->get()->ALTER("zbp_post")
->ALTERCOLUMN('log_IsHide INTEGER NOT NULL DEFAULT \'0\'')
->sql;

# MySQL generated
ALTER TABLE zbp_post MODIFY log_IsHide INTEGER NOT NULL DEFAULT '0'
# PostgreSQL generated
ALTER TABLE zbp_post ALTER COLUMN log_IsHide INTEGER NOT NULL DEFAULT '0'

Delete Field

$sql = $zbp->db->sql->get()->ALTER("zbp_post")
->DROPCOLUMN('log_IsHide')
->DROPCOLUMN('log_CreateTime')
->DROPCOLUMN('log_Note')
->sql;

ALTER TABLE zbp_post DROP COLUMN log_IsHide ,DROP COLUMN log_CreateTime ,DROP COLUMN log_Note

SQL Statistical Functions AVG, COUNT, MIN, MAX, SUM

Here we only use COUNT as an example, to get the total number of articles for each author and display them grouped:

$sql = $zbp->db->sql->get()->select($zbp->table['Post'])
                         ->column('log_AuthorID')
                         ->count(array('log_ID'=>'num'))
                         ->where(array('=', 'log_Type' , '0'))
                         ->groupBy('log_AuthorID')
                         ->sql;

SELECT log_AuthorID, COUNT(log_ID) AS num FROM zbp_post WHERE log_Type = '0' GROUP BY log_AuthorID

SQL Random Record Selection

Version 1.7 introduced random reading functionality, which supports three databases simultaneously.

Here's an example of randomly selecting 5 articles:

$sql = $zbp->db->sql->get()->select($zbp->table['Post'])
                         ->where('=','log_Type',0)
                         ->random(5)
                         ->sql;

Method of using GetList function to randomly get articles:

-- MySQL generated SQL
SELECT *
FROM zbp_post
WHERE log_Type = '0'
        AND log_ID >=
 (SELECT FLOOR( RAND() * (
        (SELECT MAX(log_ID)
        FROM `zbp_post`)-
            (SELECT MIN(log_ID)
            FROM `zbp_post`)) +
            (SELECT MIN(log_ID)
            FROM `zbp_post`))) LIMIT 5

-- PostgreSQL and SQLite generated SQL
SELECT * FROM zbp_post WHERE log_Type = '0' ORDER BY Random() LIMIT 5

SQL Transaction Handling

• BEGIN starts a transaction

• ROLLBACK rolls back the transaction

• COMMIT confirms the transaction

GetList(null,null,null,null,null,null,array('random'=>5));

// GetList in version 1.7 and higher has been improved
GetList(array('random'=>4,'cate' =>1)); // Get 4 random articles from category 1

BEGIN;
COMMIT;

Note: Only the InnoDB engine in MySQL supports transactions. The MyISAM engine does not support transactions. Therefore, when installing, you must select InnoDB as the database engine. If it is installed, you can switch the engine for all tables in phpMyAdmin.