Typecho's API for adding, deleting, modifying and querying databases

Typecho database provides a very easy-to-use API, which is not much different from the native SQL writing method. At the same time, it also handles common SQL security issues, such as SQL injection.

From a practical perspective, this article introduces common scenarios for typecho to operate databases and related API usage.

Table creation and deletion

During the development process of Typecho plug-in, you often need to create your own table. As mentioned above, the query function in the Typecho_Db class can be used to execute all sql statements, so we use query() to create, modify, or delete tables.

$db= Typecho_Db::get();
$prefix = $db->getPrefix();
$db->query('create table '.$prefix.'metas xxxxx');

Note that when using the query method to create a table, you need to manually add the $prefix prefix before the indication, otherwise it will cause confusion during subsequent use.

You can also use table. instead of $prefix, and typecho will automatically recognize and replace it with the specified prefix.

In the same way, to modify or delete the table in the Typecho database, just call query in the same way.

Data query

1. select, query table data

The select statement is arguably the most commonly used SQL call in Typecho plug-in development.

$db = Typecho_Db::get();
$query= $db->select()->from('table.metas');
$result = $db->fetchAll($query);

Description:

typecho中，.号具有特定的意义，这里table.metas表示这是一个metas表。实际上，typecho是自动将table.的字符使用str_replace替换成了config.inc.php中设定的前缀。

举例：

$db->select()->from('table.metas');

将生成SELECT * FROM typecho_metas WHERE (mid = '2' )，其中typecho_是表前缀；

而$db->select()->from('metas');将生成SELECT * FROM metas WHERE (mid = '2' )，注意这里没有了表前缀。

Specify table field query

Sometimes in order to improve query performance, you need to specify several specific fields in the query table. Then you can use the following method:

$query= $db->select('mid','name')->from('table.metas'); 
echo $query; //SELECT `mid` , `name` FROM typecho_metas

If the same field name exists in two tables in a joint query, you can use table. to specify the table name:

$query = $db->select('table.contents.cid')->from('table.contents')->join....

Specify query conditions

Specifying the where statement of SQL query is the most commonly used API call.

$query= $db->select('mid','name')->from('table.metas')->where('mid = ?', 2); 
echo $query; //SELECT * FROM typecho_metas WHERE (`mid` = '2' )

If you need to specify multiple query conditions, just call where multiple times, and the where conditions of the and relationship will be generated.

$db->select('mid','name')->from('table.metas')->where('mid = ?', 2)->where('name like ? ', $name);

Query conditions using OR relationship

You can use the orWhere() function to specify the or condition of the SQL query.

$db->select('mid','name')->from('table.metas')->where('mid = ?', 2)->orWhere('mid = ? ', 3);
//SELECT `mid` , `name` FROM typecho_metas WHERE (`mid` = '2' ) OR (`mid` = '3' )

Specify query range

In scenarios where paging is required, paging is a necessary operation. offset() and limit() are used to specify the starting position and ending position respectively, that is, specifying the query range.

$query = $db->select('mid','name')->from('table.metas')->offset(2)->limit(3);
echo $query;//SELECT `mid` , `name` FROM typecho_metas LIMIT 3 OFFSET 2

Typecho also provides a shorthand method, see page() function.

$query = $db->select('mid','name')->from('table.metas')->page(3,10);
echo $query;//SELECT `mid` , `name` FROM typecho_metas LIMIT 10 OFFSET 20
//表示取第三页，并取10条记录。

Sort query results

In Typecho, use the order() function and Typecho_Db::SORT_DESC to specify the sorting method of query results.

$query = $db->select('mid','name')->from('table.metas')->order('mid',Typecho_Db::SORT_DESC);
echo $query;//SELECT `mid` , `name` FROM typecho_metas ORDER BY `mid` DESC

Tips: Typecho_Db::SORT_ASC 表示升序排序，Typecho_Db::SORT_DESC表示降序排序

Union query

Union query is a common syntax of SQL. In Typecho, the built-in function join() is also used to conveniently perform joint query.

$query = $db->select()
    ->from('table.contents')
    ->join('table.comments', 'table.contents.cid = table.comments.cid',Typecho_Db::LEFT_JOIN)
    ->where('table.contents.type = ?', 'post');
echo $query;
//SELECT * FROM typechocontents LEFT JOIN typecho_comments ON typecho_contents.`cid` = typecho_comments.`cid` WHERE (typecho_contents.`type` = 'post' )

update, update table data

In Typecho, use the update() function to update the table. But note that the update operation needs to be executed with the help of query.

$update = $db->update('table.metas')->rows(array('name'=>'case_in_cn'))->where('mid=?',6);
echo $update;//UPDATE typecho_metas SET `name` = 'some_name' WHERE (`mid`='6' )
//执行后，返回收影响的行数。
$updateRows= $db->query($update);

insert, insert data

In Typecho, use the insert() function to perform table insertion operations. Similarly, the insert operation requires the help of the query function.

$insert = $db->insert('table.metas')
    ->rows(array('mid' => '22', 'name' => 'hello world'));
//将构建好的sql执行, 如果你的主键id是自增型的还会返回insert id
$insertId = $db->query($insert);

delete, delete data

Typecho uses the delete() function to delete rows in the data table. The delete operation is used to delete specified rows in the data table, and it also needs to be executed with the help of the query function.

$delete = $db->delete('table.metas')
    ->where('mid = ?', 2);
//将构建好的sql执行, 会自动返回已经删除的记录数
$deletedRows = $db->query($delete);

Database debugging

During Typecho debugging, it is often helpful to print sql statements. For PHP versions greater than 5.2, just echo $query directly. For versions less than 5.2, you need to explicitly call the __toString() function.

$select = $db->select()->from('table.metas');
//如果版本大于php5.2
echo $select;
//如果小于php5.2
echo $select->__toString();