Commonly used SQL statements in DedeCMS

In DedeCMS V5.3 system, we need to use SQL statements in many places, such as batch modification (replacement) content, data content call, etc. There is a special function in the system template Regarding the template tag {dede:sql/} for calling data, we can refer to the template tag description in the help center to learn how to use this tag.

Of course, we need to have a general understanding of the database structure of DreamWeaver before using and learning SQL statements. We can simply understand these contents through the database description in the help center. (Recommended learning: 梦Weavercms)

The following is the quoted content:

Tag name: sql Function description: Used Use a SQL query to obtain the returned content from the template. Scope of application: Global use Basic syntax: {dede:sql 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 using [field:field name/]

Application example:

1. Call a specific member The content of the published article

{dede:sql sql='Select * from dede_archives where mid=1'}
<a href=&#39;/plus/view.php?aid=[field:id/]&#39; target=&#39;_blank&#39;>[field:title/]</a>
{/dede:sql}

mid is the user ID, you can refer to the introduction of the data table fields of dede_archives in the secondary development

Next, we will collect and sort out some commonly used SQL statements to help everyone work together better Use DedeCMS system.

We divide SQL statements into two categories, functional and content calling. The functional type mainly performs regular operations on the database, such as {insert, update}, and the data calling type is (select ), it is also very simple to use and issue two types of SQL statements.

If it is a functional type, it only needs to be used in the system background [System]-[SQL Command Running Tool]. If it is a data call type of template label, you only need to add a label in the corresponding position of the template.

Functional SQL statement organization:

Function description: Add custom attributes

Related statements:

以下为引用的内容：

insert into `dede_arcatt`(sortid,att,attname) values(9,'d','评论');
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 batches

Related statements:

以下为引用的内容：

UPDATE dede_archives SET writer='要赋的值' WHERE writer='';
UPDATE dede_archives SET source='要赋的值' WHERE source='';

Function description: Delete comments from specified IP

Related statements :

以下为引用的内容：

DELETE FROM `dede_feedback` WHERE `dede_feedback`.`ip` = '000.000.000.000' 000.000.000.000 为垃圾评论发布者的IP

Function description: Clear the keyword field in the article

Related statements:

以下为引用的内容：

update dede_archives set keywords=''

Function description: Batch replace the publishing time, Storage time, update time

Related statements:

The following is the quoted content:

Step one. Add an article in the background.

Get a time, such as 2009-01-13 14:13:32, which can be seen through the management article.

The second step is to execute the SQL statement SELECT * FROM dede_archives order by id DESC limit 1

in the background. 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 it’s 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 be dynamic or static

Related statements:

The following is the quoted content:

UPDATE `dede_arctype` SET `isdefault` = '-1' 动态
UPDATE `dede_arctype` SET `isdefault` = '1' 静态

Function description: Batch replace SQL statements with article content

Related statements:

The following is the quoted content:

update `dede_addonarticle` set body=REPLACE(body,'论坛','社区') where body like "%论坛%"

The function of the above SQL statement is to find all articles with the phrase "forum" and replace the forum with "community"

Data call SQL statement organization:

Tag description: Common content statistics code

Related tags:

The following is the quoted content:

·Total articles: **

{dede:sql sql="select count(*) as c from dede_archives where channel=1"}·共有文章：[field:c /]篇{/dede:sql}

·Total albums: **

{dede:sql sql="select count(*) as c from dede_archives where channel=2"}·共有图集：[field:c /]个{/dede:sql}

·Total software: **

{dede:sql sql="select count(*) as c from dede_archives where channel=3"}·共有软件：[field:c /]个{/dede:sql}

·Total comments: **

{dede:sql sql="select count(*) as c from dede_feedback"}·共有评论：[field:c /]条{/dede:sql}

·Total members: **names

{dede:sql sql="select count(mid) as c from dede_member "}·共有会员：[field:c /]名{/dede:sql}

·Article views:** persons

{dede:sql sql="select sum(click) as c from dede_archives"}文章阅读：[field:c /]人次{/dede:sql}

·Today’s update: **articles

{dede:sql sql="SELECT count( * ) AS c FROM dede_archives WHERE pubdate > UNIX_TIMESTAMP( CURDATE( ) ) "}Today’s update: [field:c /]{/dede:sql}

Total messages:

{dede:sql sql="select count(*) as cc From dede_guestbook"}[field:cc/]{/dede:sql}条

Calling instructions: Call the Discuz forum attachment with pictures

Related tags:

The following is the quoted content:

{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="/static/imghwm/default1.png"  data-src="/bbs/p_w_uploads/[field:p_w_upload/]"  class="lazy"   alt="Commonly used SQL statements in DedeCMS" ></A></li>
<li><A href="/bbs/viewthread.php?tid=[field:tid /]">[field:subject function="cn_substr(&#39;@me&#39;,30)" /]</A></li>
{/dede:sql}

Calling instructions: Call UCHOME latest log

Related tags:

The following are quoted Content:

{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(&#39;@me&#39;,24)" /]</A></li>
{/dede:sql}

Call instructions: Member points ranking

Related tags:

The following is the quoted content:

{dede:sql sql="Select mid,userid,uname,scores From dede_member order by scores desc limit 0,10"}
<dd><span class="name"><a href=&#39;[field:global name=&#39;cfg_cmspath&#39;/]/member/?[field:userid/]/&#39;>[field:uname/]</a>
</span><span class="jifen">积分[field:scores/]</span></dd>
{/dede:sql}

Calling instructions: The company’s latest product calling method (picture title)

Related tags:

The following is the quoted content :

{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="/static/imghwm/default1.png"  data-src="[field:litpic/]"  class="lazy"   alt="[field:title/]"/></a></div><a href="/plus/view.php?aid=[field:aid/]" title="[field:title/]">[field:title/]</a></li>
{/dede:sql}

Calling instructions: Call the latest joined enterprise and the industry code of the enterprise to the home page

Related tags:

below Quoted content:

{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 ='企业' and m.matt = 1 and c.vocation=d.evalue and d.egroup='vocation' LIMIT 0 , 10"}
[field:company/]
[field:ename/]
{/dede:sql}

Call instructions: Recommended members (with user avatar)

Related tags:

The following is the quoted content:

{dede:sql sql="SELECT mid,mtype,userid,uname,matt,face
FROM dede_member
where matt = 1 and mtype='个人'
LIMIT 0 , 10"}

Avatar:

<img src="[field:face runphp=&#39;yes&#39;]
if(!@me)@me = &#39;http://bbs.dedecms.com/attachment/upload/87/11787.gif&#39;;
[/field:face]" />

Username:

<a href=&#39;[field:global name=&#39;cfg_cmspath&#39;/]/member/?[field:userid/]/&#39;>[field:uname/]</a>
{/dede:sql}
[field:face runphp=&#39;yes&#39;]
if(!@me)@me = &#39;http://bbs.dedecms.com/p_w_picpath/post/smile/default/14.gif&#39;;
[/field:face]

为用户头像图片

[img]http://bbs.dedecms.com/attachment/upload/87/11787.gif[/img]

是如果用户头像为空的话要显示的图像 这个大家自己改吧

调用说明：推荐企业

相关标签：

以下为引用的内容：

{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"}

头像：

<img src="[field:spacep_w_picpath runphp=&#39;yes&#39;]
if(!@me)@me = &#39;http://bbs.dedecms.com/p_w_picpath/post/smile/default/14.gif&#39;;
[/field:spacep_w_picpath]" />

用户名：

<a href=&#39;[field:global name=&#39;cfg_cmspath&#39;/]/member/?[field:userid/]/&#39;>[field:comname/]</a>
{/dede:sql}

调用说明：UCenter Home会员调用（带头像）

相关标签：

以下为引用的内容：

{dede:sql sql="SELECT * FROM `uchome_space` WHERE `avatar` =1 LIMIT 0 , 10"}
<a href="你安装UCenter Home的地址/space.php?uid=[field:uid/]">
<img  src=&#39;你安装UCenter的地址/avatar.php?uid=[field:uid/]&size=[field:small/]&#39; border=&#39;0&#39;    style="max-width:90%"Commonly used SQL statements in DedeCMS" >
<br />
[field:username/]</a>
{/dede:sql}

The above is the detailed content of Commonly used SQL statements in DedeCMS. For more information, please follow other related articles on the PHP Chinese website!