PHP mysql script breaking my head

[vnlvet]

Hey everyone, I am new to php scripting and I feel somewhat retarded I am not understanding something about a script I need produced. In short I need to mirror sections of a database by moving columns from one remote database to a local one.

 

Before I post what I have already attempted, I think it would be a better use of my time to start fresh with some intelligent advice from the forum users. My hurdle is to understand what exactly I am putting in my script to do this. I have the $remote_link and $local_link already declaired and it gives me a nice 'connected successfully' echo when both connect as listed below:

 

<?php

 

echo "sync..";

 

$remote_link = mysql_connect('phpmyadmin.xxxxxxxx', 'uuuuuuu', 'wwwwwww');

 

if (!$remote_link) {

die('Could not connect: ' . mysql_error());

}

mysql_select_db('AAAAA',$remote_link);

echo 'Connected successfully REMOTE<br>';

// --------------------------------------------------------------------------

 

$local_link = mysql_connect('localhost', 'yyyyyy', 'zzzzzzzzz');

 

if (!$local_link) {

die('Could not connect: ' . mysql_error());

}

mysql_query('SET NAMES utf8',$local_link);

mysql_select_db('BBBBBBB',$local_link);

 

echo 'Connected successfully LOCAL<br>';

// ----------------------------------------------------------------------------

 

For example: I need to copy a whole column named "title" from the "product" table on 'AAAAA' database and insert it in the column "name" in the "product_desc" on 'BBBBB' database.

 

Help!

[requinix]

For example: I need to copy a whole column named "title" from the "product" table on 'AAAAA' database and insert it in the column "name" in the "product_desc" on 'BBBBB' database.

But what about the rest of the data? What "title"s match up with what "name"s? How do you know where the new values are going?

[vnlvet]

Each table has the standard "id" category that has each row a specific number. Hopefully it will keep everything in line and related to one another. There are instances where there are lets say 40 columns in one table from the remote that need to be transfered over, but they need to land in different places on the other database. I am not planning on making a perfect mirror database, the two database structures are different but they have parts that are similar and have numerous columns that need identical data in them.

 

Basicly I am looking for the standard way to move a whole column from a specific table on a remote to a specific table column on a local. I managed to screw it up each attempt I have made and it is tiresome to continuously reset the local database each attempt I make.

 

Any help will be appreciated!

[Jessica]

There is no "standard" way to do that. It's not something one normally does.

[vnlvet]

ok, is there a simple way anyone knows how to do that?

[Jessica]

This is a one-time thing right?

[Barand]

So does the title from the row with id=1 go into the name column of the row with id=1 in the second table and so on, always matching on the id?

[vnlvet]

This script is not a one time thing, it will eventually have to run once a night after the work shift is done to sync data from the database the company uses and the separate database.

 

The title with id=1 will have to line up with name id=1 on the other database table yes.

[Barand]

The query you will need is

 

UPDATE product_desc pd
INNER JOIN product p USING (id)
SET
   pd.name = p.title,
   pd.colX = p.colY

 

if the tables are in separate databases you can prefix the table names with the db names (eg AAAAA.product_desc). However, the two tables will need to be on the same server.

 

Create job to dump the table on the remote server and load the dump on to the local server.

 

Alternatively, to copy the table via csv file, you can run a "SELECT ... INTO my.csv" on one server followed by "LOAD DATA INFILE" on the second.

[vnlvet]

Thanks for the query Barand, could you break down what each part in the query means? The databases are on two separate servers. I understand to prefix of a table name like AAAAA.product_desc, but would I need to declare it as a variable in the script?

 

I apologise in advance for my newbieness. My company is very cheap and has dumped this project on me because it does not want to pay a real programmer to make this script. I know very little to nothing about php or mysql programming. I know PLC programming and ladder/ST. This land is alien to me!

[Barand]

The query is fairly straightforward. It matches records from the two table using the id columns and updates the name column of the product_description table, copying the content of the title column from the product table. It will do the same for any other columns specified (as with the colX and colY which I added as an example of how to add any other columns you want to update).

 

As for the database names, they only need to be variables if they are actually variable. I would expect them to be static in this case. Plus it depends on where you load the transferred table. If it it is loaded into the same database then the database names can be omitted.

[vnlvet]

Hey Barand,

 

Thank you for the explanation. For me to add to this a new column from a new table, coming from the same remote database and going to the same database I would do this below?

 

UPDATE product_desc pd

INNER JOIN product p USING

(

id

)

SET

pd

.

name

=

p

.

title

,

 

UPDATE product_price pd

INNER JOIN product_cost p USING

(

id

)

SET

pd

.

(name of column)

=

p

.

(name of column)

,

 

One more thing. If the remote database one day has 5000 rows (id up to 5000), and the next day it has 6000 rows (id up to 6000), is there a way to copy the id column over first to the local server so the new 1000 rows of data are copied? or will it create the new rows automaticly?