中国典当联盟网站是用现在比较流行的B2B网站管理系统destoon做的一个网站,在做中国典当联盟网改版的时候遇到一个问题是,当我们点击导航栏目中的新闻资讯列表的时候,要显示的是左侧栏目中所有分类总共的数据,这时我们就需要用到sql语句中的多表联合查询.(第一次使用destoon做网站,还是由于php基础不过关,导致遇到很多问题不能及时的处理好.)

分别查找对应的表,把相关的数据读取出来,在做连接,其中sql语句如下 (每个表都得来一个别名,否则sql语句会报错的~....):

select count(title) as num from (
            select * from (SELECT
            title,
            introduce,
            linkurl,
            addtime
            FROM
            cnpawn_article_24
            WHERE
            STATUS = 3) as a
        union
            select * from (SELECT
            title,
            introduce,
            linkurl,
            addtime
            FROM
            cnpawn_article_21
            WHERE
            STATUS = 3) as b
        union
            select * from (SELECT
            title,
            introduce,
            linkurl,
            addtime
            FROM
            cnpawn_article_23
            WHERE
            STATUS = 3) as c
        union 
        select * from (select 
            title,
            introduce,
            linkurl,
            addtime
            FROM
            cnpawn_article_26
            where 
            STATUS = 3) as d
        union 
        select * from (select 
            title,
            introduce,
            linkurl,
            addtime
            FROM
            cnpawn_article_29
            where 
            STATUS = 3) as e
            UNION
        select * from (SELECT
        title,content,linkurl,addtime from
        cnpawn_announce) as f

) as g;

然后在新闻列表的模板页面中用php来读取相关的字段显示在页面上面:

<!-- start  新闻列表-->
<div class="dynamic borderbottom">
    <div class="htitle"><i><img src="{DT_SKIN}images/sicons.png"></i><a href="#">首页</a> > <a href="">金融超市</a> > <a href="#">陕西西部信德典当有限责任公司</a> > <span>公司动态</span></div>
    <div class="bd">
        <?php
            global $MOD, $TYPE, $pages, $page, $pagesize, $offset;
             $r = $db->query("select count(title) as num from (
                        select * from (SELECT
                        title,
                        introduce,
                        linkurl,
                        addtime
                        FROM
                        ".$DT_PRE."article_24
                        WHERE
                        STATUS = 3) as a
                        union
                            select * from (SELECT
                            title,
                            introduce,
                            linkurl,
                            addtime
                            FROM
                            ".$DT_PRE."article_21
                            WHERE
                            STATUS = 3) as b
                        union
                            select * from (SELECT
                            title,
                            introduce,
                            linkurl,
                            addtime
                            FROM
                            ".$DT_PRE."article_23
                            WHERE
                            STATUS = 3) as c
                        union 
                        select * from (select 
                            title,
                            introduce,
                            linkurl,
                            addtime
                            FROM
                            ".$DT_PRE."article_26
                            where 
                            STATUS = 3) as d
                        union 
                        select * from (select 
                            title,
                            introduce,
                            linkurl,
                            addtime
                            FROM
                            ".$DT_PRE."article_29
                            where 
                            STATUS = 3) as e
                            UNION
                        select * from (SELECT
                        title,content,linkurl,addtime from
                        ".$DT_PRE."announce) as f

                ) as g;
                ");

                //$pages = pages($r['num'], $page, $pagesize);
                //读取并显示查询的数据总数,做分页使用
                $total = mysql_fetch_array($r);
                $pages = pages($total[num], $page, $pagesize);
                //$k = mysql_num_rows($r);
                //echo $r;
                //exit;
                ?>
        {php $t=$db->query("select title,introduce,linkurl,addtime from ".$DT_PRE."article_24 where status=3 union all (select title,introduce,linkurl,addtime from ".$DT_PRE."article_21 where status=3) union all (select title,introduce,linkurl,addtime from ".$DT_PRE."article_23 where status=3) union all (select title,introduce,linkurl,addtime from ".$DT_PRE."article_26 where status=3) union all (select title,introduce,linkurl,addtime from ".$DT_PRE."article_29 where status=3) union all (select title,content,linkurl,addtime from ".$DT_PRE."announce) limit $offset,7");}
        <ul class="list">
            <?php
                //测试打印查询的条数echo mysql_num_rows($t);
                while($row = mysql_fetch_array($t)){
                    echo '<li class="list">
                            <div class="tit"><a href="'.$row[linkurl].'">'.dsubstr($row[title],50,'...').'</a><span>'.date('Y-m-d H:m:s',$row[addtime]).'</span></div>
                            <p>'.dsubstr(strip_tags($row[introduce]),280,'....').'<a href="'.$row[linkurl].'">阅读全文</a>'.'</p>
                        </li>';
                    }
                ?>
        </ul>
        <div class="pages listp">
            {$pages}
        </div>
    </div>
</div>

其中destoon框架修改分页样式的文件是在网站根目录文件夹下面,找到 api/pages.default.php 文件修改对应的样式文件即可.

最终页面显示效果如下图所示: