Copy added info from one mysql table to another in PHP

Question

I have two tables in different mysql databases.

I would like to copy from table A to table B. Only one way. I need to read last datetime from table B and then check if there any data added to table A after this readed datetime. If there is some data added, then copy it.

I tried this:

It writes one row if I refresh page, but I need it to write everything in one load!

do
   {

#TABLE A    
$querylastA = "SELECT * FROM `stock` ORDER BY `jrk` DESC LIMIT 1";
$resultlastA = mysql_query($querylastA) or die(mysql_error());
while($rows=mysql_fetch_array($resultlastA)){
$lastcodeA = $rows['datetime'];
}

#TABLE B
$querylastB = "SELECT * FROM `stockcopy` ORDER BY `jrk` DESC LIMIT 1";
$resultlastB = mysql_query($querylastB) or die(mysql_error());
while($rows=mysql_fetch_array($resultlastB)){
$lastcodeB = $rows['datetime'];
}

#TABLE A - NEXT DATE AFTER LAST DATE IN TABLE B
$querynextA = "SELECT datetime FROM stock WHERE datetime > '$lastcodeB' ORDER BY datetime ASC LIMIT 1";
$resultnextA = mysql_query($querynextA) or die(mysql_error());
while($rows=mysql_fetch_array($resultnextA)){
$nextcodeA = $rows['datetime'];
}


   mysql_query("INSERT INTO stockcopy(datetime, data1, data2) SELECT datetime, data1, data2 FROM stock WHERE datetime = '$nextcodeA'");
   echo "Date from table A " . $lastcodeA . "<br>";
   echo "Date from table B " . $lastcodeB . "<br>";
   }
 while ('$lastcodeA' == '$lastcodeB');

You can use a foreach loop to retrieve info from you first table to the second. Good luck — samayo, Jan 28 '13 at 12:11

score 1 · Accepted Answer · edited May 23 '17 at 12:05

1

You can insert data on a table using a SELECT statement, so all you need to do is put the condition in the SELECT.

For instance:

INSERT INTO table_example(foo, bar)
SELECT foo, bar FROM table_example2
WHERE time_condition > {SOME DATE}

This will insert into table_example the rows that the SELECT statement returns, which are the ones that satisfy the time condition. You will need to replace {SOME DATE} with an actual date (without brackets).

Also, please see: INSERT with SELECT

And also: Select columns across different databases

edited May 23 '17 at 12:05

Community

1
1

answered Jan 28 '13 at 12:16

Telmo Marques

5,066
1
24
34

I need to read from one database and write to another, how I will manage DB connections? And also what happens if there are two inputs after my datetime? – Jal_ Jan 28 '13 at 12:38
1

INSERT INTO db2.table_example(foo, bar) SELECT foo, bar FROM db1.table_example2 WHERE time_condition > {SOME DATE} – symcbean Jan 28 '13 at 13:03
@Jal_, are you doing this with PHP, or directly in a MySQL console? Also, what do you mean by "two inputs after my datetime"? – Telmo Marques Jan 28 '13 at 14:15

score 0 · Answer 2 · answered Jan 28 '13 at 12:15

You can do this with little to no knowledge of SQL, but with a bit of creativity :)

Create a temporary table (table_c), and copy the contents of table_a there.

Then manually (this means by running delete queries in phpmyadmim) delete the information that doesn't matter anymore ( DELETE FROM table_c WHERE id < 1000 for example ).

Then, export the info from table_c and import it into table_b (and delete table_c

score 0 · Answer 3 · answered Jan 29 '13 at 07:29

0

Problem was in: while ('$lastcodeA' == '$lastcodeB');

Changed it to: while ($lastcodeA != $lastcodeB); now it works! Thank you all :)

answered Jan 29 '13 at 07:29

Jal_

3
2

Copy added info from one mysql table to another in PHP

3 Answers3