Converts different structured databases in phpcms

The requirement is to convert an mssql2000 database of the previous website into a mysql database and use different programs for the new website. Therefore, the data in the database must be based on different fields, different from the field data rules. 1. different database types directly... the requirement is to convert an mssql2000 database of the previous website into a mysql database and use different programs for the new website. Therefore, the data in the database must be based on different fields, different from the field data rules.

1. direct database conversion for different database types

Navicat is a good database management software that can be used for direct conversion between different types of databases. the following link describes how to use navicat to convert a database named mssql2000 to a mysql database.

2. Data Conversion between databases of different structures

There are several problems with data conversion between different database structures. ① The fields are different and cannot even correspond one to one. ② The field formats are different. convert the fields to the design format of the target database. ③ SQL statements are converted using php programs.

This is to convert the database of the previous downplus download system into a phpcms database.

Go directly to the code and study the code with this requirement.

Site 1 software site

① Import all software to the software table 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;

② Set the SQL write status to 99.

Update v95_soft set status = 99;

③ Update the full site url in the background. Then, update the software name with the title empty.

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

④ Import to v95_soft_date

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

Select softid, softintro, softlanguage, softauthorurl, IcoImage, softos

From dp_softlist;

⑤ Execute the php conversion program and import

Http://www.phprm.com/admin.php? M = admin & c = index & a = down

 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';}?>

⑥ Software unit and size conversion

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;

7. import tags to v95_keyword and v95_keyword_data.

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

Update v95_keyword set siteid = 1;

⑧ Execute the php conversion program and match tags to keywords.

Http: // admin. php? M = admin & c = index & a = transe

Php code:

 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';}?>

Adjust format

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

Renewal click rate

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

// You don't need to switch. it's too slow.

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 );

11 Related articles

Http: // admin. php? M = admin & c = index & a = related (last execution, very slow)

Php code:

 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';}?>

Site 2 stand-alone

① Due to different categories, you must first change the original category to the current Category id

Update downtb set ClassID = 217 where ClassID = 1;

Update downtb set ClassID = 218 where ClassID = 2;

Update downtbset 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;

② Import the master table to 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, ages, DownIntro1 from downtb;

③ Update the full-site url in the background, set status to 99, and use the single-host game name without the seo title

Update v95_danji set status = 99;

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

④ Size unit conversion, format adjustment

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, '@ ** @ local Download ','');

⑤ Modification

Determine the address for a single-host game

If you enter

Game = z1.9553.com

Game2 = z2.9553.com

And so on

/Admin. php? M = admin & c = index & a = downurl (obsolete)

⑥ Import to v95_danji_data

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

7. single-host label conversion

1. added the standalone site label to the v95_keyword table.

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

Update v95_keyword set siteid = 2 where siteid = 0;

2. execute the php program

Remember the maximum id of siteid 1 in the current v95_keyword, change the php program, enter keywords, and execute

Http: // admin. php? M = admin & c = index & a = danji

Php code:

 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. execute the php program

Http: // admin. php? M = admin & c = index & a = danji2

Php code

 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. modify the contentid format of the v95_keyword_data table siteid to 2.

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

Enabled to make click rate available

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

Article URL:

Reprint ^ at will, but please attach the tutorial address.