Discuz! X 数据查询表 - 常用查询

admin · 发表于 2020-7-7 09:09:28

马上注册，免费下载更多dz插件网资源。

您需要登录才可以下载或查看，没有账号？立即注册

×

调用最新主题

$new=DB::fetch_all("SELECT tid,subject FROM ".DB::table('forum_thread')." ORDER BY `dateline` desc LIMIT 0,1;");

复制代码

调用最新主题封面图

$img=DB::fetch_all("SELECT a.subject,a.tid,b.attachment FROM ".DB::table("forum_thread")." a LEFT JOIN ".DB::table("forum_threadimage")." b on b.tid=a.tid WHERE a.`attachment`=2 ORDER BY a.`dateline` DESC LIMIT 0,5");

复制代码

调用当前登录用户阅读权限

$read=DB::result(DB::query("SELECT b.readaccess FROM ".DB::table('common_member')." a left join ".DB::table('common_usergroup_field')." b on b.groupid=a.groupid WHERE a.`uid`= '$_G[uid]'"));

复制代码

调用常用统计数据

//获取VERSION、RELEASE$version = unserialize($_G['setting']['cloudaddons_newversion']);//获取站长用户名$name = DB::result_first("select username from ".DB::table("common_member")." where uid = 1");//获取建站时间$time = DB::result_first("select regdate from ".DB::table("common_member")." where uid = 1");//获取模板个数$templates = DB::result_first("select count(styleid) from ".DB::table("common_style")."");//获取插件个数$plugins = DB::result_first("select count(pluginid) from ".DB::table("common_plugin")."");//获取会员人数$users = DB::result_first("select count(uid) from ".DB::table("common_member")."");//获取临时会员人数$guests = DB::result_first("select count(conopenid) from ".DB::table("common_connect_guest")."");//获取会员总人数$all_users = $users + $guests;//获取管理团队人数$admins = DB::result_first("select count(uid) from ".DB::table("common_member")." where groupid in (1,2)");//获取最新注册会员$newuser = DB::result_first("select username from ".DB::table("common_member")." order BY regdate desc");//获取发帖会员人数$post_posts = DB::result_first("select count(uid) from ".DB::table("common_member_count")." where posts > 0 ");//获取发表主题会员人数$post_threads = DB::result_first("select count(uid) from ".DB::table("common_member_count")." where threads > 0 ");//获取版块数$forums = DB::result_first("select count(fid) from ".DB::table("forum_forum")." where type = 'forum' and status in (0,1)");//获取总帖数$posts = DB::result_first("select count(pid) from ".DB::table("forum_post")."");//获取主题数$threads = DB::result_first("select count(tid) from ".DB::table("forum_thread")."");//获取精华主题数$digest = DB::result_first("select count(digest) from ".DB::table("forum_thread")." where digest > 0");//获取高亮主题数$highlight = DB::result_first("select count(highlight) from ".DB::table("forum_thread")." where highlight > 0");//获取置顶主题数$displayorder = DB::result_first("select count(displayorder) from ".DB::table("forum_thread")." where displayorder > 0");//获取附件主题数$attachment = DB::result_first("select count(tid) from ".DB::table("forum_thread")." where attachment > 0");//获取待审核主题数$moderate_threads = DB::result_first("select count(id) from ".DB::table("forum_thread_moderate")." where status = 0");//获取已忽略主题数$moderate_ignore_threads = DB::result_first("select count(id) from ".DB::table("forum_thread_moderate")." where status = 1");//获取待审核回复数$moderate_posts = DB::result_first("select count(id) from ".DB::table("forum_post_moderate")." where status = 0");//获取已忽略回复数$moderate_ignore_posts = DB::result_first("select count(id) from ".DB::table("forum_post_moderate")." where status = 1");//获取主题回收站数$recyclebin_threads = DB::result_first("select count(tid) from ".DB::table("forum_thread")." where displayorder = -1");//获取回复回收站数$recyclebin_posts = DB::result_first("select count(tid) from ".DB::table("forum_post")." where invisible = -5");//获取访问总数$views = DB::result_first("select sum(views) from ".DB::table("forum_thread")."");//获取回复总数$replies = DB::result_first("select sum(replies) from ".DB::table("forum_thread")."");//获取支持总数$recommend_add = DB::result_first("select sum(recommend_add) from ".DB::table("forum_thread")."");//获取反对总数$recommend_sub = DB::result_first("select sum(recommend_sub) from ".DB::table("forum_thread")."");//获取收藏总数$favtimes = DB::result_first("select sum(favtimes) from ".DB::table("forum_thread")."");//获取分享总数$sharetimes = DB::result_first("select sum(sharetimes) from ".DB::table("forum_thread")."");//获取会员:男性$users_man = DB::result_first("select count(uid) from ".DB::table("common_member_profile")." where gender = 1 ");//获取会员:女性$users_woman = DB::result_first("select count(uid) from ".DB::table("common_member_profile")." where gender = 2 ");//获取会员:80后$users_80 = DB::result_first("select count(uid) from ".DB::table("common_member_profile")." where birthyear > 1980 and birthyear < 1989 ");

复制代码

©DZ插件网所发布的一切资源仅限用于学习和研究目的；不得将上述内容用于商业或者非法用途，否则，一切后果请用户自负。
本站内容为站长个人技术研究记录或网络，不提供用户交互功能，所有内容版权归原作者所有。您必须在下载后的24个小时之内，从您的电脑中彻底删除上述内容。如果您喜欢该程序，请支持正版软件，得到更好的正版服务。
您在本站任何的赞助购买、下载、查阅、回复等行为等均表示接受并同意签订《DZ插件网免责声明协议》。
如有侵权请邮件与我们联系处理: discuzaddons@vip.qq.com 并出示相关证明以便删除。敬请谅解！

Discuz智能体 · 发表于 4 天前

在Discuz! X中，数据查询是非常常见的操作，尤其是在开发插件或进行二次开发时。你提供的代码片段展示了如何通过SQL查询来获取Discuz! X中的各种数据。以下是对这些查询的详细解释和一些优化建议：

### 1. 调用最新主题

$new = DB::fetch_all("SELECT tid, subject FROM ".DB::table('forum_thread')." ORDER BY `dateline` DESC LIMIT 0,1;");

复制代码

- **解释**: 这个查询从`forum_thread`表中获取最新的主题（`tid`和`subject`），并按`dateline`字段降序排列，限制结果为1条。
- **优化建议**: 如果你只需要一条记录，可以使用`DB::fetch_first`代替`DB::fetch_all`，这样可以减少内存占用。

### 2. 调用最新主题封面图

$img = DB::fetch_all("SELECT a.subject, a.tid, b.attachment FROM ".DB::table("forum_thread")." a LEFT JOIN ".DB::table("forum_threadimage")." b ON b.tid = a.tid WHERE a.`attachment` = 2 ORDER BY a.`dateline` DESC LIMIT 0,5");

复制代码

- **解释**: 这个查询从`forum_thread`表和`forum_threadimage`表中联合查询，获取带有封面图的最新主题，并按`dateline`降序排列，限制结果为5条。
- **优化建议**: 如果封面图不是必须的，可以考虑去掉`LEFT JOIN`，或者确保`forum_threadimage`表中的数据量不会太大，以免影响查询性能。

### 3. 调用当前登录用户阅读权限

$read = DB::result(DB::query("SELECT b.readaccess FROM ".DB::table('common_member')." a LEFT JOIN ".DB::table('common_usergroup_field')." b ON b.groupid = a.groupid WHERE a.`uid` = '$_G'"));

复制代码

- **解释**: 这个查询从`common_member`表和`common_usergroup_field`表中联合查询，获取当前登录用户的阅读权限。
- **优化建议**: 确保`$_G`变量已经正确初始化，并且包含当前用户的UID。如果`$_G`变量未定义或为空，可能会导致SQL错误。

### 4. 调用常用统计数据

// 获取VERSION、RELEASE
$version = unserialize($_G);
// 获取站长用户名
$name = DB::result_first("SELECT username FROM ".DB::table("common_member")." WHERE uid = 1");
// 获取建站时间
$time = DB::result_first("SELECT regdate FROM ".DB::table("common_member")." WHERE uid = 1");
// 获取模板个数
$templates = DB::result_first("SELECT COUNT(styleid) FROM ".DB::table("common_style")."");
// 获取插件个数
$plugins = DB::result_first("SELECT COUNT(pluginid) FROM ".DB::table("common_plugin")."");
// 获取会员人数
$users = DB::result_first("SELECT COUNT(uid) FROM ".DB::table("common_member")."");
// 获取临时会员人数
$guests = DB::result_first("SELECT COUNT(conopenid) FROM ".DB::table("common_connect_guest")."");
// 获取会员总人数
$all_users = $users + $guests;
// 获取管理团队人数
$admins = DB::result_first("SELECT COUNT(uid) FROM ".DB::table("common_member")." WHERE groupid IN (1,2)");
// 获取最新注册会员
$newuser = DB::result_first("SELECT username FROM ".DB::table("common_member")." ORDER BY regdate DESC");
// 获取发帖会员人数
$post_posts = DB::result_first("SELECT COUNT(uid) FROM ".DB::table("common_member_count")." WHERE posts > 0 ");
// 获取发表主题会员人数
$post_threads = DB::result_first("SELECT COUNT(uid) FROM ".DB::table("common_member_count")." WHERE threads > 0 ");
// 获取版块数
$forums = DB::result_first("SELECT COUNT(fid) FROM ".DB::table("forum_forum")." WHERE type = 'forum' AND status IN (0,1)");
// 获取总帖数
$posts = DB::result_first("SELECT COUNT(pid) FROM ".DB::table("forum_post")."");
// 获取主题数
$threads = DB::result_first("SELECT COUNT(tid) FROM ".DB::table("forum_thread")."");
// 获取精华主题数
$digest = DB::result_first("SELECT COUNT(digest) FROM ".DB::table("forum_thread")." WHERE digest > 0");
// 获取高亮主题数
$highlight = DB::result_first("SELECT COUNT(highlight) FROM ".DB::table("forum_thread")." WHERE highlight > 0");
// 获取置顶主题数
$displayorder = DB::result_first("SELECT COUNT(displayorder) FROM ".DB::table("forum_thread")." WHERE displayorder > 0");
// 获取附件主题数
$attachment = DB::result_first("SELECT COUNT(tid) FROM ".DB::table("forum_thread")." WHERE attachment > 0");
// 获取待审核主题数
$moderate_threads = DB::result_first("SELECT COUNT(id) FROM ".DB::table("forum_thread_moderate")." WHERE status = 0");
// 获取已忽略主题数
$moderate_ignore_threads = DB::result_first("SELECT COUNT(id) FROM ".DB::table("forum_thread_moderate")." WHERE status = 1");
// 获取待审核回复数
$moderate_posts = DB::result_first("SELECT COUNT(id) FROM ".DB::table("forum_post_moderate")." WHERE status = 0");
// 获取已忽略回复数
$moderate_ignore_posts = DB::result_first("SELECT COUNT(id) FROM ".DB::table("forum_post_moderate")." WHERE status = 1");
// 获取主题回收站数
$recyclebin_threads = DB::result_first("SELECT COUNT(tid) FROM ".DB::table("forum_thread")." WHERE displayorder = -1");
// 获取回复回收站数
$recyclebin_posts = DB::result_first("SELECT COUNT(tid) FROM ".DB::table("forum_post")." WHERE invisible = -5");
// 获取访问总数
$views = DB::result_first("SELECT SUM(views) FROM ".DB::table("forum_thread")."");
// 获取回复总数
$replies = DB::result_first("SELECT SUM(replies) FROM ".DB::table("forum_thread")."");
// 获取支持总数
$recommend_add = DB::result_first("SELECT SUM(recommend_add) FROM ".DB::table("forum_thread")."");
// 获取反对总数
$recommend_sub = DB::result_first("SELECT SUM(recommend_sub) FROM ".DB::table("forum_thread")."");
// 获取收藏总数
$favtimes = DB::result_first("SELECT SUM(favtimes) FROM ".DB::table("forum_thread")."");
// 获取分享总数
$sharetimes = DB::result_first("SELECT SUM(sharetimes) FROM ".DB::table("forum_thread")."");
// 获取会员:男性
$users_man = DB::result_first("SELECT COUNT(uid) FROM ".DB::table("common_member_profile")." WHERE gender = 1 ");
// 获取会员:女性
$users_woman = DB::result_first("SELECT COUNT(uid) FROM ".DB::table("common_member_profile")." WHERE gender = 2 ");
// 获取会员:80后
$users_80 = DB::result_first("SELECT COUNT(uid) FROM ".DB::table("common_member_profile")." WHERE birthyear > 1980 AND birthyear < 1989 ");

复制代码

- **解释**: 这些查询用于获取Discuz! X中的各种统计数据，如会员人数、主题数、回复数等。
- **优化建议**: 如果这些查询在同一个页面中频繁使用，可以考虑将这些查询结果缓存起来，以减少数据库的查询压力。可以使用Discuz!自带的缓存机制，或者使用外部缓存系统如Redis。

### 总结
- **性能优化**: 对于频繁使用的查询，建议使用缓存机制来减少数据库的负载。
- **代码优化**: 尽量使用`DB::fetch_first`代替`DB::fetch_all`，以减少内存占用。
- **安全性**: 确保所有用户输入的数据都经过适当的过滤和转义，以防止SQL注入攻击。

如果你有更多关于Discuz! X开发的问题，欢迎继续提问！
-- 本回答由人工智能 AI智能体生成，内容仅供参考，请仔细甄别。