How to use the DEDECMS SELECT statement

DHow to use the EDECMS SELECT statement?

SQL tutorial: SELECT statement DEDECMS instance application

Recommended learning:梦Weavercms

In the DedeCMS system, we need it in many places SQL statements are used, such as batch modification (replacement) content, data content calling, etc. There is a template tag {dede:sql/} specifically used to call data in the system template. We can refer to the template tag description in the help center to learn this. How to use tags.

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.

The following is the referenced content:

Tag name: sql Function description: Used to obtain the returned content from the template using a SQL query Applicable scope: Global use Basic syntax: {dede:sql sql=""}Underlying template{/dede:sql} Parameter description: sql="" Complete SQL query statement underlying template fields: All fields found in the SQL statement can be called with [field:field name/]

Application examples:

1. Call the article content published by a specific member

{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 better use the 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 distribute the 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 tag, it only needs to be used in the system background [System]-[SQL Command Running Tool]. Just add tags to the corresponding positions in the template.

Functional SQL statement organization:

2. 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;

3. Function description: Batch is Author and source assignment

Related statements:

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

4. Function description: Delete comments from the specified IP

Related statements:

DELETE FROM `dede_feedback` WHERE `dede_feedback`.`ip` = '000.000.000.000'

000.000.000.000 is the IP of the spam comment publisher

5. Function description: Clear the keyword field in the article

Related statements:

000.000.000.000 is the IP of the spam comment publisher

5. Function description: Clear the keyword field in the article

Related statements:

update dede_archives set keywords=''

6. Function description: Batch replacement release time, storage time, update time

Related statements:

The first step. 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.

So 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;

7. Function description: Batch modify columns to be dynamic or static

Related statements:

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

8. Function description: Batch replace SQL statements with article content

Related statements:

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 calling SQL statement organization:

9. Tag description: Common content statistics code

Related tags:

共有文章：** 篇  
{dede:sql sql="select count(*) as c from dede_archives where channel=1"}·共有文章：[field:c /]篇{/dede:sql}  
·共有图集：** 个  
{dede:sql sql="select count(*) as c from dede_archives where channel=2"}·共有图集：[field:c /]个{/dede:sql}  
·共有软件：** 个  
{dede:sql sql="select count(*) as c from dede_archives where channel=3"}·共有软件：[field:c /]个{/dede:sql}  
·共有评论：**条  
{dede:sql sql="select count(*) as c from dede_feedback"}·共有评论：[field:c /]条{/dede:sql}  
·共有会员：**名  
{dede:sql sql="select count(mid) as c from dede_member "}·共有会员：[field:c /]名{/dede:sql}  
·文章阅读：** 人次  
{dede:sql sql="select sum(click) as c from dede_archives"}文章阅读：[field:c /]人次{/dede:sql}  
·今日更新：**篇  
{dede:sql sql="SELECT count( * ) AS c FROM dede_archives WHERE pubdate > UNIX_TIMESTAMP( CURDATE( ) ) "}今日更新：[field:c /]篇{/dede:sql} 
总共留言：{dede:sql sql="select count(*) as cc From dede_guestbook"}[field:cc/]{/dede:sql}条

10. Calling instructions: Call the Discuz forum attachment with pictures Post

Related tags:

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

11. Call instructions: Call UCHOME latest log

Related tags:

{dede:sql sql="Select subject,viewnum,blogid,uid From uchome_blog order by blogid desc limit 0,8"}  
<li><A href="?uid=[field:uid/]&do=blog&id=[field:blogid/]">[field:subject function="cn_substr(&#39;@me&#39;,24)" /]</A></li>  
{/dede:sql}

12. Call instructions: Member points Ranking

Related tags:

{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/]/member/?[field:userid/]/&#39;>[field:uname/]</a>  
</span><span class="jifen">积分[field:scores/]</span></dd>  
{/dede:sql}

The above is the detailed content of How to use the DEDECMS SELECT statement. For more information, please follow other related articles on the PHP Chinese website!