We need to use SQL statements in many places, such as batch modifying (replacing) content, calling data content, etc. there is a template label {dede:sql /} in the system template for calling data. We can learn how to use this label by referring to the template label instructions in the help center.
Of course, before using and learning SQL statements, we need to have a general understanding of the database structure of Zhimeng. We can simply understand these contents through the database description of the help center.
The following are the references:
Tag name: sql function description: used to obtain the returned content from an sql query in the template. Scope of application: global basic syntax: {dede:sql = ""} underlying template {/ dede:sql} Parameter Description: sql = "" complete sql query statement underlying template field: all fields found in the sql statement can be called with [field: field name /]
Application example:
1. Call the article content published by a specific member
{dede:sql sql='Select * from dede_archives where mid=1'}
<a href='/plus/view.php?aid=[field:id/]' target='_blank'>[field:title/]</a>
{/dede:sql}
mid is the user ID. you can refer to Dede in secondary development_ Introduction to data table fields of Archives
Functional SQL statement collation:
Function Description: add custom attribute
Related statements:
The following are the references:
insert into `dede_arcatt`(sortid,att,attname) values(9,'d','comment');
alter table `dede_archives` modify `flag` set ('c','h','p','f','s','j','a','b','d') default NULL;
Function Description: assign values to authors and sources in batch
Related statements:
The following are the references:
UPDATE dede_archives SET writer='Value to assign' WHERE writer=''; UPDATE dede_archives SET source='Value to assign' WHERE source='';
Function Description: delete comments on the specified IP
Related statements:
The following are the references:
DELETE FROM `dede_feedback` WHERE `dede_feedback`.`ip` = '000.000.000.000' 000.000.000.000 For spammers IP
Function Description: clear the keyword field in the article
Related statements:
The following are the references:
update dede_archives set keywords=''
Function Description: batch replacement release time, receipt time and update time
Related statements:
The following are the references:
The first step. Add an article in the background.
Get a time, such as 2009-01-13 14:13:32, which can be seen from the management article.
Step 2: execute the SQL statement select * from Dede in the background_ archives order by id DESC limit 1
In this way, you can see all the field values of the article you just added.
Observe the following data:
pubdate: 1231846313
senddate: 1231846313
sortrank: 1231846313
1231846313 is the time data.
Then there is replacement.
UPDATE dede_archives SET sortrank = 1231846313; UPDATE dede_archives SET senddate = 1231846313; UPDATE dede_archives SET pubdate = 1231846313;
Function Description: batch modify columns to dynamic or static
Related statements:
The following are the references:
UPDATE `dede_arctype` SET `isdefault` = '-1' dynamic UPDATE `dede_arctype` SET `isdefault` = '1' static state
Function Description: batch replacement of SQL statements
Related statements:
The following are the references:
update `dede_addonarticle` set body=REPLACE(body,'forum','community') where body like "%forum%"
The above SQL statement is used to find the phrases with "forum" in all articles and replace the forum with "community"
Data call SQL statement collation:
Label Description: common content statistics code
Relevant labels:
The following are the references:
·Total articles: * * articles
{dede:sql sql="select count(*) as c from dede_archives where channel=1"}. There are: [field:c /] articles {/ dede:sql}
·Atlas: * * in total
{dede:sql sql="select count(*) as c from dede_archives where channel=2"} · common Atlas: [field:c /] pieces {/ dede:sql}
·Total software: * * PCs
{dede:sql sql="select count(*) as c from dede_archives where channel=3"} · total software: [field:c /] pieces {/ dede:sql}
·Total comments: * * articles
{dede:sql sql="select count(*) as c from dede_feedback"} · common comments: [field:c /] items {/ dede:sql}
·Total members: * * members
{dede:sql sql="select count(mid) as c from dede_member"} · total member: [field:c /] name {/ dede:sql}
·Article reading: * * person times
{dede:sql sql="select sum(click) as c from dede_archives"} article reading: [field:c /] person times {/ dede:sql}
·Update today: * * articles
{dede:sql SQL = "select count (*) as C from dede_archives where PubDate > unix_timestamp (curdate())"} update today: [field:c /] article {/ dede:sql}
Total messages: {dede:sql sql="select count(*) as cc From dede_guestbook"}[field:cc/]{/dede:sql}
Call Description: call the post with picture attached to Discuz forum
Relevant labels:
The following are the references:
{dede:sql sql="SELECT` cdb_p_w_uploads`.`aid`, `cdb_p_w_uploads`.`p_w_upload`,`cdb_threads`.`tid`, `cdb_threads`.`fid`, `cdb_threads`.`subject` FROM `cdb_p_w_uploads` LEFT JOIN `cdb_threads` ON `cdb_threads`.`tid`=`cdb_p_w_uploads`.`tid` WHERE `cdb_p_w_uploads`.`readperm`='0' AND `displayorder`>='0' AND `filetype`='p_w_picpath/pjpeg' GROUP BY tid LIMIT 0,2"}
<li><A href="/bbs/viewthread.php?tid=[field:tid /]"><IMG src="/bbs/p_w_uploads/[field:p_w_upload/]"></A></li>
<li><A href="/bbs/viewthread.php?tid=[field:tid /]">[field:subject function="cn_substr('@me',30)" /]</A></li>
{/dede:sql}
Call Description: call the latest UCHOME log
Relevant labels:
The following are the references:
{dede:sql sql="Select subject,viewnum,blogid,uid From uchome_blog order by blogid desc limit 0,8"}
<li><A href="http://www.dedecms.com/space.php?uid=[field:uid/]&do=blog&id=[field:blogid/]">[field:subject function="cn_substr('@me',24)" /]</A></li>
{/dede:sql}
Call Description: member points ranking
Relevant labels:
The following are the references:
{dede:sql sql="Select mid,userid,uname,scores From dede_member order by scores desc limit 0,10"}
<dd><span class="name"><a href='[field:global name='cfg_cmspath'/]/member/?[field:userid/]/'>[field:uname/]</a>
</span><span class="jifen">integral[field:scores/]</span></dd>
{/dede:sql}
Call Description: the latest product call method of the enterprise (picture + title)
Relevant labels:
The following are the references:
{dede:sql sql="SELECT a.id,a.litpic,a.title FROM dede_addonshop p left join dede_archives a on a.id = p.aid order by a.id desc LIMIT 0 , 4"}
<li><div><a href="/plus/view.php?aid=[field:aid/]" title="[field:title/]"><img src="[field:litpic/]" alt="[field:title/]"/></a></div><a href="/plus/view.php?aid=[field:aid/]" title="[field:title/]">[field:title/]</a></li>
{/dede:sql}
Call Description: call the latest added enterprise and its industry code to the home page
Relevant labels:
The following are the references:
{dede:sql sql="SELECT m.mid,m.mtype,m.userid,m.matt,c.mid,c.company,c.comface,d.ename,d.evalue,d.egroup FROM dede_sys_enum as d ,dede_member as m left join dede_member_company c on m.mid = c.mid where m.mtype ='enterprise' and m.matt = 1 and c.vocation=d.evalue and d.egroup='vocation' LIMIT 0 , 10"}
[field:company/]
[field:ename/]
{/dede:sql}
Call Description: recommended member (with user avatar)
Related labels: the following are the referenced contents:
{dede:sql sql="SELECT mid,mtype,userid,uname,matt,face
FROM dede_member
where matt = 1 and mtype='personal'
LIMIT 0 , 10"}
head portrait:<img src="[field:face runphp='yes']
if(!@me)@me = 'http://bbs.dedecms.com/attachment/upload/87/11787.gif';
[/field:face]" />
user name:<a href='[field:global name='cfg_cmspath'/]/member/?[field:userid/]/'>[field:uname/]</a>
{/dede:sql}
[field:face runphp='yes']
if(!@me)@me = 'http://bbs.dedecms.com/p_w_picpath/post/smile/default/14.gif';
[/field:face]
User avatar image
[img]http://bbs.dedecms.com/attachment/upload/87/11787.gif[/img]
If the user's Avatar is empty, please change the image to be displayed by yourself
Call Description: recommended enterprise
Relevant labels:
The following are the references:
{dede:sql sql="SELECT m.ID,m.type,m.userid,m.matt,m.spacep_w_picpath,c.id,c.comname
FROM dede_member m left join dede_member_cominfo c on m.ID = c.id
where m.type = 1 and m.matt = 1
LIMIT 0 , 10"}
head portrait:<img src="[field:spacep_w_picpath runphp='yes']
if(!@me)@me = 'http://bbs.dedecms.com/p_w_picpath/post/smile/default/14.gif';
[/field:spacep_w_picpath]" />
user name:<a href='[field:global name='cfg_cmspath'/]/member/?[field:userid/]/'>[field:comname/]</a>
{/dede:sql}Call Description: UCenter Home member call (with Avatar)
Relevant labels:
The following are the references:
{dede:sql sql="SELECT * FROM `uchome_space` WHERE `avatar` =1 LIMIT 0 , 10"}
<a href="You install UCenter Home Address of/space.php?uid=[field:uid/]">
<img src='You install UCenter Address of/avatar.php?uid=[field:uid/]&size=[field:small/]' border='0' width='99' height='88' >
<br />
[field:username/]</a>
{/dede:sql}