Home > CMS Tutorial > DEDECMS > body text

Commonly used SQL statements in DedeCMS

(*-*)浩
Release: 2019-11-23 14:12:31
Original
2082 people have browsed it

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}
Copy after login

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,&#39;d&#39;,&#39;评论&#39;);
alter table `dede_archives` modify `flag` set (&#39;c&#39;,&#39;h&#39;,&#39;p&#39;,&#39;f&#39;,&#39;s&#39;,&#39;j&#39;,&#39;a&#39;,&#39;b&#39;,&#39;d&#39;) default NULL;
Copy after login

Function description: Assign values ​​to authors and sources in batches

Related statements:

以下为引用的内容:

UPDATE dede_archives SET writer=&#39;要赋的值&#39; WHERE writer=&#39;&#39;;
UPDATE dede_archives SET source=&#39;要赋的值&#39; WHERE source=&#39;&#39;;
Copy after login

Function description: Delete comments from specified IP

Related statements :

以下为引用的内容:

DELETE FROM `dede_feedback` WHERE `dede_feedback`.`ip` = &#39;000.000.000.000&#39; 000.000.000.000 为垃圾评论发布者的IP
Copy after login

Function description: Clear the keyword field in the article

Related statements:

以下为引用的内容:

update dede_archives set keywords=&#39;&#39;
Copy after login

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
Copy after login

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;
Copy after login

Function description: Batch modify columns to be dynamic or static

Related statements:

The following is the quoted content:

UPDATE `dede_arctype` SET `isdefault` = &#39;-1&#39; 动态
UPDATE `dede_arctype` SET `isdefault` = &#39;1&#39; 静态
Copy after login

Function description: Batch replace SQL statements with article content

Related statements:

The following is the quoted content:

update `dede_addonarticle` set body=REPLACE(body,&#39;论坛&#39;,&#39;社区&#39;) where body like "%论坛%"
Copy after login

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}
Copy after login

·Total albums: **

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

·Total software: **

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

·Total comments: **

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

·Total members: **names

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

·Article views:** persons

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

·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}条
Copy after login

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`=&#39;0&#39; AND `displayorder`>=&#39;0&#39; AND `filetype`=&#39;p_w_picpath/pjpeg&#39; 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(&#39;@me&#39;,30)" /]</A></li>
{/dede:sql}
Copy after login

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}
Copy after login

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}
Copy after login

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="[field:litpic/]" alt="[field:title/]"/></a></div><a href="/plus/view.php?aid=[field:aid/]" title="[field:title/]">[field:title/]</a></li>
{/dede:sql}
Copy after login

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

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=&#39;个人&#39;
LIMIT 0 , 10"}
Copy after login

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]" />
Copy after login

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]
Copy after login

为用户头像图片

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

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

调用说明:推荐企业

相关标签:

以下为引用的内容:

{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"}
Copy after login

头像:

<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]" />
Copy after login

用户名:

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

调用说明: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; width=&#39;99&#39; height=&#39;88&#39; >
<br />
[field:username/]</a>
{/dede:sql}
Copy after login

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!

Related labels:
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template
About us Disclaimer Sitemap
php.cn:Public welfare online PHP training,Help PHP learners grow quickly!