Hello you can sync your local database data with your live mysql server. I had done it making a local sync file that will present all the tables of the local database that I wanted to update to live database and then calling a call back function in footer file that function calls the function to sync local data to live server Footer.php File
Footer.php
Config.php CodeConfig.php
Sync.php Code syncsync.php contents Also you need to on CURL extension in local site wamp so that it can communicate with the server side, If database record that is already synced to server edited then sync field in DB should be set to 0 so that on edit it can overwrite server DB again.
Sync.php
<?php
class Sync extends CI_Controller {
private $tables;
function __construct() {
parent::__construct();
$this->load->database();
$this->tables = array(
"customers" => array("per_id"),
"db_log" => array("id"),
"inventory" => array("tr_id"),
"material" => array("id_mat"),
"mtracking" => array("id_tra"),
"orecipie" => array("id_rec"),
"moutlets" => array("id_st"),
"opeople" => array("per_id"),
"oreceivings" => array("rec_id"),
"osales" => array("sal_id"),
"osales_items" => array("sal_id", "itm_id"),
"osales_payments" => array("sal_id"),
"osales_sus" => array("sal_id", "emp_id"),
"osales_sus_items" => array("sal_id", "itm_id"),
"osales_sus_payments" => array("sal_id"),
"osuppliers" => array("per_id"),
);
$this->id_store = $this->config->item('id_store');
}
} function index() {
echo '<br>'.date("H:i:s A");
$newLine = "\r\n";
$output = $update = "";
foreach($this->tables as $table_name => $columns ){
function index() {
echo '<br>'.date("H:i:s A");
$newLine = "\r\n";
$output = $update = "";
foreach($this->tables as $table_name => $columns ){
$col_check = "SHOW COLUMNS FROM `{$table_name}` LIKE 'is_sync'";
$col_exists = $this->db->query($col_check);
if ( $col_exists->num_rows > 0 ) { // is_sync column exists for this table
$sql = "SELECT * FROM {$table_name} WHERE is_sync = 0 LIMIT 500" ;
$rows = $this->db->query($sql);
if ( $rows->num_rows > 0 ) { // where is_sync = 0
foreach ( $rows->result_array() as $row ) {
$col_val = $update_col_val = $already_exists = array();
foreach($row as $name => $val) {
if ( is_null( $val ) ) {
continue;
}
$val = mysql_real_escape_string($val);
$update_col_val[] = " `{$name}` = '{$val}' ";
if ( "is_sync" === $name ) {
$val = 1;
}
if ( "id_store" === $name ) {
$val = $this->id_store;
}
$col_val[] = " `{$name}` = '{$val}' ";
if ( $name === $columns[0] ){
$already_exists[] = " `$columns[0]` = '{$val}'";
}
if ( $name === $columns[1] ){
$already_exists[] = " `$columns[1]` = '{$val}'";
}
}
if ( $nameis_array($col_val) ===&& $columns[1]count($col_val) > 0 ) {
$already_exists[]
//echo $this->table_keys[$table_name];
$output .= "SELECT * FROM `{$table_name}` WHERE `id_store` = {$this->id_store} AND " `$columns[1]`. implode(" AND ", $already_exists) . " ||";
$output .= '"INSERT INTO `{$val$table_name}'";` SET " . implode(",", $col_val) . " ||";
$output .= "UPDATE `{$table_name}` SET " . implode(",", $col_val) . " WHERE `id_store` = {$this->id_store} AND " . implode(" AND ", $already_exists);
$output .= " ;; ";
$update .= "UPDATE {$table_name} SET is_sync = 1 WHERE " . implode(" AND ", $update_col_val) . " ;; ";
}
}
} // if ( is_array($col_val) && count($col_val)is_sync >= 0 ) {
} //echo $this->table_keys[$table_name];
if is_sync column exists
$output .= "SELECT * FROM `{$table_name}` WHERE `id_store` = {$this->id_store} AND " . implode(" AND ", $already_exists) . " ||";
if ( !empty($output .= "INSERT INTO `{$table_name}` SET ") .&& implode$this->post_data(",", $col_val$output) . ") ||";{
$output .= "UPDATE `{$table_name}` SET " . implode(",", $col_val) . " WHERE `id_store`$update = {$this->id_store} AND " . implodeexplode(" AND;; ", $already_exists$update);
$output .= " ;; ";
foreach($update as $upq){
$update .= "UPDATE {$table_name} SET is_sync = 1 WHERE " .if implode(" AND ", $update_col_val!empty($upq) . " ;;) ";{
}$this->db->query($upq);
}
} // if is_sync = 0
} // if is_sync column exists
}
if ( !empty($output) && $this->post_data($output) ) {
$update = explode(" ;; ", $update);
foreach($update as $upq){
if (!empty($upq) ) {
$this->db->query($upq);
}
} else {
echo $result;
}
} else {
echo $result;date("H:i:s A");
}
echo date("H:i:s A");
}
function post_data($qry){
$qry = htmlspecialchars(urlencode($qry));
$data = "qry=" . $qry;
$ch = curl_init();
curl_setopt( $ch, CURLOPT_URL,$this->config->item("sync_server_url") );
curl_setopt( $ch, CURLOPT_AUTOREFERER, 1);
curl_setopt ( $ch, CURLOPT_HTTPHEADER, array ( 'Content-length: ' . strlen($qry) ) );
curl_setopt( $ch, CURLOPT_RETURNTRANSFER, 1);
curl_setopt( $ch, CURLOPT_POST,1);
curl_setopt( $ch, CURLOPT_POSTFIELDS,$data);
curl_setopt( $ch, CURLOPT_CRLF, 1);
$result = curl_exec($ch);
curl_close($ch);
if ( 1 === intval($result) ) {
return TRUE;
} else {
echo $result;
return $result;
}
}
}
}
?>
<?php
class Sync_server extends CI_Controller {
function __construct() {
parent::__construct();
$this->load->database();
}sync_server.php
<?php
class Sync_server extends CI_Controller {
function __construct() {
parent::__construct();
$this->load->database();
}
function index() {
if (isset( $_POST['qry'] ) ) {
$qry = htmlspecialchars_decode( urldecode( $_POST['qry']));
//$this->connect_local_db();
$qry = explode(" ;; ", $qry);
foreach($qry as $q) {
$q = explode( "||", $q );
//print_r($q);
$exists = mysql_query( $q[0] ) or die ("<hr/>" . mysql_error() ."<br/>" . $q[0]);
if ( $exists && mysql_num_rows( $exists ) ) {
mysql_query( $q[2] ) or die ("<hr/>" . mysql_error()."<br />".$q[2]);
} else{
mysql_query( $q[1] ) or die ("<hr/>" . mysql_error()."<br />".$q[1]);
}
//$this->db->query($q);
}
die("1");
} else {
echo "qry not found";
}
}
}
?>
}
