当前位置：网站首页 > Zblog教程 > 正文

zblog php链式SQL查询数据库教程

作者：xlnxin发布时间：2021-04-15分类：Zblog教程浏览：421

导读：对zblogphp主题进行二次开发时经常会查询调用数据库信息，但zblogphp查询数据库并不是使用常规的SQL语句，而是使用链式SQL调用，因此很多刚涉及zblog...

对zblog php主题进行二次开发时经常会查询调用数据库信息，但zblog php查询数据库并不是使用常规的SQL语句，而是使用链式SQL调用，因此很多刚涉及zblog php的用户不知道如何通过SQL对数据库进行查询。下面是结合zblog官方wiki给出的例子讲解一下如何运用链接SQL调用来查询数据库。

创建一外链式SQL实例

1	$db = new SQLMySQL($GLOBALS['zbp']->db);

或：

1	$db = $zbp->db->sql->get()

SQL常用语句对照

SELECT – $db ->select
INSERT – $db->insert
DELETE – $db->delete
DROP – $db->drop
CREATE – $db->create
UPDATE – $db->update

以此类推…

具体示例

如搜索log_ID从1~3的文件，SQL语句如下：

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

转换成链式SQL代码，并执行获取数据，如下：

$db = $zbp->db->sql->get();
$sql = $db ->select("zbp_post")
       ->where(array('between', 'log_ID', "1", "3"))
       ->sql;

提示：每一个where就是一个数组，如以下语句：

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

那么链接SQL代码的where部分就应该如下：

$db = $zbp->db->sql->get();
$sql = $db ->select("zbp_post")
       ->where(array('between', 'log_ID', "1", "3"),array('=','log_Type','1'))
       ->sql;

更多细节请根据以下测试代码自行参考使用吧

<?php
class ClassSQLGlobalTest extends PHPUnit_Framework_TestCase
{
    protected $backupGlobalsBlacklist = ['zbp'];
    protected static $db = null;
 
    public function setUp() {
        /**
         * Use MySQL to test Global
         */
        self::$db = new SQLMySQL($GLOBALS['zbp']->db);
    }
 
    public function tearDown() {
        self::$db->reset();
        self::$db = null;
    }
 
    /**
     * Basic test cases
     * 以下都是通过我们测试的案例。你可以将本文档放到本地，参考其中代码添加自己的案例进行测试
    **/ 
    public function testSelect() {
        self::$db->select('zbp_post');
        $this->assertEquals('SELECT * FROM  `zbp_post` ', self::$db->sql); //assertEquals用于比对拼接后的sql语句是否与原生sql一致
    }
 
    public function testInsert() {
        self::$db = new SQLMySQL($GLOBALS['zbp']->db);
        self::$db->insert('zbp_post')->data(array('log_Title' => 'test'));
        $this->assertEquals('INSERT INTO  `zbp_post`  (`log_Title`)  VALUES (  \'test\'  )', self::$db->sql);
    }
 
    public function testDelete() {
        self::$db = new SQLMySQL($GLOBALS['zbp']->db);
        self::$db->delete('zbp_post');
        $this->assertEquals('DELETE FROM  `zbp_post` ', self::$db->sql);
    }
 
    public function testDrop() {
        self::$db = new SQLMySQL($GLOBALS['zbp']->db);
        self::$db->drop('zbp_post');
        $this->assertEquals('DROP TABLE  `zbp_post` ', self::$db->sql);
    }
 
    public function testCreate() {
        self::$db = new SQLMySQL($GLOBALS['zbp']->db);
        self::$db
        ->create('zbp_post')
        ->data(array(
            'ID' => array('log_ID', 'integer', '', 0)
        ));
        $this->assertEquals('CREATE TABLE IF NOT EXISTS zbp_post  ( log_ID int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (log_ID) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;', self::$db->sql);
    }
 
    public function testUpdate() {
        self::$db = new SQLMySQL($GLOBALS['zbp']->db);
        self::$db
        ->update('zbp_post')
        ->data(array('log_Title' => 'test'));
        $this->assertEquals('UPDATE  `zbp_post`   SET log_Title = \'test\'', self::$db->sql);
    }
 
    public function testCount() {
        self::$db = new SQLMySQL($GLOBALS['zbp']->db);
        $this->assertEquals('SELECT  COUNT(log_id) AS countid  FROM  `zbp_post` ', self::$db
            ->select("zbp_post")
            ->count('log_id', 'countid')
            ->sql
        );
        $this->assertEquals('SELECT  COUNT(log_id) AS countid  FROM  `zbp_post` ', self::$db
            ->select("zbp_post")
            ->count(array('log_id', 'countid'))
            ->sql
        );
        $this->assertEquals('SELECT  COUNT(log_id)  FROM  `zbp_post` ', self::$db
            ->select("zbp_post")
            ->count('log_id')
            ->sql
        );
        $this->assertEquals('SELECT  COUNT(log_id),log_authorid  FROM  `zbp_post` ', self::$db
            ->select("zbp_post")
            ->count('log_id')
            ->column('log_authorid')
            ->sql
        );
    }
 
 
    public function testWhere() {
        self::$db = new SQLMySQL($GLOBALS['zbp']->db);
        $this->assertEquals('SELECT * FROM  `zbp_post`  WHERE  `log_ID` = \'1\' ',
            self::$db
            ->select("zbp_post")
            ->where(array('=', 'log_ID', "1"))
            ->sql
        );
        $this->assertEquals('SELECT * FROM  `zbp_post`  WHERE  `log_ID` = \'1\' ',
            self::$db
            ->select("zbp_post")
            ->where(' `log_ID` = \'1\' ')
            ->sql
        );
        $this->assertEquals('SELECT * FROM  `zbp_post`  WHERE  EXISTS ( SELECT 1 ) ',
            self::$db
            ->select("zbp_post")
            ->where(array('exists', 'SELECT 1'))
            ->sql
        );
        $this->assertEquals('SELECT * FROM  `zbp_post`  WHERE  `log_ID` = \'1\' ',
            self::$db
            ->select("zbp_post")
            ->where(array('=', 'log_ID', "1"))
            ->sql
        );
        $this->assertEquals('SELECT * FROM  `zbp_post`  WHERE  (`log_ID` BETWEEN \'1\' AND \'3\') ',
            self::$db
            ->select("zbp_post")
            ->where(array('between', 'log_ID', "1", "3"))
            ->sql
        );
        $this->assertEquals('SELECT * FROM  `zbp_post`  WHERE  ((1 = 1) AND ( (`log_Title` LIKE \'%Test%\') ) )',
            self::$db
            ->select("zbp_post")
            ->where(array('search', 'log_Title', "Test"))
            ->sql
        );
        $this->assertEquals('SELECT * FROM  `zbp_post`  WHERE  ((1 = 1) AND ( `log_ID` = \'1\'  OR  `log_Title` = \'2\' ) )',
            self::$db
            ->select("zbp_post")
            ->where(array('array',
                array(
                    array('log_ID', '1'),
                    array('log_Title', '2')
                )
            ))
            ->sql
        );
        $this->assertEquals('SELECT * FROM  `zbp_post`  WHERE  ((1 = 1) AND ( `log_ID` <> \'1\'  OR  `log_Title` <> \'2\' ) )',
            self::$db
            ->select("zbp_post")
            ->where(array('not array',
                array(
                    array('log_ID', '1'),
                    array('log_Title', '2')
                )
            ))
            ->sql
        );
        $this->assertEquals('SELECT * FROM  `zbp_post`  WHERE  ((1 = 1) AND ( `log_ID` LIKE \'1\'  OR  `log_Title` LIKE \'2\' ) )',
            self::$db
            ->select("zbp_post")
            ->where(array('like array',
                array(
                    array('log_ID', '1'),
                    array('log_Title', '2')
                )
            ))
            ->sql
        );
        $this->assertEquals('SELECT * FROM  `zbp_post`  WHERE  ((1 = 1) AND ( `log_ID` ILIKE \'1\'  OR  `log_Title` ILIKE \'2\' ) )',
            self::$db
            ->select("zbp_post")
            ->where(array('ilike array',
                array(
                    array('log_ID', '1'),
                    array('log_Title', '2')
                )
            ))
            ->sql
        );
        $this->assertEquals('SELECT * FROM  `zbp_post`  WHERE  ((1 = 1) AND (`log_ID` IN ( \'1\' ,  \'2\' ,  \'3\' ,  \'4\' ) ) )',
            self::$db
            ->select("zbp_post")
            ->where(array('IN', 'log_ID', array(1, 2, 3, 4)))
            ->sql
        );
        $this->assertEquals('SELECT * FROM  `zbp_post`  WHERE  (log_ID IN ( \'1\' ,  \'2\' ,  \'3\' ,  \'4\' )) ',
            self::$db
            ->select("zbp_post")
            ->where(array('IN', 'log_ID', ' \'1\' ,  \'2\' ,  \'3\' ,  \'4\' '))
            ->sql
        );
        $this->assertEquals('SELECT * FROM  `zbp_post`  WHERE (`log_Meta` LIKE \'%s:10:\"meta_Value\";%\')',
            self::$db
            ->select("zbp_post")
            ->where(array('META_NAME', 'log_Meta', 'meta_Value'))
            ->sql
        );
        $this->assertEquals('SELECT * FROM  `zbp_post`  WHERE (`log_Meta` LIKE \'%s:9:\"meta_Name\";s:10:\"meta_Value\"%\')',
            self::$db
            ->select("zbp_post")
            ->where(array('META_NAMEVALUE', 'log_Meta', 'meta_Name', 'meta_Value'))
            ->sql
        );
        $this->assertEquals('SELECT * FROM  `zbp_post`  WHERE 1=1',
            self::$db
            ->select("zbp_post")
            ->where(array('CUSTOM', '1=1'))
            ->sql
        );
    }
 
    public function testOrderby() {
        $this->assertEquals('SELECT * FROM  `zbp_post`  ORDER BY bbb desc, aaa ',
            self::$db
            ->select("zbp_post")
            ->orderBy(array(array('bbb' => 'desc'), 'aaa'))
            ->sql
        );
        $this->assertEquals('SELECT * FROM  `zbp_post`  ORDER BY bbb desc, aaa ',
            self::$db
            ->select("zbp_post")
            ->orderBy(array('bbb' => 'desc'), 'aaa')
            ->sql
        );
        $this->assertEquals('SELECT * FROM  `zbp_post`  ORDER BY aaaa ',
            self::$db
            ->select("zbp_post")
            ->orderBy('aaaa')
            ->sql
        );
        $this->assertEquals('SELECT * FROM  `zbp_post`  ORDER BY a , b , c ',
            self::$db
            ->select("zbp_post")
            ->orderBy(array('a', 'b', 'c'))
            ->sql
        );
    }
 
    public function testGroupBy() {
        $this->assertEquals('SELECT * FROM  `zbp_post`  GROUP BY bbb, aaa',
            self::$db
            ->select("zbp_post")
            ->groupBy(array('bbb', 'aaa'))
            ->sql
        );
        $this->assertEquals('SELECT * FROM  `zbp_post`  GROUP BY bbb, aaa',
            self::$db
            ->select("zbp_post")
            ->groupBy('bbb', 'aaa')
            ->sql
        );
        $this->assertEquals('SELECT * FROM  `zbp_post`  GROUP BY aaaa',
            self::$db
            ->select("zbp_post")
            ->groupBy('aaaa')
            ->sql
        );
 
    }
 
    public function testHaving() {
        $this->assertEquals('SELECT * FROM  `zbp_post`  HAVING   bbb > 0 AND aaa < 0',
            self::$db
            ->select("zbp_post")
            ->having(array('bbb > 0', 'aaa < 0'))
            ->sql
        );
        $this->assertEquals('SELECT * FROM  `zbp_post`  HAVING   bbb > 0 AND aaa < 0',
            self::$db
            ->select("zbp_post")
            ->having('bbb > 0', 'aaa < 0')
            ->sql
        );
        $this->assertEquals('SELECT * FROM  `zbp_post`  HAVING   aaaa > 0',
            self::$db
            ->select("zbp_post")
            ->having('aaaa > 0')
            ->sql
        );
    }
 
    public function testLimit() {
        $this->assertEquals('SELECT * FROM  `zbp_post`  LIMIT 5',
            self::$db
            ->select("zbp_post")
            ->limit(5)
            ->sql
        );
        $this->assertEquals('SELECT * FROM  `zbp_post`  LIMIT 1 OFFSET 10',
            self::$db
            ->select("zbp_post")
            ->limit(10, 1)
            ->sql
        );
        $this->assertEquals('SELECT * FROM  `zbp_post`  LIMIT 1 OFFSET 10',
            self::$db
            ->select("zbp_post")
            ->limit(array(10, 1))
            ->sql
        );
    }
 
    public function testColumn() {
        $this->assertEquals('SELECT  *  FROM  `zbp_post` ',
            self::$db
            ->select("zbp_post")
            ->column('*')
            ->sql
        );
        $this->assertEquals('SELECT  * AS sum,log_Content AS content,log_AuthorID AS author,log_Title,log_PostTime,log_ID AS ID  FROM  `zbp_post` ',
            self::$db
            ->select("zbp_post")
            ->column(array('*', 'sum'))
            ->column(array(array('log_Content', 'content'), array('log_AuthorID', 'author')))
            ->column('log_Title', 'log_PostTime', array('log_ID', 'ID'))
            ->sql
        );
    }
 
    public function testOption() {
        $this->assertEquals('SELECT * FROM  `zbp_post` ',
            self::$db
            ->select("zbp_post")
            ->option(array('test' => 'test'))
            ->sql
        );
    }
 
    public function testInvalid() {
        $this->assertEquals('SELECT * FROM  `zbp_post` ',
            self::$db
            ->select("zbp_post")
            ->orderBy(null)
            ->sql
        );
        $this->assertEquals('SELECT * FROM  `zbp_post` ',
            self::$db
            ->select("zbp_post")
            ->groupBy(null)
            ->sql
        );
        $this->assertEquals('SELECT * FROM  `zbp_post` ',
            self::$db
            ->select("zbp_post")
            ->having(null)
            ->sql
        );
        $this->assertEquals('SELECT * FROM  `zbp_post`  WHERE  (1 = 1) ',
            self::$db
            ->select("zbp_post")
            ->where(array('array', null))
            ->sql
        );
        $this->assertEquals('SELECT * FROM  `zbp_post`  WHERE  (1 = 1) ',
            self::$db
            ->select("zbp_post")
            ->where(array('IN', 'log_ID', null))
            ->sql
        );
        $this->assertEquals('SELECT * FROM  `zbp_post`  WHERE  (1 = 1) ',
            self::$db
            ->select("zbp_post")
            ->where(array('IN', 'log_ID', array()))
            ->sql
        );
        $this->assertEquals('SELECT * FROM  `zbp_post`  WHERE  (1 = 1) ',
            self::$db
            ->select("zbp_post")
            ->where(array('array', array()))
            ->sql
        );
        $this->assertEquals('SELECT * FROM  `zbp_post`  WHERE  (1 = 1) ',
            self::$db
            ->select("zbp_post")
            ->where(array('array', array()))
            ->sql
        );
        $this->assertEquals('SELECT * FROM  `zbp_post`  WHERE  (1 = 1) ',
            self::$db
            ->select("zbp_post")
            ->where(array('meta_namevalue', array()))
            ->sql
        );
        $this->assertEquals('SELECT * FROM  `zbp_post`  WHERE  (1 = 1) ',
            self::$db
            ->select("zbp_post")
            ->where(array('meta_name', array()))
            ->sql
        );
    }
 
    /**
     * @expectedException        Exception
     * @expectedExceptionMessage Unimplemented fuck
     */
    public function testExpectionInCall() {
        self::$db->fuck();
    }
}
?>

扩展：其它的用法

global $zbp;
$sql = $zbp->db->sql->Select(
	$zbp->table['Comment'],
	array('COUNT(comm_ID) AS cnt, comm_Name, comm_HomePage , comm_Email'),
	array(
		array('<>', 'comm_Name', '访客'),
		array('=', 'comm_AuthorID', 0),			
		array('CUSTOM', '1=1 GROUP BY comm_HomePage')
	),
	array('comm_PostTime' => 'DESC'),
	10,
	null
);

zblog wiki：https://wiki.zblogcn.com/doku.php?id=zblogphp:development:features:1.5:chainquery

标签：zblog 教程主题数据库 mysql

上一篇：zblog调用后台分类管理的所有分类目录
下一篇：zblog错误提示E_WARNING : mysqli_query(): (HY000/1194)解决方法