Copy table data from one databse to another databse table daily

[sunilpaladugu]

Hi all

 

I am describing my problem below can any body plz look into this

 

I have two databases

DB1:datain1

DB2:datain2

having the below structure

 

database,            table,                        fields

    datain1 ,            student  ,                    id,name

    datain2 ,            student  ,                    id,name

 

 

the two databases having same structure

 

My query is

 

i want copy all data from datain1 to datain2 on daily process through php script(script will be executed manually on daily)

 

i don't want any duplicate or repeated data in datain2

 

give me some script to do this

 

kindly help me to solve this its so urgent

[trq]

Is there a particular reason you need to use PHP for this?

[sunilpaladugu]

I have to use only PHP here because

actually i have to integrate 4 existing databases in to central server

has already these 4 databases are developed with mysql and PHP and mostly my client requirement also using PHP only 

 

[trq]

That doesn't really explain anything.

[onlyican]

PHP to run a MySQL query along the lines of

 

INSERT INTO tbl2 (SELECT * FROM tbl1)

[codefossa]

Instead of copying it everyday, why don't you just have the scripts inserting the data insert to both databases?  I don't see the point of doing one, then copying it when they're going to come out identical.

[sunilpaladugu]

The two databases resides on two different locations(hosts) i dont't have  the 24/7  internet connection b/w them i have only 2-3 hours of a day i can connect.When I get connection i have to run the PHP script manually to INSERT and UPDATE from one host to another host. 

[litebearer]

Is server A supposed to be an exact duplicate of server B when you have finished inserting/updating the data FROM server A to server B?

[sunilpaladugu]

Yes

The database structure and tables of the both databases are same just i need to make the exact copy of local database at server

[litebearer]

Might consider EXPORT A, upload the export, truncate B, IMPORT the data created by A (Yes, its a 'sledge-hammer' approach)

[sunilpaladugu]

But here the hosts are different

If I export local Db(A) and import it to server(B),Needs some to do in sql file for import(ex:changing host address)

I have to do this task to my client,he doesn't have any Knowledge on DB.

 

Here I did some script work could plz look in

Html File:

<form name="in" action="code.php">

Year<select name="da">

<option value="2011">2011</option>

<option value="2012">2012</option>

</select>

Month

<select name="mon">

<option value="01">01</option>

<option value="02">02</option>

<option value="03">03</option>

</select>

Day

<select name="day">

<option value="23">23</option>

<option value="24">24</option>

<option value="25">25</option>

<option value="26">26</option>

 

</select>

 

<input type="submit" name="insert" />

 

</form>

My Php File:

<?php

$dbhost1='localhost';

$dbuser1='root';$dbpw1='';

$dbname1='datain1';$dbname2='datain2';

$resource1 = mysql_connect($dbhost1, $dbuser1, $dbpw1);

$db1 = mysql_select_db($dbname1, $resource1)

or die ("Couldn't select database.");

$resource2 = mysql_connect($dbhost1,$dbuser1, $dbpw1);

$db = mysql_select_db($dbname2, $resource2)

or die ("Couldn't select database.");

$year=$_GET['da'];

$month=$_GET['mon'];

$day=$_GET['day'];

$selddate=$year.'-'.$month.'-'.$day;

echo 'Selected Date:'.$selddate.'<br>';

$curdate=date("Y-m-d");

echo 'Current Date:'.$curdate;

if($selddate>$curdate)

{

echo "date exceeds";

}

$query_newsfeed = "SELECT id,name FROM datain1.student where insert_date='$selddate' ";

$query_newsfeed1 = "SELECT id,name FROM datain1.student1 where indate='$selddate' ";

$query1_exec = mysql_query ( $query_newsfeed ) or die (mysql_error());

$query1_exec1 = mysql_query ( $query_newsfeed1 ) or die (mysql_error());

$num=mysql_num_rows($query1_exec);

$num1=mysql_num_rows($query1_exec1);

 

echo '<br><b>'.$num.'</b><br>';

echo '<b>'.$num1.'</b><br>';

if (mysql_num_rows($query1_exec) == 0 && mysql_num_rows($query1_exec1) == 0)

{

echo "No rows found, cannot do anything";

exit;

}

//if (mysql_num_rows($query1_exec1) == 0)

//{

//echo "No rows found, cannot do anything..........";

//exit;

//}

 

while ( $query_row = mysql_fetch_assoc ( $query1_exec ) )

{

//$query_row[id]=addslashes($query_row[id]);

//$query_row[name]=addslashes($query_row[name]);

$query2 = "INSERT INTO datain2.student (id,name) VALUES ($query_row[id],'$query_row[name]')";

//echo $query2;

$query2_exec = mysql_query($query2) or die (mysql_error());

}

while ( $query_row1 = mysql_fetch_assoc ( $query1_exec1 ) )

{

//$query_row[id]=addslashes($query_row[id]);

//$query_row[name]=addslashes($query_row[name]);

$query3 = "INSERT INTO datain2.student1 (id,name) VALUES ($query_row1[id],'$query_row1[name]')";

//echo $query2;

$query2_exec1 = mysql_query($query3) or die (mysql_error());

}

?>   

 

Here i took two databases datain1(local DB) and datain2(server DB)

In datain1 i have two tables student and studnet1

 

In datain2 i have two tables student and studnet1

 

While insert in to datain1 table i have considerd insert_date(date of insert) and update1(date of update)

 

By the insert_date I am copying the local DB(datain1) data into server DB(datain2)

 

It works very fine with Insert

 

But I need it for update  also

 

If any updates happening in Local DB(datain1) that should be update the same in server(datain2)

 

hope I am clear about my Issue

 

Please help me to solve this

 

 

 

 

 

 

[litebearer]

psuedo code...

SERVER A small script (client does nothing)

  connect to db

  query select all from table

  loop through results saveingdata to cvs file (new_data.txt)

  end loop

 

SERVER B small script (you or client points browser to this script)

  file_get_contents(url to server A new_data.txt)

  file_put_contents into local file

  $new_data = file("new_data.txt")

  connect to db

  truncate table

  loop through new_data.txt

    explode each line into an array

    insert each element into proper table field

  end loop

  delete (unlink) new_data.txt

 

[joshbedo]

Do you just need a backup? If so I would lookup sql backup routine. My brother does this every night on another hard drive so if anything happens to a server.

[sunilpaladugu]

yes I need to implement back up strategies also for my DB.it could be possible at every evening as automatically 

[litebearer]

If I am understanding you correctly, these two scripts should accomplish your goals. The first script goes on your server, the second goes on the client's server.

 

You can create CRON's  to run them. CRON 1 is on your server and is set to run script 1 at, oh let's say 9PM GMT. Then CRON 2 is on client's server and is set to run script 2 at 11PM GMT.

 

I think the scripts are pretty self-explanatory (untested, un-proof-read)...

 

script 1

<?PHP
/* script for use with datain1 - PUT THIS ON YOUR SERVER */

/* create txt file name - DO NOT CHANGE */
$data_file = "datain1_update.txt";

/* make your database connection here - PUT YOUR OWN DATABASE CONNECTION HERE*/
include('db.php');

/* create your query - DO NOT CHANGE */
$query = "SELECT * FROM student";

/* execute the query  - DO NOT CHANGE */
$result = mysql_query($query);


/* start looping through the results  - DO NOT CHANGE */

while($row = mysql_fecth_array($result)) {
$content = $content . $row['id'] . "|" . $row['name'] . "\n";
}

/* write the content to the $data_content file  - DO NOT CHANGE */
file_put_contents($date_file, $content);
?>

 

script 2

<?PHP
/* script for use with datain2 - PUT THIS ON YOUR CLIENT'S SERVER */
/* NOTE: MAKE SURE TABLE ON CLIENT SERVER IS NOT AUTO-INCREMENT */

/* create txt file name - DO NOT CHANGE */
$data_file = "datain1_update.txt";

/* make your database connection here - PUT YOUR OWN DATABASE CONNECTION HERE*/
include('db.php');

/* read the file from YOUR SERVER  */
file_get_contents("URL TO THE FILE ON YOUR SERVER");

/* write the file to CLIENT'S SERVER */
file_put_contents($date_file, $content);

/* read the file into an array - DO NOT CHNAGE */
$lines = file($data_file);


/* TRUNCATE TABLE ON CLIENT'S SERVER */
$query1 = "TRUNCATE student";
$result1 = mysql_query($query1);

/* loop through the array */
for($i=0,$i<count($lines);$i++) {
/* create an array from the current line */
$new_data = explode("|", $lines[$i];
/* store the values into variables */
$id = $new_data[0];
$name = $new_data[1];
/* create the query */
$query2 = "INSERT INTO students (id,name) VALUES('$id', '$name')";
/* execute the query */
$result2 = mysql_query($query2);
}

/* delete the data file  - DO NOT CHANGE */
unlink($data_file);
?>

[trq]

Mysql has built in methods for creating and then importing database dumps.

[sunilpaladugu]

oh this is great

 

I will try this and i let you know

 

Thanks alot

[sunilpaladugu]

hey can u plz answer my query

 

in the script(You given) after copying the data from local server(datain1) to server(datain2)....... Is the data at local server(datain1) still available or you can truncate the data of datain1

 

if yes my requirement was not this.......I need the data to be present on local server also(datain1)

 

Thanks

 

 

 

 

 

 

[litebearer]

(SCRIPT 1) we COPY the data from SERVER A to a file - we DO NOT delete any of the data

 

THEN

 

(SCRIPT 2) On SERVER B we read the datafile FROM server A; TRUNCATE the data on SERVER B; then insert the data into the table on SERVER B