MySQL同结构多表联合查询
2013-09-13 金城 4032
在一些模块化的CMS中,经常遇到要用基本模块复制出更多的结构相同的扩展模块。
而在网站首页面 或 系统后台统计页面需要将这些结构相同的模块并在一起,然后分析处理。
但他们处在结构相同但名称不同的各种表中,就需要联合查询他们,方法有二,下面解析。
方法一,使用UNION
拿destoon系统举例子:
系统中存在一个基本模块 - 文章模块(资讯),对应表名为 destoon_article_21
我们又以文章模块为基础添加了类同的更多模块,如下对应
- 最新资讯 destoon_article_21 (文章模块的基础表)
- 最新技术 destoon_article_23
- 管理常识 destoon_article_24
- 领导艺术 destoon_article_25
- 相关法规 destoon_article_26
现在要综合查询这些模块的推荐信息用到网站首页面的推荐区域:
在 /index.php 中模板输出语句前 添加 如下补丁代码
#补丁 联合查询推荐信息
$Recommon=$db->query("select `savedir`,itemid,`title`,addtime,linkurl from (
(select 'news' as `savedir`,itemid,title,addtime,linkurl from dt_article_21 where `status`=3 and `level`=1 order by addtime desc limit 10)
union (select 'jishu' as `savedir`,itemid,title,addtime,linkurl from dt_article_23 where `status`=3 and `level`=1 order by addtime desc limit 10)
union (select 'changshi' as `savedir`,itemid,title,addtime,linkurl from dt_article_24 where `status`=3 and `level`=1 order by addtime desc limit 10)
union (select 'yishu' as `savedir`,itemid,title,addtime,linkurl from dt_article_25 where `status`=3 and `level`=1 order by addtime desc limit 10)
union (select 'fagui' as `savedir`,itemid,title,addtime,linkurl from dt_article_26 where `status`=3 and `level`=1 order by addtime desc limit 10)
) as t
order by addtime desc
limit 10");
if($Recommon){
$tag_recommon_list='';
while($RecommonRow=mysql_fetch_array($Recommon,MYSQL_ASSOC)){
$tag_recommon_list .="<li><a href=\"/$RecommonRow[dir]/$RecommonRow[linkurl]\">$RecommonRow[title]</a></li>";
}
if($tag_recommon_list!='')$tag_recommon_list="<ul>$tag_recommon_list</ul>";
}
mysql_free_result($Recommon);
#
$Recommon=$db->query("select `savedir`,itemid,`title`,addtime,linkurl from (
(select 'news' as `savedir`,itemid,title,addtime,linkurl from dt_article_21 where `status`=3 and `level`=1 order by addtime desc limit 10)
union (select 'jishu' as `savedir`,itemid,title,addtime,linkurl from dt_article_23 where `status`=3 and `level`=1 order by addtime desc limit 10)
union (select 'changshi' as `savedir`,itemid,title,addtime,linkurl from dt_article_24 where `status`=3 and `level`=1 order by addtime desc limit 10)
union (select 'yishu' as `savedir`,itemid,title,addtime,linkurl from dt_article_25 where `status`=3 and `level`=1 order by addtime desc limit 10)
union (select 'fagui' as `savedir`,itemid,title,addtime,linkurl from dt_article_26 where `status`=3 and `level`=1 order by addtime desc limit 10)
) as t
order by addtime desc
limit 10");
if($Recommon){
$tag_recommon_list='';
while($RecommonRow=mysql_fetch_array($Recommon,MYSQL_ASSOC)){
$tag_recommon_list .="<li><a href=\"/$RecommonRow[dir]/$RecommonRow[linkurl]\">$RecommonRow[title]</a></li>";
}
if($tag_recommon_list!='')$tag_recommon_list="<ul>$tag_recommon_list</ul>";
}
mysql_free_result($Recommon);
#
在你的首页模版页中推荐区域放上{$tag_recommon_list}即可。
第二种方法,就是插入符合条件的数据到新的汇总表,这种方法需要创建新表,可以用临时表。也可以做个计划任务自动更新汇总表用以提升性能。
- 上一篇:php抓取页面的几种方式
- 下一篇:PHP转义文章内容字符串中的网址