Shulou(Shulou.com)06/02 Report--

This article is to share with you the content of the cases of SQL sentences commonly used in Weaving Dream DedeCMS. The editor thought it was very practical, so I shared it with you as a reference. Let's follow the editor and have a look.

In the DedeCMS v5.3 system, we need to use SQL statements in many places, such as batch modification (replacement) content, data content calls, etc. There is a template tag {dede:sql/} specifically for calling data in the system template. We can refer to the template tag 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 dream weaving before using SQL statements and learning SQL statements. We can simply understand these contents through the database description of the help center. (recommended study: dream weaving cms)

The following is the referenced content:

Tag name: sql function description: used to use a SQL query from the template to get its return 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 example:

1. Call the content of an article published by a specific member

{dede:sql sql='Select * from dede_archives where mid=1'} [field:title/] {/ dede:sql}

Mid is the user's ID. You can refer to the introduction of the datasheet field about dede_archives in the secondary development.

Next, we collect and sort out some commonly used SQL statements to help you better use the DedeCMS system.

We divide SQL statements into two types, functional and content calling. Functional statements are mainly for routine operations on the database, such as {insert, update}, and data call operations (select). The two types of SQL statements are also easy to use and issue.

If it is functional, you only need to use it in [system]-[SQL Command running tool] at the system backend. If it is the data call type of the template tag, you only need to add the tag in the appropriate location of the template.

Functional SQL statement arrangement:

Feature description: add custom attributes

Related statements:

The following are references: insert into `dede_ Arcatt` (sortid,att,attname) values; alter table `dede_ archives` modify `comment `set ('clockwork modify' set) default NULL

Function description: assign values to authors and sources in batch

Related statements:

The following is the content of the reference: the value to be assigned by UPDATE dede_archives SET writer=' 'WHERE writer='';UPDATE dede_archives SET source=' to be assigned' WHERE source=''

Function description: delete the comments of the specified IP

Related statements:

The following is the quoted content: DELETE FROM `dede_ feedback` WHERE `dede_ feedback`.`ip` = '000.000.000.000' 000.000.000.000 is the IP of the spam comment publisher

Function description: clear the key fields in the article

Related statements:

The following is the referenced content: update dede_archives set keywords=''

Function description: batch replacement release time, storage time, update time

Related statements:

The following is the referenced content:

The first step. Add an article in the background.

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

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

In this way you can see all the field values of the newly added article you just added.

Observe the following data:

Pubdate:1231846313senddate:1231846313sortrank:1231846313

1231846313 of them are time data.

And then it was replaced.

UPDATE dede_archives SET sortrank = 1231846313 update dede_archives SET senddate = 1231846313 update dede_archives SET pubdate = 1231846313

Function description: batch modify the column for dynamic or static

Related statements:

The following is the referenced content:

UPDATE `dede_ arctype` SET `isdefault` ='- 1' dynamic UPDATE `dede_ arctype` SET `isdefault` ='1' static

Function description: replace SQL statements in batch with article content

Related statements:

The following is the referenced content:

Update `dede_ addonSecretle`set body=REPLACE (body,' Forum', 'Community') where body like "% Forum%"

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

Data call SQL statement collation:

Label description: commonly used content statistics code

Related labels:

The following is the referenced content:

Total articles: * *

{dede:sql sql= "select count (*) as c from dede_archives where channel=1"} Common articles: [field:c /] {/ dede:sql}

Common atlas: * *

{dede:sql sql= "select count (*) as c from dede_archives where channel=2"} common atlas: [field:c /] {/ dede:sql}

Total software: * *

{dede:sql sql= "select count (*) as c from dede_archives where channel=3"} shared software: [field:c /] {/ dede:sql}

Total comments: * *

{dede:sql sql= "select count (*) as c from dede_feedback"} A total of comments: [field:c /] {/ dede:sql}

Total 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}

Updated today: * *

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

Call instructions: call the posts with pictures attached to the Discuz forum

Related labels:

The following is the referenced content:

{dede:sql sql= "SELECT` cdb_p_w_ Secretads`.`aid`, `cdb_ threads`.`p _ upload`, `cdb_ threads`.`tid`, `cdb_ threads`.`fid`, `cdb_ threads`.`FROM `LEFT JOIN `cdb_ threads`ON `cdb_ threads`.`tid` WHERE `cdb_p_w_ uploads`.`readperm` ='0' AND `displayorder` > > ='0' AND `filetype` = 'pwresume picpath`pjpeg` GROUP BY tid LIMIT} 2"

[field:subject function= "cn_substr ('@ me',30)" /] {/ dede:sql}

Call description: call the latest log of UCHOME

Related labels:

The following is the referenced content:

{dede:sql sql= "Select subject,viewnum,blogid,uid From uchome_blog order by blogid desc limit 0 8"} [field:subject function= "cn_substr ('@ me',24)" /] {/ dede:sql}

Call instructions: member points ranking

Related labels:

The following is the referenced content:

{dede:sql sql= "Select mid,userid,uname,scores From dede_member order by scores desc limit 0 10"} [field:uname/] integral [field:scores/] {/ dede:sql}

Call description: enterprise's latest product call method (picture + title)

Related labels:

The following is the referenced content:

{dede:sql sql= "SELECT a. ID, a. Litpic. title FROM dede_addonshop p left join dede_archives an on a.id = p.aid order by a.id desc LIMIT 0,4"}

[field:title/] {/ dede:sql}

Call description: call the latest added enterprise and the industry code to the home page

Related labels:

The following is the referenced content:

{dede:sql sql= "SELECT m.midreparing m.mtyperem.useridreagem.mattrec.companyrecec.comfacered.enamered.enamestamd.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.egrouprecording vocation` LIMIT 0,10"} [field:company/] [field:ename/] {/ dede:sql}

Call instructions: recommend members (with user avatar)

Related labels:

The following is the referenced content:

{dede:sql sql= "SELECT mid,mtype,userid,uname,matt,faceFROM dede_memberwhere matt = 1 and mtype=' personal 'LIMIT 0,10"}

Avatar:

User name:

[field:uname/] {/ dede:sql} [field:face runphp='yes'] if (! @ me) @ me = 'http://bbs.dedecms.com/p_w_picpath/post/smile/default/14.gif';[/field:face]

Picture for the user's profile picture

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

It is the image to be displayed if the user's profile picture is empty. Please change it yourself.

Call description: recommend the enterprise

Related labels:

The following is the referenced content:

{dede:sql sql= "SELECT m.ID dede_member m left join dede_member_cominfo m.typerem.useridrect m.mattrect m.spaceptile wickedness picpathie c.idco.comnameFROM dede_member m left join dede_member_cominfo c on m.ID = c.idwhere m.type = 1 and m.matt = 1LIMIT 0,10"}

Avatar:

User name:

[field:comname/] {/ dede:sql}

Call description: UCenter Home member call (lead image)

Related labels:

The following is the referenced content:

{dede:sql sql= "SELECT * FROM `uchome_ space` WHERE `avatar` = 1 LIMIT 0,10"}

[field:username/] {/ dede:sql} Thank you for reading! This is the case of commonly used SQL sentences in Weaving Dream DedeCMS. I hope the above content can be helpful to you, so that you can learn more knowledge. If you think the article is good, you can share it and let more people see it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.