PHP realizes synchronization of remote Mysql_PHP tutorial-PHP Tutorial-php.cn

PHP realizes synchronization of remote Mysql_PHP tutorial

WBOY

Release： 2016-07-13 10:27:50

Original

1014 people have browsed it

PHP implements remote Mysql synchronization:

Requirements: Since the company’s English website is placed in the United States, the data of these websites must be synchronized with the server data in mainland China. Synchronization time is within one day.
After getting the requirements, I found that the MYSQL databases of these two websites cannot be accessed remotely (security first). Then I remembered the csv file I usually use to enter data in batches. So I tried to use CSV import and export.

The imported framework is as follows:

1 First export the data into CSV format.
Create a file and place it on the Chinese server: csv.php. It is actually an export function that obtains data through the database, table name and SQL statement.

/**
* Output a table in a database to a CSV file
*
* @param string Mysql database host
* @param string database name
* @param string table name in database
* @param string database connection username
* @param string database connection password
* @param string database table name
* @param string database
* @param string error page
* @param string SQL statement
*
* @return text Returns the content in CSV format
*
* @access public
*/
function PMA_exportData(host,db,user,pass,filename,table, crlf, error_url, sql_query) {
what="csv";
csv_terminated=" ";
csv_separator=",";
csv_enclosed=" ";
csv_escaped=" ";

mysql_connect(host, user,pass) or die("Unable to connect to the database, the error code is as follows:" . mysql_error());
mysql_select_db(db);

result = mysql_query(sql_query);
fields_cnt = mysql_num_fields(result);
cc="";

//fp = fopen(filename, 'w');
//Format data
while (row = mysql_fetch_row(result)) {
schema_insert = '';
for (j = 0; j < fields_cnt; j++) {
if (!isset(row[j]) || is_null(row[j])) {
schema_insert .="NULL"; //What to use to replace null values
} elseif (row[j] == '0' || row[j] != '') {
// loic1: Use quotes to include field values

if (csv_enclosed == '') {
schema_insert .= row[j];
} else {
schema_insert .= csv_enclosed
. str_replace(csv_enclosed, csv_escaped . csv_enclosed, row[j])
.csv_enclosed;
}
} else {
schema_insert .= '';
}
if (j < fields_cnt-1) {
schema_insert .= csv_separator;
}
} // end for

// fwrite(fp,schema_insert . csv_terminated);
cc.=schema_insert . csv_terminated;
} // end while
mysql_free_result(result);

// fclose(fp);
return cc;
}

?>

2. Import the content in CSV format into the table
Create an imported file on the US server and place it: import.php. The code is as follows:

/**
* Import data from an uploaded file into a table
*
* @param string Mysql database host
* @param string database name
* @param string table name in database
* @param string database connection username
* @param string database connection password
* @param string database table name
*
* @return bool Whether the execution was successful
*
* @access public
*/
function uploadFileOfCsv(host,db,user,pass,table,content){
mysql_connect(host, user,pass) or die("Unable to connect to the database, the error code is as follows: " . mysql_error());
mysql_select_db(db);

result = mysql_query("select * from table");
fields_cnt = mysql_num_fields(result);

test2=array(array());
rownum=0;
log("The extracted data is as follows:
".content);
fd1 = fopen ("C:test.csv",'a');
fwrite(fd1,content);
fclose(fd1);

fp = fopen("C:test.csv", "r");

while (buffer = fgets(fp,4096))
{
i++;

tmp_arr = explode(",",buffer);

if(trim(tmp_arr[0]) == ""){
echo "";
exit;
}

query = "INSERT INTO db.table";
query .=" values ( ";
for(q=0;q if(q==fields_cnt-1){
tmp=tmp_arr[q];
query.="'tmp');";
}else{
tmp=tmp_arr[q];
query.="'tmp',";

}
}//end for(q=0;

log2(query);
mysql_query(query);

}
fclose(fp);

return "OK";
unlink("C:test.csv");

}

function log2(event = null){
//global db;
// global login;

if(LOG_ENABLED){
now = date("Y-M-d H:i:s");

fd = fopen ("C:log.html",'a');
log = now." "._SERVER["REMOTE_ADDR"] ." - event
";
fwrite(fd,log);
fclose(fd);
}
}

?>
3Call the function to execute the export

Create another file on the Chinese server: test_export.php, call the function of the previous csv.php, then convert the data into CSV, and then temporarily save it to the textera of a form. Pay attention to the location of form submission:

require_once("csv.php");
host="localhost";
db="project";
user="root";
pass="";

//Export the data of tb_contact table as csv file
filename = 'file4.csv';
cc=PMA_exportData( host,db,user,pass, filename,"tb_project_dvp", "", "test.php", "select * from tb_project_dvp") ;

handle = fopen(filename, "rb");
contents = fread(handle, filesize (filename));
fclose(handle);

?>

<?php echo cc?>

Then set up the following file on the US server to accept the uploaded data, the file name is test_import.php:

require_once("csv.php");
require_once("import.php");

host="localhost";
db="wintopweb";
user="root";
pass="";

if(_POST['action']=="1"){

content=_POST['textarea'];
echo uploadFileOfCsv(host,db,user,pass,"tb_project_dvp",content);

}

?>

Finally, use the task plan that comes with the Windows-xp/nt/03 control panel to schedule and execute the Chinese server test_export.php file