我想同时同步和更新本地数据库和远程数据库 . 店主在不同的地方有四家餐厅（ A ， B ， C ， D ） . 他在所有四家餐厅都保持相同的产品价格和相同的质量 . 因此他使用远程数据库来改变曾经影响所有分支机构价格的价格 . 所有四个分支和远程服务器具有相同的数据库结构和相同的表（即：分支还有其他分支的记录 . 每个分支的每个分支由 id 和 branch 字段（ id + branch as composite key ）唯一标识 .
Sample table (purchase)
+----+--------+------------+------------+-----+--------------------+---------------------+
| id | branch | item | unit_price | qty | added_on | last_updated |
+----+--------+------------+------------+-----+--------------------+---------------------+
| 1 | A | Pizza | 800 | 5 |2018-12-05T15:47:54 | 2018-05-11T15:47:54 |
+----+--------+------------+------------+-----+--------------------+---------------------+
| 2 | A | Chicken | 350 | 5 |2018-12-05T15:49:54 | 2018-05-11T15:50:54 |
+----+--------+------------+------------+-----+--------------------+---------------------+
| 2 | B | cappuccino | 280 | 7 |2018-12-05T15:47:24 | 2018-05-11T15:47:24 |
+----+--------+------------+------------+-----+--------------------+---------------------+
我有以下代码从本地数据库中提取新添加的记录和更新的记录（使用字段 added_on 和 last_updated ），然后使用计时器在远程数据库中上载和导入 . 这里，从本地数据库中提取记录的检查时间存储在将由计时器使用的文件中 .
现在，在每个分支中更新本地数据库（下载其他分支记录）如何从远程服务器下载新插入的记录和更新的记录？
php script on remote server ( To insert on remote database)(execute.php)
<?php
try
{
$connect = mysqli_connect("localhost", "username", "password", "database");
$query = '';
$table_data = '';
$filename = "app_restaurant.json";
$data = file_get_contents($filename);
$array = json_decode($data, true);
foreach ($array as $set)
{
$tblName = $set['tableName'];
if (sizeof($set['rows']) > 0)
{
$query = '';
$colList = array();
$valList = array();
// Get list of column names
foreach ($set['rows'][0] as $colName => $dataval)
{
$colList[] = "`" . $colName . "`";
}
$query .= "INSERT INTO `" . $tblName . "` \n";
$query .= "(" . implode(",", $colList) . ")\nVALUES\n";
// Go through the rows for this table.
foreach ($set['rows'] as $idx => $row)
{
$colDataA = array();
// Get the data values for this row.
foreach ($row as $colName => $colData)
{
$colDataA[] = "'" . $colData . "'";
}
$valList[] = "(" . implode(",", $colDataA) . ")";
}
// Add values to the query.
$query .= implode(",\n", $valList) . "\n";
// If id column present, add ON DUPLICATE KEY UPDATE clause
if (in_array("`id`", $colList))
{
$query .= "ON DUPLICATE KEY UPDATE\n\t";
$tmp = array();
foreach ($colList as $idx => $colName)
{
//$tmp[] = $colName." = new.".$colName." ";
// Changed the following line to get value from current insert row data
$tmp[] = $colName . " = VALUES(" . $colName . ") ";
}
$query .= implode(",", $tmp) . "\n";
}
else
{
echo "<p><b>`id`</b> column not found. <i>ON DUPLICATE KEY UPDATE</i> clause <b>NOT</b> added.</p>\n";
echo "<p>Columns Found:<pre>" . print_r($colList, true) . "</pre></p>\n";
}
echo "<p>Insert query:<pre>$query</pre></p>";
$r = mysqli_query($connect, $query);
echo mysqli_errno($connect) . ": " . mysqli_error($connect) . "\n";
echo "<h1>" . mysqli_affected_rows($connect) . " Rows appended in .$tblName.</h1>";
}
else
{
echo "<p>No rows to insert for .$tblName.</p>";
}
}
}
catch (Exception $e)
{
echo $e->getMessage();
}
?>
file up-loader(upload.php)
<?php
$filepath = $_FILES["file"]["tmp_name"];
move_uploaded_file($filepath,"app_restaurant.json");
?>
1.create JSON file from local database
private void btnExportJson_Click(object sender, EventArgs e)
{
string filePath = @"C:\Users\testeam-PC\Desktop\app_restaurant.json";
if(File.Exists(filePath))
{
MessageBox.Show("Sorry! The file is already exists, Please restart the operation","File Exists");
File.Delete(filePath);
}
else
{
MySQL mysql = new MySQL();
var source_result = false;
source_result = mysql.check_connection(myConString);
if (source_result == false)
{
MessageBox.Show("Sorry! Unable to connect with XAMP / WAMP or MySQL.\n Please make sure that MySQL is running.", "Local Database Connection Failure");
}
else
{
// MessageBox.Show("Connected");
int count = 0;
using (var connection = new MySqlConnection(myConString))
{
connection.Open();
// get the names of all tables in the chosen database
var tableNames = new List<string>();
using (var command = new MySqlCommand(@"SELECT table_name FROM information_schema.tables where table_schema = @database", connection))
{
command.Parameters.AddWithValue("@database", "app_restaurant");
using (var reader = command.ExecuteReader())
{
while (reader.Read())
tableNames.Add(reader.GetString(0));
}
}
// open a JSON file for output; use the streaming JsonTextWriter interface to avoid high memory usage
using (var streamWriter = new StreamWriter(filePath))
// For seperate lines may be huge capacity
using (var jsonWriter = new JsonTextWriter(streamWriter) { Formatting = Newtonsoft.Json.Formatting.Indented, Indentation = 2, IndentChar = ' ' })
//using (var jsonWriter = new JsonTextWriter(streamWriter) )
{
// one array to hold all tables
jsonWriter.WriteStartArray();
foreach (var tableName in tableNames)
{
//MessageBox.Show(tableName);
count += 1;
// an object for each table
jsonWriter.WriteStartObject();
jsonWriter.WritePropertyName("tableName");
jsonWriter.WriteValue(tableName);
jsonWriter.WritePropertyName("rows");
// an array for all the rows in the table
jsonWriter.WriteStartArray();
// select all the data from each table
using (var command = new MySqlCommand(@"SELECT * FROM " + tableName + " WHERE (last_updated >= '" + local_checked_time + "') OR (added_on >= '" + local_checked_time + "')", connection))
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
// write each row as a JSON object
jsonWriter.WriteStartObject();
for (int i = 0; i < reader.FieldCount; i++)
{
jsonWriter.WritePropertyName(reader.GetName(i));
jsonWriter.WriteValue(reader.GetValue(i));
}
jsonWriter.WriteEndObject();
}
}
jsonWriter.WriteEndArray();
jsonWriter.WriteEndObject();
}
jsonWriter.WriteEndArray();
MessageBox.Show("Totally " + count + " tables circulated", "Success");
btnUploadToServer.Enabled = true;
// Application.Exit();
//btnUploadToServer_Click(sender, e);
}
}
}
}
}
2.upload the JSON file to server
private void btnUploadToServer_Click(object sender, EventArgs e)
{
bool connection = NetworkInterface.GetIsNetworkAvailable();
if (connection == true)
{
//MessageBox.Show("Internet Available");
try
{
using (WebClient client = new WebClient())
{
string filePath = @"C:\Users\testeam-PC\Desktop\app_restaurant.json";
var myUri = new Uri(@"http://youraddress.com/path/upload.php");
client.UploadFile(myUri, filePath);
client.Credentials = CredentialCache.DefaultCredentials;
}
}
catch (Exception err)
{
MessageBox.Show(err.Message);
}
MessageBox.Show("Successfully Uploaded", "Success");
btnExecuteURL.Enabled = true;
// btnExecuteURL_Click(sender, e);
}
else
{
MessageBox.Show("There is no internet connection.\n Please make sure that you have an internet connection.", "No Internet");
}
}
3.Execute the file
private void btnExecuteURL_Click(object sender, EventArgs e) {
bool connection = NetworkInterface.GetIsNetworkAvailable();
if (connection == true) {
//MessageBox.Show("Internet Available");
try {
// Launch the execution code...
System.Diagnostics.Process.Start("http://youraddress.com/path/execute.php");
}
catch(Exception err) {
MessageBox.Show(err.Message);
}
}
else {
MessageBox.Show("There is no internet connection.\n Please make sure that you have internet connection.", "No Internet");
}
}