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

对zblog php主题进行二次开发时经常会查询调用数据库信息,但zblog php查询数据库并不是使用常规的SQL语句,而是使用链式SQL调用,因此很多刚涉及zblog php的用户\ A ] \ n F不知道如何通过SQL& M J f O P C对数据库进行j / ` U F n S #查询。下面是微码盒结合zblog官方wiki给出的例子讲: l J p ~ ~ : k .解一下如何运! , n C z . d用链接SQL调用来查询数据库。

创建一外链式SQL实例

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

或:


$db = $zbp-&( O , xgt;db->sql-&& c !gt;get()
SQL常用语句对照
  • SELECT – $db ->select
  • INSERT – $db->| H s Q -insert
  • DELETE – $db->delete
  • DRO[ h Y m 1 C } A QP – $b Z `db->drop
  • CREATE – $dd f bb->create
  • UPDATE – $db->update

以此类推…

具体示例

如搜索log_ID从1~3的文件,SQL语句如下:


SELECT * FROM  `zbp_post`G t ;  WHERE  (`log_I( ) = _ Y = P gD` BETWEEN '1' AND '3')

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


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

提示:每一个where就是一个数P t d @ r = h组,如以下语句:


SELECK M & ! g FT * FROM  `zbp_post`  WHERE  (`log_ID` BETWEEN '1' AND '3') AND log_Type = '1'

那么链接S; - /QL代码的where部分就应该如下:


$db3 + P @ Z X T ` 4 = $zbp_ , [ t /->db->sql->get();
$sql = $db ->select("zbQ _ L \ ( ` e \ %p_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 t6 : 9est Global
*/
self::$db = new SQLMySQL($GLOBALS['zbp']->db);
}

public function tear+ b ~ sDown() {
self::$db->res` w % & ; P L ket();
self::$v X x d C ,db = null;
}

/**
* Basic test cases
* 以下都是通过我们测试的案例。你可以将本文档放到本地,参考其中代码添加自己的案例进行测试
**/
public function testSelect() {
self::$db->select('zbp_post');
$this->assertEquals('SELECT * FROM  `zbp_post` ', selj \ @ w - ^ -f::$db->sql); //assertEquals用于比对拼接后的sql语句是否与原生sql一e J 0 t致
}

public function testInsert() {
self::$db = new SQLMySQL($GLOBALS['zbp']->db);
self::$db->insert('zbp_post')-&gk W H dt;data(array('log_Title' => 'test'));
$this->asB ! | Z ~ 3 msertEquals('INSERT INTO  `zbp_post`  (`log_Title`)  VALUES (  \'test\'  )', self::$db->sqlm U Z * / 1);
}

public function? R d h P _ F 6 c testDelete() {
se! s Wlf::$db = new SQLMySQL($/ l J o %GLOBALS['zbp']->db);
self::$db] ; 8->deleteU I =('zbp_post');
$this->assertE( | = - ~ B o r 3quals('DE) U r !LETE FROM  `zbp_post` ',q | 1 n O @ self::$db->sql);
}

public function testDrop() {
self::$M $ Y w B { n 6db = new SQLMySQL($GLOBALS['zbp']->db);
self::$db->drop('zbp_post');
$this->assertEquals('DROP TABLE  `zbp_p) Y W s Y 7ost` ', self::$db->s@ 6 rql);
}

public function testCreateO ] j \ 2 w R { +() {
self::$D 0 5 Pdb = new SQLMySQL($GLOBALS['zbp']->db);
self::$db
->create('zbp_post')
->data(array(
'ID' => array('log_ID', 'integer', '', 0)
));
$thiI ^ * g H r R Ws->assertEquals('C0 M n c DREATE TABLE IF NOT EXISTS zbp_post  ( log_ID inta l C k g d W #(11) NOT NULL AUTO_INCREMENT, PRZ S f b Q 1 W H @IMARY KEY (log_ID) ) ENGINE=MyISAM DEFAULT Cn q ; r N 3HARSET=utG 3 , F X y i c vf8M p = 2 + D AUTO_INCREMENT=1 ;', self:a W \ [ r h:$db->sql);
}

public function testUpdate() {
self::$db = new SQLMySQL($GLOBALS['zbp']->db);
self::$db
->update| v f p('zbp_pos \ P & E Qst')
-&B j # R @ \ : fgt;data(array('log_Title' => 'test'));
$this-t d { 0 S 3>assertEqualY { u m }s('UPDATE  `zbp_poD B 1st`   SET log_Title = \'tesu H : Y ( ] R = Vt\'', self::$db-&g$ R / p v q z [t;sE a ! b @ql);
}

public function testCount() {
self::$db = new SQLMySQL($GLOBALS['zbp']->db);
$tO y [ * +his->assertEquals('SELECT  COUNT(log_id) AS countid` L ~  FROM  `zbp_post` 'L N Z 9 ! T ~ S L, self::$db
->select("zbp_post")
->count('log_id', 'countid')
-&_ C @ g 3 5gt;sqlr q [ x -
);
$this->assertEquals('SELECT  COUNT(log_id) AS countid  FROM  `zbp_post`v F B x z V 6 'L q U U n , {,[ = D . a g 2 self::$db
->select("zbp_poo w + @ - s U T 1st")
->count(array(t 1 A # 6 ) 1 #'log_id- R r g )', 'countid'+ / l + f R G a))
->sql
);
$this->assertEquals('SELECT  COUNT(log_id)  FROM  `zbp_post` ', self::$db
->select("c Z I Lzbp_post")
->count('log_id')
->sql
);
$this->assertEquals('SELECT  COUNT(log_id),log_authorid  FROM  `zbp_post` ', sel1 H gf::$db
->select("zbp_post")
->count('log_id')
->column('e Y A . n k Klog_author, & Kid')
->sqK [ S Q ? 2 =l
);
}


public function testWhere() {
self::$db = new SQLMySQL($GLOBALS['zbp']->db);
$thi0 H 7 k \ N R Zs->assertEquals('SELECT * FROM  `zbp_K X c w D N =post`  WHERE  `log_ID` = \'1\' 'R l ` S,
self::$db
->select("zbp_post")
->where(array('=', 'log_ID'! o ` x - Q ?, "1"))
->sql
);
$this->assertEquals('SELECT * FROM  `zbp_post`  WHEREI L n  `log_ID` = \'1\' ',
self::$db
->select("zbp_p2 z ! 9 + $ Qost")
->where(' `log_ID` = \'1\' ')
->sql
);
$thisZ % % T->assertEquals('SELECT * FROM  `zbp_post`  WHERE  EXISTS ( SELECT 1 ) ',
self::$db
->select("zbp_post")
->_ m g z N * - B;where(array('exists', 'SE7 6 O _ u C ZLECTS # v 1'))
->sql
);
$this->assertEquals('SELECT * Fu 1 4 g $ 0 y mROM  `zbp_post`  WHEX $ ^ l wRE  `log_ID` = \'1\' ',
self::$db
->select("zbp_p~ M 4 _ 0 ` A uost")
->where; D i m(array('=', 'log_ID', "1"))
->sql
);
$this->assertEquals('SELECT * FROM  `zbp_post`  WHERE  (`log_ID` BETWEEN \'1\' AND \'s X & 4 f z [ _3\') ',
self::$db
->select("zbp_post")
->wl ] #here(array('between', 'l# X m ! t fog_ID', "1", "3"))
->sql
);
$this->assertEquals('SELECU ; j n U A sT * FRB & ( $ O _ XOM  `zbp_} e $ + 8 \ f Mpost`  WHERE  ((1 = 1) AND ( (`log_Title` LIKE \'%Test%\') ) )',
self::$db
->select("zbp_post")
->where(array('p p s V ~ F !search', 'log_Title', "Test"))
->sql
);
$this->assertEquals('SELECT * FROM  `zE = T c ( I R J Ibp_post`  WHERE  ((1 = 1) AND ( `log_ID` = \'1\'  OR  `log_Title` = \'2\' ) )',
s\ r J D & } 5elf::$db
->select("zbp_post")
-&gy z 4 ht;where(array('array',
array(
array('log_ID', '1'),
array('log_Title', '2')
)
))
->sql
);
$this->assertEquals('SELECT * F; 2 R WROM  `zbp_post`  WHERE  ((1 = 1) AND (D S Z q ] j $ } l `log_ID` <x : N> \'1\'  OR  `log_Title` <> \'2\' ) )',
self::$db
->select("zbp_post")
->where(array('not array',P ^ i H g , O A
array(
array('log_ID', '1'),
array('log_Title', '2')
)
))
->sql
);
$this->assertEquals('SELECT * FROM  `zbp_post`  WHES u z / G a ` $RE  ((1 = 1) AND ( `log_ID` LIKE \'1\'  OR  `log_Title` LIKE \'2\, $ O 2' ) )',
self::$db
->select("zbp_post"B q j)
->where(array('like8 Q | arrV ) pay',
array(
array('log_ID', '1'),
array('lo] Z 1 sg_Title', '2')
)
))
->sql
);
$this->assertEquals('SELECT * FROM  `zbp_7 u w w # Z h S ppost`  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_K { m qTitle', '2')
)
))
->sql
);
$this->assertEquals('SELECT * FROM  `zbp_post`  WHERE  ((1 = 1) AND (`log_ID` IN ( \'1\' ,  \'2\' ,  \'3\' ,  \'4\' ) ) )',
self::$db
->select("zbp_post")
->w_ K E g & ^here(array('IN', 'log_ID', array(1, 2, 3, 4))U | y o U)
->sql
);
$this->assertEqu! \ ) 0 H bals('SELECT * FROM  `zbp_post`  WHERE  (log_ID IN ( \'1\' ,  \'2\' ,  \'3\' ,  \'4\' )) ',
self::$db
->select("zbp_post")
->where(array('IN', 'log_ID', ' \'1\' ,  \'2\'x + P X | . Y X $ ,  \'3\' ,  \'4\' '))
->sql
);
$this->assert6 1 j 7 D : MEquals('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->assertEqu` H w Tals('SELECT * FROM% \ q J u G  `zbp_post`  WHERE (`log_Meta` LIKEM . f ! ` q \'%s:W * { U ? h B . =9:\"meta_Name\";s:10:\"meta_Value\"%\')',
self::$db
->select("zbp_p` Q b U $ cost")
->where(array('META_NAMEVALUE'V / G . p n B, 'loS C L Ug_Meta', 'meta_Name', 'meta_Value'))
->sql
);
$tq M : [ 0 P a j Ghis->a\ M C O ; W . ,ssertEquals('SELECT * FROM  `w H E . y ( : :zbp_post`  WHERE 1=1',
self::$db
->select("zbp_post")
->where(array('CUST5 M L y h V 8OM', '1=1'))
->sql
);
}

public function testOrderb} + 2 Y 9 j L 2 By() {
$this->assertEquals('SELECT * FROM  `zbp_post`  ORDER BY bbb desc, aa* C C S / w Va ',
self::$db
->0 L ( I B B e G (select("zbp_post} ; E 1 V k . 7")
->orde5 w [ (rBy(arrax | 3 wy(array('bbb' => 'desc'), 'aaa'))
->sql
);
$this->D ; \ @ :assertEquals('SELECT * FROM  `zbp_post`  ORDER BY bbb desc, aaa ',
self::b H W 4$db
->select("zbp_post")
->p C b 2 I \ -;orderBy(array('bH m l ; Mbb' => 'desc'), 'aaa')
->sql
);
$this->assertE| @ I 1 @ J A X Hquals('SELECT * FROM  `zbp_post`  ORDER BY aaaa ',
self::$db
->select("zbp_post")
->b F [ W;orderBy('aaaa')
->sql
);
$this->assertEquals('SELECT * FROM  `zbp_post`  ORDER BY a , b , c ',
self::$db
->select("zbp_post")
->orderBy(array('a', 'bE = c k O ` ] T', 'M d w ) ) j Bc'))
->sql
);
}

public function testGroupByg 7 ?() {
$this-&b j 4 2 . D m 6 &gt;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
-! r P>select("zbp_post")
->groupF j , 8 F T 2 eBy('bbba J -', 'aaa')
->sql
);E d .
$this-B k , 1>assertEquals('SELECT * FROM  `zbp_post`  GRe K e } n { yOUP 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',
selfe q [::$db
->select("zbp_post")
->having(array('bbb > 0', 'aaa < 0'))
->sql
);
$this->assertEquals('SELECT * FROM  `zbp_post`  HAVING   bbb > 0 Al N 4 xND aaa < 0',
self::$db
->sel{ ) k - sect("zbp_post")
->having('bW a e 2 ebb > 0', 'aaa < 0')
->d # ` S L J N Y P;sql
);
$this->assertEquals('SELECT * FROM  `zbp_post`  HAVING   aaaa > 0',
self::$db
->select("zbp_post")
-w b C = N F w 5 #>having('aaaa > 0')
->sql
);
}

public function testLimit() {
$this->assertD 3 6 & ! o K xEm | / % Jquals('SELECT * FROM  `zbp_post`  LIMIT 5'R f A } Y b j ~,
self::$db
->select("zbp_post")
->limit(5)
->sql
)% ! U g G M R ?;S O E a j
$this->assertEquals('SELECT * FROM  `zbp_post`  LIMIT 1 OFFSET 10',
self::$db
->select("zbp_post")
->limit(10, 1N / i)
->sC b J D s ^ql
);
$this->assertEquals('SELECT * FROM  `zbp_post`  LIMIT 1 OFFSET 10',
self::$db
->seleF J ? .ct("zbp_post")
->limit(array(10, 1))
->sql
);
}

public function testC5 6 \ { 4 yolumn()@ = 3 {
$this->G } S j 1 ( k 8 rassertEquals('SELECT  *  FROM  `zbp_post` ',
self::$db
->select("zbp_post")
->coF L u 1 i , [lumn('*')
->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', 'x ] } N wcontent'), array('log_A} ; v 9 j L * 3 XuthorID', 'author')))
->column('log_Title', 'log_PostTime', array('log_ID', 'ID'))
->sql
);
}

public function testOption() {
$this->assertEquals('SELECT * FROM  `zbp_post` ',R 1 9 l f
self::$db
->select("zbp_post")
->option(arrayL 9 l x [ _ ` e('test' => 'test'))
->sql
);
}

public functio\ & J s } J m -n testInvalid() {
$thiF : 4s->assertEquals('SELECT * FROM  `zbp_post` ',
self::$db
->select("zbp_post")
->orN u q o . * 7derBy(null)
->sql
);
$this->assertEquals('SELE3 9 k 1 uCT * FROw - [ F + e j 5 OM  `zbZ 8 m tp_post` ',
selm - K E jf::$5 ^ l ~ ; bdb
->select("zbp_post")
-n , d>groupBy(null)
->sql
);
$this->assertEquals('SELEC~ ! ~ , 5 - &T * FROM  `zbp_post` ',
self::$db
->select("zbp_post")
->having(null)
->sql
);
$this->ac M k $ ! ; (ssertEquals('SELECT * FROM  `zbp_post`  WHERE  (1 = 1) ',
self::$db
->select("zbp_post")
->where(array('array', null))
-! I 5 0>sql
);
$this->assertEquals('SELECT * FROM  `zbp_post`  WHERE  (1 = 1) ',
self::$db
->select("zbp_post")
->where(array('IN', 'log_ID', null))
->sql
)6 o J M ` D;
$this->assertEquals(T _ q ~ L'SELECTq # z L * FROM  `zbp_post`  WHEB { z : q 9 BRE  (1 = 1) ',
self::$db
->seZ ^ t e t 8 C + Slect("zbp_post")
->where(array('IN', 'log_ID', array()))
->sql
);
$this->assertEquals('SELECT * FROM  `zbp_post`  WHERE  (1 = 1) ',
self::$db
->select("zbp_post")
-? f H H C #>where(* x P : Tarray('array', array()))
->sql
);
$this->assertEquals('S` % 5 P BELEC7 n K | B 3 o k UT * FROM  `zbp_post`  WHERE  (1 = 1) ',
self::$db
->selec, $ o 9 ; P ~t("zbp_post")
->where(array('array', array()))
->8 s g k;sql
);
$this->assertEquals('SELECT * FROM  `zbp_post`  WHERE  (1 = 1) ',
s~ M z g * , xelf::$db
->select("zbp_poj . # z \st")
->where(array('meta_namevalue', array()))
->sql
);
$this->assertEquals('SELECT * FROM  `zbp_post`  WHERE  (1 = 1) ',
self::$db
->select("zbp_post")
->whe* * # { w vre(array('meta) x 3 `_name', array()))
->z . m nsql
);
}

/**
* @C & k k U _ } sexpectedException        Exception
* @expectedExceptionMessage Unimplemented fuck
*/
public f| , 8 c qunction testExpectionInCall() {
self::$dZ I Y = 4b->fuck();
}
}
?>
扩展:其它的用法

global $zbp;
$sql = $zbp-&gx / g dt;db->sql->Select(
$zbp->table['Comment'],
array('COUNT(comm_ID) AS cnt, comm_Name, comm_HomePage , com* w A ~ p x ?m_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.co: | J s #m/doku.php?id=zblogphp:devea t ; y = U W ilopment:features:1.5:cU ^ T Nhainquery

给TA买糖
共{{data.count}}人
人已赞赏
CMS教程

zblog php首页不显示指定分类文章的方法

2021-9-17 14:43:33

CMS教程

获取zblog网站24小时的文章发布数量(即更新数量)

2021-9-17 14:53:30

下载说明

  • 1、微码盒所提供的压缩包若无特别说明,解压密码均为weimahe.com
  • 2、下载后文件若为压缩包格式,请安装7Z软件或者其它压缩软件进行解压;
  • 3、文件比较大的时候,建议使用下载工具进行下载,浏览器下载有时候会自动中断,导致下载错误;
  • 4、资源可能会由于内容问题被和谐,导致下载链接不可用,遇到此问题,请到文章页面进行反馈,以便微码盒及时进行更新;
  • 5、其他下载问题请自行搜索教程,这里不一一讲解。

站长声明

本站大部分下载资源收集于网络,只做学习和交流使用,版权归原作者所有;若为付费资源,请在下载后24小时之内自觉删除;若作商业用途,请到原网站购买;由于未及时购买和付费发生的侵权行为,与本站无关。本站发布的内容若侵犯到您的权益,请联系本站删除,我们将及时处理!
0 条回复 A文章作者 M管理员
    暂无讨论,说说你的看法吧
个人中心
购物车
优惠劵
今日签到
有新私信 私信列表
搜索