对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 ->selectINSERT – $db->insertDELETE – $db->deleteDROP – $db->dropCREATE – $db->createUPDATE – $db->update

以此类推…

具体示例

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

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

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

1234 $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部分就应该如下：

1234 $db = $zbp->db->sql->get();$sql = $db ->select("zbp_post") ->where(array(’between’, ‘log_ID’, "1", "3"),array(’=’,’log_Type’,’1’)) ->sql;
更多细节请根据以下测试代码自行参考使用吧
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392 <?phpclass 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(); }}?>
扩展：其它的用法
12345678910111213 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

声明：本站所有文章，如无特殊说明或标注，均为本站原创发布。任何个人或组织，在未征得本站同意时，禁止复制、盗用、采集、发布本站内容到任何网站、书籍等各类媒体平台。如若本站内容侵犯了原著者的合法权益，可联系我们进行处理。