Converts different structured databases in phpcms

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

The code is as follows:	Copy code
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.

The code is as follows:	Copy code
Update v95_soft set status = 99;

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

The code is as follows:	Copy code
Update v95_soft set title = soft_name where title = '';

④ Import to v95_soft_date

The code is as follows:	Copy code
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 the download address

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

The code is as follows:

Copy code

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

⑥ Software unit and size conversion

The code is as follows:	Copy code
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.

The code is as follows:	Copy code
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:

The code is as follows:

Copy code

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

Adjust format

The code is as follows:	Copy code
Update v95_keyword_data set contentid = CONCAT (contentid, '-12 ');

Renewal click rate

The code is as follows:	Copy code
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

The code is as follows:

Copy code

Http: // admin. php? M = admin & c = index & a = related (last execution, very slow) Php code: 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 ['keyword']; $ 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

The code is as follows:

Copy code

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

The code is as follows:	Copy code
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

The code is as follows:	Copy code
Update v95_danji set status = 99; Update v95_danji set title = soft_name where title = '';

④ Size unit conversion, download address format adjustment

The code is as follows:	Copy code
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 ','');

⑤ Modify the download address
Determine the address for a single-host game
If you enter

The code is as follows:	Copy code
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

The code is as follows:	Copy code
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.

The code is as follows:	Copy code
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:

The code is as follows:

Copy code

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

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

Php code:

The code is as follows:

Copy code

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 ['keyword']; $ 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.

The code is as follows:	Copy code
Update v95_keyword_data set contentid = CONCAT (contentid, '-14') where siteid = 2;

Enabled to make click rate available

The code is as follows:	Copy code
Insert into v95_hits (hitsid, catid) select CONCAT ('c-14-', id), catid from v95_danji;