phpcms中实现不同结构数据库转换
1.不同数据库类型直接的数据库转换
navicat是个很好的数据库管理软件,可以用它进行不同类型数据库直接的转换,以下链接详细说明如何用navicat将mssql2000数据库转换成mysql数据库。
2.不同结构数据库之间的数据转换
不同数据库结构之间的数据转换存在几个问题,①字段不同,甚至无法一一对应 ②字段格式不同,要转换成目标数据库字段的设计格式 ③sql语句结合php程序转换
这是把之前一个downplus下载系统的数据库转换成phpcms的数据库。
直接上代码,有这方面需求的研究代码吧。
站点1 软件站
①导入所有软件到软件表v95_soft
<table width="620" align="center" border="0" cellpadding="1" cellspacing="1" style="background:#FB7"> <tr> <td width="464" height="27" bgcolor="#FFE7CE"> 代码如下</td> <td width="109" align="center" bgcolor="#FFE7CE" style="cursor:pointer;" onclick="doCopy('copy2138')">复制代码</td> </tr> <tr> <td height="auto" colspan="2" valign="top" bgcolor="#FFFFFF" style="padding:10px;" class="copyclass" id=copy2138>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
<table width="620" align="center" border="0" cellpadding="1" cellspacing="1" style="background:#FB7"> <tr> <td width="464" height="27" bgcolor="#FFE7CE"> 代码如下</td> <td width="109" align="center" bgcolor="#FFE7CE" style="cursor:pointer;" onclick="doCopy('copy6722')">复制代码</td> </tr> <tr> <td height="auto" colspan="2" valign="top" bgcolor="#FFFFFF" style="padding:10px;" class="copyclass" id=copy6722>update v95_soft set status=99;③到后台更新全站url。然后更新title为空的,软件名称
<table width="620" align="center" border="0" cellpadding="1" cellspacing="1" style="background:#FB7"> <tr> <td width="464" height="27" bgcolor="#FFE7CE"> 代码如下</td> <td width="109" align="center" bgcolor="#FFE7CE" style="cursor:pointer;" onclick="doCopy('copy9855')">复制代码</td> </tr> <tr> <td height="auto" colspan="2" valign="top" bgcolor="#FFFFFF" style="padding:10px;" class="copyclass" id=copy9855>update v95_soft set title=soft_name where title='';④导入到v95_soft_date
<table width="620" align="center" border="0" cellpadding="1" cellspacing="1" style="background:#FB7"> <tr> <td width="464" height="27" bgcolor="#FFE7CE"> 代码如下</td> <td width="109" align="center" bgcolor="#FFE7CE" style="cursor:pointer;" onclick="doCopy('copy6335')">复制代码</td> </tr> <tr> <td height="auto" colspan="2" valign="top" bgcolor="#FFFFFF" style="padding:10px;" class="copyclass" id=copy6335>insert into v95_soft_data(id,content,language,website,icon,softos)select softid,softintro,softlanguage,softauthorurl,IcoImage,softos
from dp_softlist;
⑤执行php转换程序,导入下载地址
http://www.xxx.com/admin.php?m=admin&c=index&a=down
<table width="620" align="center" border="0" cellpadding="1" cellspacing="1" style="background:#FB7"> <tr> <td width="464" height="27" bgcolor="#FFE7CE"> 代码如下</td> <td width="109" align="center" bgcolor="#FFE7CE" style="cursor:pointer;" onclick="doCopy('copy3561')">复制代码</td> </tr> <tr> <td height="auto" colspan="2" valign="top" bgcolor="#FFFFFF" style="padding:10px;" class="copyclass" id=copy3561>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['id'];
$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['fileurlname']) {
$sql = "select soft_name from v95_soft where id = $softid ";
$result2 = $this->db->query($sql);
while ($r2 = mysql_fetch_assoc($result2)) {
$soft_name = $r2['soft_name'];
}
$r['fileurlname'] = $soft_name;
}
if(12 == $r['fileftpid']){
$isbigfile = 1;
}
$downfiles[] = array('fileurl'=>$r['fileurl'],'filename'=>$r['fileurlname'],'isbigfile'=>$isbigfile);
}
$downfiles = array2string($downfiles);
//var_dump($downfiles);exit;
$sql = "update v95_soft_data set downfiles = '$downfiles' where id = $softid ";
$this->db->query($sql);
}
echo 'OK';
}
⑥软件单位和大小转换
<table width="620" align="center" border="0" cellpadding="1" cellspacing="1" style="background:#FB7"> <tr> <td width="464" height="27" bgcolor="#FFE7CE"> 代码如下</td> <td width="109" align="center" bgcolor="#FFE7CE" style="cursor:pointer;" onclick="doCopy('copy1908')">复制代码</td> </tr> <tr> <td height="auto" colspan="2" valign="top" bgcolor="#FFFFFF" style="padding:10px;" class="copyclass" id=copy1908>update v95_soft set size=size/1000,unit='MB' where size>1000 and size<1000000;update v95_soft set size=size/1000000,unit='GB' where size>1000000;
⑦导入标签,到v95_keyword和v95_keyword_data
<table width="620" align="center" border="0" cellpadding="1" cellspacing="1" style="background:#FB7"> <tr> <td width="464" height="27" bgcolor="#FFE7CE"> 代码如下</td> <td width="109" align="center" bgcolor="#FFE7CE" style="cursor:pointer;" onclick="doCopy('copy2624')">复制代码</td> </tr> <tr> <td height="auto" colspan="2" valign="top" bgcolor="#FFFFFF" style="padding:10px;" class="copyclass" id=copy2624>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代码:
<table width="620" align="center" border="0" cellpadding="1" cellspacing="1" style="background:#FB7"> <tr> <td width="464" height="27" bgcolor="#FFE7CE"> 代码如下</td> <td width="109" align="center" bgcolor="#FFE7CE" style="cursor:pointer;" onclick="doCopy('copy5012')">复制代码</td> </tr> <tr> <td height="auto" colspan="2" valign="top" bgcolor="#FFFFFF" style="padding:10px;" class="copyclass" id=copy5012>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['softidlist'];
$tags = explode(",", $tags);
$tags = array_filter($tags);
//var_dump($tags);exit;
$tagid = $r['tagid'];
//echo $tagid;exit;
foreach ($tags as $tag) {
$sql="insert into v95_keyword_data(tagid,siteid,contentid) values('$tagid','1','$tag')";
$this->db->query($sql);
}
}
echo 'OK';
}
调整格式
<table width="620" align="center" border="0" cellpadding="1" cellspacing="1" style="background:#FB7"> <tr> <td width="464" height="27" bgcolor="#FFE7CE"> 代码如下</td> <td width="109" align="center" bgcolor="#FFE7CE" style="cursor:pointer;" onclick="doCopy('copy6424')">复制代码</td> </tr> <tr> <td height="auto" colspan="2" valign="top" bgcolor="#FFFFFF" style="padding:10px;" class="copyclass" id=copy6424>update v95_keyword_data set contentid=CONCAT(contentid,'-12');⑩点击率
<table width="620" align="center" border="0" cellpadding="1" cellspacing="1" style="background:#FB7"> <tr> <td width="464" height="27" bgcolor="#FFE7CE"> 代码如下</td> <td width="109" align="center" bgcolor="#FFE7CE" style="cursor:pointer;" onclick="doCopy('copy2711')">复制代码</td> </tr> <tr> <td height="auto" colspan="2" valign="top" bgcolor="#FFFFFF" style="padding:10px;" class="copyclass" id=copy2711>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);
十一 相关文章
<table width="620" align="center" border="0" cellpadding="1" cellspacing="1" style="background:#FB7"> <tr> <td width="464" height="27" bgcolor="#FFE7CE"> 代码如下</td> <td width="109" align="center" bgcolor="#FFE7CE" style="cursor:pointer;" onclick="doCopy('copy4520')">复制代码</td> </tr> <tr> <td height="auto" colspan="2" valign="top" bgcolor="#FFFFFF" style="padding:10px;" class="copyclass" id=copy4520>http:///admin.php?m=admin&c=index&a=related (最后执行,很慢)
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['id'];
$keywords = $r['keywords'];
$keywords = explode(",", $keywords);
$related = '';
foreach ($keywords as $keyword) {
$sql = "select softidlist from dp_tag where tagname='$keyword' ";
$result2 = $this->db->query($sql);
while ($r2 = mysql_fetch_assoc($result2)) {
$related = $related.','.$r2['softidlist'];
}
}
$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 = '$related' where id = $softid ";
$this->db->query($sql);
}
echo 'OK';
}
站点2 单机站
①由于类别不同,要先把原先类别改成现在的类别id
<table width="620" align="center" border="0" cellpadding="1" cellspacing="1" style="background:#FB7"> <tr> <td width="464" height="27" bgcolor="#FFE7CE"> 代码如下</td> <td width="109" align="center" bgcolor="#FFE7CE" style="cursor:pointer;" onclick="doCopy('copy8990')">复制代码</td> </tr> <tr> <td height="auto" colspan="2" valign="top" bgcolor="#FFFFFF" style="padding:10px;" class="copyclass" id=copy8990>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
<table width="620" align="center" border="0" cellpadding="1" cellspacing="1" style="background:#FB7"> <tr> <td width="464" height="27" bgcolor="#FFE7CE"> 代码如下</td> <td width="109" align="center" bgcolor="#FFE7CE" style="cursor:pointer;" onclick="doCopy('copy5419')">复制代码</td> </tr> <tr> <td height="auto" colspan="2" valign="top" bgcolor="#FFFFFF" style="padding:10px;" class="copyclass" id=copy5419>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标题的采用单机游戏名称
<table width="620" align="center" border="0" cellpadding="1" cellspacing="1" style="background:#FB7"> <tr> <td width="464" height="27" bgcolor="#FFE7CE"> 代码如下</td> <td width="109" align="center" bgcolor="#FFE7CE" style="cursor:pointer;" onclick="doCopy('copy7022')">复制代码</td> </tr> <tr> <td height="auto" colspan="2" valign="top" bgcolor="#FFFFFF" style="padding:10px;" class="copyclass" id=copy7022>update v95_danji set status=99;update v95_danji set title=soft_name where title='';
④大小单位转换,下载地址格式调整
<table width="620" align="center" border="0" cellpadding="1" cellspacing="1" style="background:#FB7"> <tr> <td width="464" height="27" bgcolor="#FFE7CE"> 代码如下</td> <td width="109" align="center" bgcolor="#FFE7CE" style="cursor:pointer;" onclick="doCopy('copy7081')">复制代码</td> </tr> <tr> <td height="auto" colspan="2" valign="top" bgcolor="#FFFFFF" style="padding:10px;" class="copyclass" id=copy7081>update v95_danji set size=size/1000,unit='MB' where size>1000 and size<1000000;update v95_danji set size=size/1000000,unit='GB' where size>1000000;
update v95_danji set downurl=replace(downurl,'@@**@@本地下载','');
⑤修改下载地址
要判断单机游戏填写的地址
如果填写的
game2=z2.9553.com
以此类推
/admin.php?m=admin&c=index&a=downurl(废弃)
⑥导入到v95_danji_data
<table width="620" align="center" border="0" cellpadding="1" cellspacing="1" style="background:#FB7"> <tr> <td width="464" height="27" bgcolor="#FFE7CE"> 代码如下</td> <td width="109" align="center" bgcolor="#FFE7CE" style="cursor:pointer;" onclick="doCopy('copy9089')">复制代码</td> </tr> <tr> <td height="auto" colspan="2" valign="top" bgcolor="#FFFFFF" style="padding:10px;" class="copyclass" id=copy9089>insert into v95_danji_data(id,content,gameid) select DownID,DownIntro,game_id from downtb;⑦单机标签转换
1、新增单机站标签到v95_keyword表
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代码:
<table width="620" align="center" border="0" cellpadding="1" cellspacing="1" style="background:#FB7"> <tr> <td width="464" height="27" bgcolor="#FFE7CE"> 代码如下</td> <td width="109" align="center" bgcolor="#FFE7CE" style="cursor:pointer;" onclick="doCopy('copy3614')">复制代码</td> </tr> <tr> <td height="auto" colspan="2" valign="top" bgcolor="#FFFFFF" style="padding:10px;" class="copyclass" id=copy3614>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['DownID'];
$ToTagIDs = explode(",", $r['ToTagIDs']);
$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['keyword'];
}
}
$new_keyword = implode(",", $keyword);
$sql="update v95_danji set keywords='$new_keyword' where id=$id";
$this->db->query($sql);
unset($keyword);
unset($ToTagIDs);
}
echo 'OK';
}
3、执行php程序
http:///admin.php?m=admin&c=index&a=danji2
php代码:
<table width="620" align="center" border="0" cellpadding="1" cellspacing="1" style="background:#FB7"> <tr> <td width="464" height="27" bgcolor="#FFE7CE"> 代码如下</td> <td width="109" align="center" bgcolor="#FFE7CE" style="cursor:pointer;" onclick="doCopy('copy1167')">复制代码</td> </tr> <tr> <td height="auto" colspan="2" valign="top" bgcolor="#FFFFFF" style="padding:10px;" class="copyclass" id=copy1167>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['id'];
$keywords = $r['keywords'];
$tags = explode(",", $keywords);
foreach ($tags as $tag) {
$sql = "select id from v95_keyword where keyword='$tag' and siteid=2";
$id = $this->db->query($sql);
while ($r = mysql_fetch_assoc($id)) {
$id = $r['id'];
$sql = "insert into v95_keyword_data(tagid,siteid,contentid) values('$id',2,'$contentid')";
$this->db->query($sql);
}
}
unset($tags);
}
echo 'OK';
}
4、修改v95_keyword_data表siteid为2的contentid格式
<table width="620" align="center" border="0" cellpadding="1" cellspacing="1" style="background:#FB7"> <tr> <td width="464" height="27" bgcolor="#FFE7CE"> 代码如下</td> <td width="109" align="center" bgcolor="#FFE7CE" style="cursor:pointer;" onclick="doCopy('copy8846')">复制代码</td> </tr> <tr> <td height="auto" colspan="2" valign="top" bgcolor="#FFFFFF" style="padding:10px;" class="copyclass" id=copy8846>update v95_keyword_data set contentid=CONCAT(contentid,'-14') where siteid=2;⑧使点击率可用
<table width="620" align="center" border="0" cellpadding="1" cellspacing="1" style="background:#FB7"> <tr> <td width="464" height="27" bgcolor="#FFE7CE"> 代码如下</td> <td width="109" align="center" bgcolor="#FFE7CE" style="cursor:pointer;" onclick="doCopy('copy4385')">复制代码</td> </tr> <tr> <td height="auto" colspan="2" valign="top" bgcolor="#FFFFFF" style="padding:10px;" class="copyclass" id=copy4385>insert into v95_hits(hitsid,catid) select CONCAT('c-14-',id),catid from v95_danji;
您可能感兴趣的文章:
phpcms用什么开发的
phpcms怎么做网站
php采集cms有哪些
phpcms调用栏目文章总数与信息总数的代码
phpcms教程之shtml的设置方法
PHPCMS v9 如何在手机端栏目绑定模板
phpcms是什么意思啊?
C/S、B/S软件技术上的比较
phpcms如何制作模板
建立对象数据库-内存映射范式,需要中间层容器的支持