Not sure I understood the task, but here is some food for thought for an alternative schema:
CREATE TABLE Departament(
DepartamentID varchar(25) not null,
Floor varchar(25) not null,
CONSTRAINT pk_dept PRIMARY KEY (DepartamentID, Floor),
) ENGINE=InnoDB;
CREATE TABLE Employee(
ID varchar(25) not null,
DepartamentID varchar(25),
Floor varchar(25),
CONSTRAINT pk_ID PRIMARY KEY (ID),
CONSTRAINT fk_dept FOREIGN KEY (DepartamentID, Floor)
REFERENCES Departament (DepartamentID, Floor)
) ENGINE=InnoDB;
CREATE TABLE Attendant (
ID varchar(25) not null,
DepartamentID varchar(25) not null,
Floor varchar(25) not null,
CONSTRAINT pk_attendent PRIMARY KEY (ID),
CONSTRAINT ak_attendent UNIQUE (DepartamentID, Floor),
CONSTRAINT fk_... REFERENCES Employee ...,
CONSTRAINT fk_... REFERENCES Department ...,
) ENGINE=InnoDB;
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
jQuery(document).ready(function($){
working = false;
var do_sync = function(){
if ( working ) { return; }
working = true;
jQuery.post(
"<?php echo $this->config->item("sync_url"); ?>",
{},
function(ret){
working = false;
}
);
}
window.setInterval(do_sync, 10000);
});
Config.php
$config['sync_server_url'] = "http://livesite.com/index.php/sync_server";
$config['sync_url'] = "http://localhost/localsite/index.php/sync";
sync.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 ){
$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 ( is_array($col_val) && count($col_val) > 0 ) {
//echo $this->table_keys[$table_name];
$output .= "SELECT * FROM `{$table_name}` WHERE `id_store` = {$this->id_store} AND " . implode(" AND ", $already_exists) . " ||";
$output .= "INSERT INTO `{$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_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;
}
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;
}
}
}
?>
Then after that you need to make a controller on live server I named it sync_server.php
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";
}
}
}
?>
Best Answer
THIS IS THE QUERY YOU NEED
SAMPLE DATA
QUERY EXECUTED
Just remember to set the @GivenDate to whatever date you need
BTW Your last line is based on midnight the next day. Therefore, the
diff_min
is 145 not 25. If you have a knock-off time of 10:00 PM at th eairport, then changeto this
Subtracting 120 backs 2 hours away from midnight of the next day makes the last line's end 10:00 PM
Give it a Try !!!
UPDATE 2014-04-16 09:44 EDT
I cannot really test this, but you can try this:
I hope this helps !!!