PHP to implement synchronous remote MySQL

PHP Implementation synchronous remote MySQL:

Requirements: Because the company's English web site is located in the United States, and these web site data to be synchronized with the mainland server data. Sync time is within one day.
After you get the demand, you will find that the MySQL database for both sites cannot be accessed remotely (security first). Then I think of the usual use of CSV file bulk input data. Then try to import the export using CSV.

The import framework is everywhere as follows:

1 first export the data into a CSV format.
Set up a file to be placed on a Chinese server: csv.php. It's actually an export function that obtains data through databases, table names, and SQL statements.

<?php
/**
* Output a table in a database into a CSV file
*
* @param string MySQL database host
* @param str ING database name
* @param table name in string database
* @param string database connection user name
* @param connection password for string database
* @param table of the string database 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= "&nbsp;";


mysql_connect (host, User,pass) or Die ("Cannot connect to the database, the error code is as follows:". Mysql_error ());
mysql_select_db (db);
br>
result = mysql_query (sql_query),
fields_cnt = Mysql_num_fields (Result),
cc= "";

//fp = fopen (filename, ' w ');
//Formatted 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";//replace null value with what
} elseif (row[j] = = ' 0 ' | | row[j]!= ') {
//LOIC1: in quotation marks Contains the field value

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
To create an imported file on a U.S. server, place: import.php, code as follows:

<?php

/**
* Import data from an uploaded file into a table
*
* @param string MySQL database host
* @param string database name
* @param the table name in the string database
* @param the connection user name of the string database
* @param the connection password for the string database
* @param the table name of the string database
*
* @return BOOL executed successfully
*
* @access Public
*/
function Uploadfileofcsv (host,db,user,pass,table,content) {

Mysql_connect (host, User,pass) or Die ("Cannot 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 ("Extracted data as follows:<br>". 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 <script language= ' JavaScript ' >;
echo "Alert" (' first ". The ID of the row is empty, please check! ');";
echo "location.href=document.referrer;";
echo "</script>";
Exit;
} 

query = INSERT into db.table;
query = "VALUES (;
for (q=0;q<fields_cnt;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 <br> ";
Fwrite(Fd,log);
Fclose (FD);
}


 

?>
3 calling function to perform export

Create a file on a Chinese server: test_export.php, call the previous csv.php function, and then convert the data to CSV and then temporarily save it to a form's textera, noting where the form is submitted:

<?php
Require_once ("csv.php");
host= "localhost";
db= "Project";
User= "Root";
Pass= "";

Export tb_contact table data to 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);

?>
<form id= "Form1" Name= "Form1" method= "post" action= "http://U.S. website address/test2.php" >

<p>
<textarea name= "textarea" cols= 180 "rows=" ><?php Echo cc?></textarea>
<input type= "hidden" name= "Action" value= "1"/>
</p>
<p>
<input type= "Submit" name= "Submission" value= "submitted" >
</p>
</form>

Again on the United States server to prevent the following files to accept uploaded data, file name is test_import.php:

<?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, using the Windows-xp/nt/03 control surface version with the task plan, the dispatch executes the Chinese server test_export.php file can