Php+mysqli method to implement batch substitution database table prefixes _php tips

This article illustrates the method of Php+mysqli to implement batch substitution database table prefixes. Share to everyone for your reference. The specific analysis is as follows:

In PHP sometimes we want to replace the table prefix in the database but do not suffer a table to modify the prefix, here I wrote a mysqli batch replacement database table prefix PHP program, interested friends can refer to the code as follows:

<?php header (' http-equiv= "Content-type" content= "text/html; Charset=utf-8" "); $DB _host = "localhost"; Database host $DB _user = "root"; Database user $DB _PSW = "root3306"; Database password $DB _datebase = "gk_yue39_com"; Database name $DB _charset = "UTF8"; 
Database Character Set $dbprefix = "Yue392_com_"; 
$new _dbprefix= "Yue39_com_"; $db = new Mysqli ($DB _host, $DB _user, $DB _PSW); 
 Instantiate object//Check connection if (Mysqli_connect_errno ()) {printf ("Connect failed:%sn", Mysqli_connect_error ()); 
Exit (); $db->select_db ($DB _datebase); Select the Operation database $db->set_charset ($DB _charset); 
Set the database character set//execute a query $sql = ' show tables '; 
 
$result = $db->query ($sql); Echo $result->num_rows. ' Line result '. $result->field_count. 
 
' column content <br/> '; $result->data_seek (' 5 ')//starting from 5th of result set Echo ' <table border= "1" cellspacing= "0" cellpadding= "0" align= " 
 
Center "width=" 90% ">"; Loop output field name//$result->field_seek (2);//start with 2nd in the field set while (true = = ($field = $result->fetch_field ()) {ECHo ' <th> '. $result->current_field. '_' . $field->name. ' ('. $field->length. 
') </th> '; 
 ///Loop output query result while (true = = ($row = $result->fetch_assoc ())) {echo ' <tr> '; foreach ($row as $col) {$sql = "rename table". $col. " ' To '. Str_replace ($dbprefix, $new _dbprefix, $col). " 
 `"; if ($db->query ($sql)) {echo ' <td align= ' center ' > '. $sql. 
 ' </td><td><font color= ' Blue ' > success</font></td> '; }else{Echo ' <td align= ' center ' > '. $sql.
 ' </td><td><font color= ' red ' > failed</font></td> '; 
} echo ' </tr> '; 
Echo ' </table> '; $result->free ()//release result set $db->close (); Close Connection?>

The second method: How to Batch modify MySQL database table prefix name

The operation method of the batch modifies table name, the following operation can be operated with Navicat, quick and convenient:

Copy Code code as follows:

SELECT CONCAT (' ALTER TABLE ', table_name, ' RENAME to ', table_name, '; ')
From INFORMATION_SCHEMA. TABLES
WHERE table_name like ' uc_% ';

After implementation, the following results are obtained:

Copy Code code as follows:

ALTER TABLE uc_aaa RENAME to UC_AAA;
ALTER TABLE uc_bbb RENAME to UC_BBB;

Keep the following data:

Copy Code code as follows:

ALTER TABLE uc_aaa RENAME to UC_AAA;
ALTER TABLE uc_bbb RENAME to UC_BBB;

Then select the database you want to modify, and execute the resulting SQL statement to modify the database table prefix.
Annex: 1, bulk delete the specified prefix table

Copy Code code as follows:

SELECT CONCAT (' drop table ', table_name, '; ')
From INFORMATION_SCHEMA. TABLES
WHERE table_name like ' uc_% ';

2, "dbtable_name" changed to "Db_table_name"

Copy Code code as follows:

SELECT CONCAT (' ALTER TABLE ', table_name, ' RENAME to Db_ ', SUBSTRING (table_name,3), '; ')
From INFORMATION_SCHEMA. TABLES
WHERE table_name like ' db% ';

The third method:

Another way to update a MySQL database table prefix today is to use a third party tool, the imperial backup king, to modify it. The operation method is as follows:

1, Login to the Empire backup background, select "Backup Data"-"Batch replacement table name", and then perform the operation can be completed bulk replacement, but also very simple. The following figure:

How to bulk modify MySQL table prefix name

The above methods can modify the site prefix, but most of the open source program to modify the suffix, and then need to modify the Web site program database configuration file, will allow the site to run the site.
I hope this article will help you with your PHP program design.