phpcms中实现不同结构数据库转换

需求是把之前网站的一个mssql2000的数据库转换成mysql数据库，并且新的网站采用不同的程序，因此还要把数据库中的数据根据字段的不同，和字段数据规则的不同进行转换。

1.不同数据库类型直接的数据库转换

navicat是个很好的数据库管理软件，可以用它进行不同类型数据库直接的转换，以下链接详细说明如何用navicat将mssql2000数据库转换成mysql数据库。

2.不同结构数据库之间的数据转换

不同数据库结构之间的数据转换存在几个问题，①字段不同，甚至无法一一对应 ②字段格式不同，要转换成目标数据库字段的设计格式 ③sql语句结合php程序转换

这是把之前一个downplus下载系统的数据库转换成phpcms的数据库。

直接上代码，有这方面需求的研究代码吧。

站点1 软件站

①导入所有软件到软件表v95_soft

insert into v95_soft(id,catid,title,soft,soft_name,pinyin,thumb,keywords,size,start,inputtime,updatetime,auth,property)

select softid,softclassid,seotitle,appname,softname,softphoneticism,IcoImage,softkeywords,softsize,softscore,

unix_timestamp(SoftInsertDate),unix_timestamp(softcreatedate),softlicence,softproperty 

from dp_softlist;

②写sql设status为99

update v95_soft set status=99;

③到后台更新全站url。然后更新title为空的，软件名称

update v95_soft set title=soft_name where title='';

④导入到v95_soft_date

insert into v95_soft_data(id,content,language,website,icon,softos) 

select softid,softintro,softlanguage,softauthorurl,IcoImage,softos 

from dp_softlist;

⑤执行php转换程序，导入下载地址

http://www.phprm.com/admin.php?m=admin&c=index&a=down

<?php
public function down() {
    set_time_limit(0);
    $sql = "select id from v95_soft_data ";
    $result = $this->db->query($sql);
    while ($r = mysql_fetch_assoc($result)) {
        //$softid = 11;
        $softid = $r[&#39;id&#39;];
        $sql = "select fileurlname,fileurl,fileftpid from dp_softfiles where softid = $softid ";
        $downfile = $this->db->query($sql);
        $downfiles = array();
        while ($r = mysql_fetch_assoc($downfile)) {
            $isbigfile = 0;
            if (!$r[&#39;fileurlname&#39;]) {
                $sql = "select soft_name from v95_soft where id = $softid ";
                $result2 = $this->db->query($sql);
                while ($r2 = mysql_fetch_assoc($result2)) {
                    $soft_name = $r2[&#39;soft_name&#39;];
                }
                $r[&#39;fileurlname&#39;] = $soft_name;
            }
            if (12 == $r[&#39;fileftpid&#39;]) {
                $isbigfile = 1;
            }
            $downfiles[] = array(
                &#39;fileurl&#39; => $r[&#39;fileurl&#39;],
                &#39;filename&#39; => $r[&#39;fileurlname&#39;],
                &#39;isbigfile&#39; => $isbigfile
            );
        }
        $downfiles = array2string($downfiles);
        //var_dump($downfiles);exit;
        $sql = "update v95_soft_data set downfiles = &#39;$downfiles&#39; where id = $softid ";
        $this->db->query($sql);
    }
    echo &#39;OK&#39;;
}
?>

⑥软件单位和大小转换

update v95_soft set size=size/1000,unit='MB' where size>1000 and size

update v95_soft set size=size/1000000,unit='GB' where size>1000000;

⑦导入标签，到v95_keyword和v95_keyword_data

insert into v95_keyword(id,keyword) select tagid,tagname from dp_tag;

update v95_keyword set siteid=1;

⑧执行php转换程序，匹配标签到关键字

http:///admin.php?m=admin&c=index&a=transe

php代码：

<?php
public function transe() {
    set_time_limit(0);
    $sql = "select tagid,softidlist from dp_tag";
    $result = $this->db->query($sql);
    while ($r = mysql_fetch_assoc($result)) {
        //var_dump($r);exit;
        $tags = $r[&#39;softidlist&#39;];
        $tags = explode(",", $tags);
        $tags = array_filter($tags);
        //var_dump($tags);exit;
        $tagid = $r[&#39;tagid&#39;];
        //echo $tagid;exit;
        foreach ($tags as $tag) {
            $sql = "insert into v95_keyword_data(tagid,siteid,contentid) values(&#39;$tagid&#39;,&#39;1&#39;,&#39;$tag&#39;)";
            $this->db->query($sql);
        }
    }
    echo &#39;OK&#39;;
}
?>

调整格式

update v95_keyword_data set contentid=CONCAT(contentid,'-12');

⑩点击率

insert into v95_hits(hitsid,catid) select id,catid from v95_soft;

//可以不用转，太慢了

update v95_hits as a left join dp_softlist as b on a.hitsid=b.softid 

set a.weekviews=b.softweekhits,a.monthviews=b.softmonthhits,a.dayviews=b.softdayhits,a.views=b.softallhits;

 

update v95_hits set hitsid=concat('c-12-',hitsid);

十一 相关文章

http:///admin.php?m=admin&c=index&a=related （最后执行，很慢）

php代码：

<?php
public function related() {
    set_time_limit(0);
    $sql = "select id,keywords from v95_soft";
    $result = $this->db->query($sql);
    while ($r = mysql_fetch_assoc($result)) {
        $softid = $r[&#39;id&#39;];
        $keywords = $r[&#39;keywords&#39;];
        $keywords = explode(",", $keywords);
        $related = &#39;&#39;;
        foreach ($keywords as $keyword) {
            $sql = "select softidlist from dp_tag where tagname=&#39;$keyword&#39; ";
            $result2 = $this->db->query($sql);
            while ($r2 = mysql_fetch_assoc($result2)) {
                $related = $related . &#39;,&#39; . $r2[&#39;softidlist&#39;];
            }
        }
        $related = explode(",", $related);
        $related = array_filter($related);
        shuffle($related);
        $related = array_slice($related, 0, 10);
        $related = implode("|", $related);
        $sql = "update v95_soft_data set relation = &#39;$related&#39; where id = $softid ";
        $this->db->query($sql);
    }
    echo &#39;OK&#39;;
}
?>

站点2 单机站

①由于类别不同，要先把原先类别改成现在的类别id

update downtb set ClassID=217 where ClassID=1;

update downtb set ClassID=218 where ClassID=2;

update downtb set ClassID=219 where ClassID=3;

update downtb set ClassID=220 where ClassID=4;

update downtb set ClassID=221 where ClassID=5;

update downtb set ClassID=222 where ClassID=6;

update downtb set ClassID=223 where ClassID=7;

update downtb set ClassID=224 where ClassID=8;

update downtb set ClassID=225 where ClassID=9;

update downtb set ClassID=226 where ClassID=10;

update downtb set ClassID=233 where ClassID=11;

②导入主表到v95_danji

insert into v95_danji(id,catid,title,thumb,soft_name,size,inputtime,updatetime,auth,language,downurl) 

select DownID,ClassID,SeoTitle,thumb,DownName,Sizes,unix_timestamp(addtime),unix_timestamp(addtime),

Shouquan,Languages,DownIntro1 from downtb;

③后台更新全站url，然后设status为99，没有seo标题的采用单机游戏名称

update v95_danji set status=99;

update v95_danji set title=soft_name where title='';

④大小单位转换，下载地址格式调整

update v95_danji set size=size/1000,unit='MB' where size>1000 and size

update v95_danji set size=size/1000000,unit='GB' where size>1000000;

update v95_danji set downurl=replace(downurl,'@@**@@本地下载','');

⑤修改下载地址

要判断单机游戏填写的地址

如果填写的

game=z1.9553.com

game2=z2.9553.com

以此类推

/admin.php?m=admin&c=index&a=downurl（废弃）

⑥导入到v95_danji_data

insert into v95_danji_data(id,content,gameid) select DownID,DownIntro,game_id from downtb;

⑦单机标签转换

1、新增单机站标签到v95_keyword表

insert into v95_keyword(id,keyword) select TagID+31616,TagName from tagtb;

update v95_keyword set siteid=2 where siteid=0;

2、执行php程序

此时记住当前v95_keyword中siteid为1的最大id，更改php程序，再填入keywords,执行

http:///admin.php?m=admin&c=index&a=danji

php代码：

<?php
public function danji() {
    set_time_limit(0);
    $sql = "select DownID,ToTagIDs from downtb";
    $result = $this->db->query($sql);
    while ($r = mysql_fetch_assoc($result)) {
        $id = $r[&#39;DownID&#39;];
        $ToTagIDs = explode(",", $r[&#39;ToTagIDs&#39;]);
        $ToTagIDs = array_filter($ToTagIDs);
        foreach ($ToTagIDs as $key => $value) {
            $ToTagIDs[$key] = $value + 31682;
        }
        //var_dump($ToTagIDs);exit;
        foreach ($ToTagIDs as $tagid) {
            $sql = "select keyword from v95_keyword where id=$tagid ";
            $keywords = $this->db->query($sql);
            while ($r = mysql_fetch_assoc($keywords)) {
                $keyword[] = $r[&#39;keyword&#39;];
            }
        }
        $new_keyword = implode(",", $keyword);
        $sql = "update v95_danji set keywords=&#39;$new_keyword&#39; where id=$id";
        $this->db->query($sql);
        unset($keyword);
        unset($ToTagIDs);
    }
    echo &#39;OK&#39;;
}
?>

3、执行php程序

http:///admin.php?m=admin&c=index&a=danji2

php代码

<?php
public function danji2() {
    set_time_limit(0);
    $sql = "select id,keywords from v95_danji";
    $result = $this->db->query($sql);
    while ($r = mysql_fetch_assoc($result)) {
        $contentid = $r[&#39;id&#39;];
        $keywords = $r[&#39;keywords&#39;];
        $tags = explode(",", $keywords);
        foreach ($tags as $tag) {
            $sql = "select id from v95_keyword where keyword=&#39;$tag&#39; and siteid=2";
            $id = $this->db->query($sql);
            while ($r = mysql_fetch_assoc($id)) {
                $id = $r[&#39;id&#39;];
                $sql = "insert into v95_keyword_data(tagid,siteid,contentid) values(&#39;$id&#39;,2,&#39;$contentid&#39;)";
                $this->db->query($sql);
            }
        }
        unset($tags);
    }
    echo &#39;OK&#39;;
}
?>

4、修改v95_keyword_data表siteid为2的contentid格式

update v95_keyword_data set contentid=CONCAT(contentid,'-14') where siteid=2;

⑧使点击率可用

insert into v95_hits(hitsid,catid) select CONCAT('c-14-',id),catid from v95_danji;

文章网址:

随意转载^^但请附上教程地址。