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

The editor will share with you how to use DEDECMS SELECT sentences. I hope you will gain a lot after reading this article. Let's discuss it together.

How to use the DEDECMS SELECT statement?

SQL tutorial: SELECT statement DEDECMS instance Application

Recommended study: dream weaving cms

In the DedeCMS system, we need to use SQL statements in many places, such as batch modification (replacement) content, data content calls, etc., in the system template there is a special template tag {dede:sql/} for calling data. 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.

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 and update}, and data call operations (select). The two types of SQL statements are also very easy to use. If functional, you only need to use it in the system backend [system]-[SQL Command Runner]. If it is the data call type of the template label. You just need to add a tag in the appropriate location of the template.

Functional SQL statement arrangement:

two。 Feature description: add custom attributes

Related statements:

Insert into `dede_ Arcatt` (sortid,att,attname) values (9 dede_ archives` modify `comments); default NULL ('clockwork modify `comments') default NULL

3. Function description: assign values to authors and sources in batch

Related statements:

The value to be assigned by UPDATE dede_archives SET writer=' 'the value to be assigned by WHERE writer=''; UPDATE dede_archives SET source='' WHERE source=''

4. Function description: delete the comments of 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 spamming comment publishers

5. Function description: clear the key fields in the article

Related statements:

000.000.000.000 is the IP of spamming comment publishers

5. Function description: clear the key fields 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 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:1231846313

Senddate:1231846313

Sortrank: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

7. Function description: batch modify the column for dynamic or static

Related statements:

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

8. Function description: replace SQL statements in batch with article content

Related statements:

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:

9. Label description: commonly used content statistics code

Related labels:

Common 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} Common Software: * * {dede:sql sql= " Select count (*) as c from dede_archives where channel=3 "} Common Software: [field:c /] {/ dede:sql} Common comments: * * {dede:sql sql=" select count (*) as c from dede_feedback "} Common comments: [field:c /] {/ dede:sql} Common member: * * name {dede:sql sql=" select count (mid) as c from dede_member "} Common member: [ Field:c /] name {/ dede:sql} article reading: * * person-time {dede:sql sql= "select sum (click) as c from dede_archives"} article reading: [field:c /] person-time {/ dede:sql} Today update: * * article {dede:sql sql= "SELECT count (*) AS c FROM dede_archives WHERE pubdate > UNIX_TIMESTAMP (CURDATE ())"} Today update: [field:c /] Article {/ dede:sql} Total comments: {dede:sql sql= "select count (*) as cc From dede_guestbook"} [field:cc/] {/ dede:sql}

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

Related labels:

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

11. Call description: call the latest log of UCHOME

Related labels:

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

twelve。 Call instructions: member points ranking

Related labels:

{dede:sql sql= "Select mid,userid,uname,scores From dede_member order by scores desc limit 0jue 10"} [field:uname/] integral [field:scores/] {/ dede:sql} after reading this article, I believe you have a certain understanding of the use of DEDECMS SELECT sentences, want to know more about it, welcome to follow the industry information channel, thank you for reading!

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.