PHP SQL - Mysql data copy HELP

Author

Mysql data copy HELP

Dejan

2006-04-08, 3:58 am

Hy

Sorry for my terreble english.
I have mysql table1 on my local comp. It looks something like this.

----------------------------------------------------------------------------
-
broj_tel | poruka | sifra |
grad | id |
----------------------------------------------------------------------------
|
+385912701600 0 Zprod rijeka
5 |
----------------------------------------------------------------------------
|
+385912701699 1 kprod crikvenica
3 |
----------------------------------------------------------------------------
-

On the remote server (web hosting) i have table2 looking something like
this:

----------------------------------------------------------------------------
---------
id_independant | Name | Surname | broj_tel
| poruka |
----------------------------------------------------------------------------
--------- |
11122 Martin Johnes
+385912701600 1 |
----------------------------------------------------------------------------
----------|
16163 John Doe
+385912701699 0 |
----------------------------------------------------------------------------
----------

Question: I nead periodicly (some scheduler, my concern) to make something
like this:

select * from table1
insert into table2 (poruka) whare table1.broj_tel = table2.broj_tel

To read the data from table1, and inserts into table2 coloumn poruka whare
tabel1.broj_tel i equal table2.broj_tel
Problem is that the data is in diferent databases.

Any ideas?
Tnx in advance
Dejan

Mike Youell

2006-04-08, 7:59 am

Hi Dejan,

Try this:

HOST SCRIPT
1) create a script called get_insert_keys.php which does a "select
broj_tel from table2" and outputs all the values using the php echo
function.

LOCAL SCRIPT
1) create a script on your local machine which does a
file_get_contents("http://www.yourdomain.com/cgi-bin/get_insert_keys.php?username=fred&password=blahblah");
and reads the broj_tel values into an array. (one-way encrypt password
before sending like this!)

2) Now you have a list of broj_tel's from table 2 (but on your local
machine). Now create a CSV file of all the fields in table1 that have
a broj_tel from table2 (see the array for broj_tel's from table2).

3) Now use the php ftp functions to copy this CSV data file to your
host to a designated directory, e.g. /data/ or whatever.

4) Then use
file_get_contents("http://www.yourdomain.com/cgi-bin/insert_values_from_table1.php?username=fred&password=blahblah");
to call a script on the host to insert the values using the new CSV
datafile.

This may seem complicated (it's actually quite easy to implement) but
at least it can be automated easily without the use of phpmyadmin etc.
You could also gzip the data before sending.

Note that you run the local script to trigger everything off. It is
that script which drives the whole thing.

There's probably a much easier way to do this. If this is the case
then someone please tell me too :o)

Cheers,
Mike Youell.

Jerry Stuckle

2006-04-08, 6:59 pm

Dejan wrote:
> Hy
>
> Sorry for my terreble english.
> I have mysql table1 on my local comp. It looks something like this.
>
>
> ----------------------------------------------------------------------------
> -
> broj_tel | poruka | sifra |
> grad | id |
> ----------------------------------------------------------------------------
> |
> +385912701600 0 Zprod rijeka
> 5 |
> ----------------------------------------------------------------------------
> |
> +385912701699 1 kprod crikvenica
> 3 |
> ----------------------------------------------------------------------------
> -
>
>
> On the remote server (web hosting) i have table2 looking something like
> this:
>
> ----------------------------------------------------------------------------
> ---------
> id_independant | Name | Surname | broj_tel
> | poruka |
> ----------------------------------------------------------------------------
> --------- |
> 11122 Martin Johnes
> +385912701600 1 |
> ----------------------------------------------------------------------------
> ----------|
> 16163 John Doe
> +385912701699 0 |
> ----------------------------------------------------------------------------
> ----------
>
> Question: I nead periodicly (some scheduler, my concern) to make something
> like this:
>
> select * from table1
> insert into table2 (poruka) whare table1.broj_tel = table2.broj_tel
>
> To read the data from table1, and inserts into table2 coloumn poruka whare
> tabel1.broj_tel i equal table2.broj_tel
> Problem is that the data is in diferent databases.
>
> Any ideas?
> Tnx in advance
> Dejan
>
>
>

This isn't a PHP question. I'd suggest you try a MySQL group - such as
comp.databases.mysql.

Depending on your version you might be able to do something with replication.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Jiri Fogl

2006-04-09, 3:59 am

Create a small PHP script, which connects to first database, reads data
and store them in some kind of object or array (as you like), closes
connection, then connects to the other database and write data.

Place this script to your local webserver.

If you use Linux, then you need to add a record to your Cron to run
periodically this command:
wget http://localhost/my_sync_script.php > /dev/null

If you use Windows, you will use Scheduled tasks instead of Cron, and
you need to find something like wget. I think there is wget for Windows,
or try running your browser with parameter to open
http://localhost/my_sync_script.php

Dejan wrote:
> Hy
>
> Sorry for my terreble english.
> I have mysql table1 on my local comp. It looks something like this.
>
>
> ----------------------------------------------------------------------------
> -
> broj_tel | poruka | sifra |
> grad | id |
> ----------------------------------------------------------------------------
> |
> +385912701600 0 Zprod rijeka
> 5 |
> ----------------------------------------------------------------------------
> |
> +385912701699 1 kprod crikvenica
> 3 |
> ----------------------------------------------------------------------------
> -
>
>
> On the remote server (web hosting) i have table2 looking something like
> this:
>
> ----------------------------------------------------------------------------
> ---------
> id_independant | Name | Surname | broj_tel
> | poruka |
> ----------------------------------------------------------------------------
> --------- |
> 11122 Martin Johnes
> +385912701600 1 |
> ----------------------------------------------------------------------------
> ----------|
> 16163 John Doe
> +385912701699 0 |
> ----------------------------------------------------------------------------
> ----------
>
> Question: I nead periodicly (some scheduler, my concern) to make something
> like this:
>
> select * from table1
> insert into table2 (poruka) whare table1.broj_tel = table2.broj_tel
>
> To read the data from table1, and inserts into table2 coloumn poruka whare
> tabel1.broj_tel i equal table2.broj_tel
> Problem is that the data is in diferent databases.
>
> Any ideas?
> Tnx in advance
> Dejan
>
>
>

Dejan

2006-04-10, 4:01 am

Too hard.
I'm gonna think abaout it, any try it.
tnx on advice.

Mike Youell <mike@youell.net> wrote in message
news:1144504342.911875.17750@e56g2000cwe.googlegroups.com...
>
> Hi Dejan,
>
> Try this:
>
> HOST SCRIPT
> 1) create a script called get_insert_keys.php which does a "select
> broj_tel from table2" and outputs all the values using the php echo
> function.
>
> LOCAL SCRIPT
> 1) create a script on your local machine which does a
>
file_get_contents("http://www.yourdomain.com/cgi-bin/get_insert_keys.php?use
rname=fred&password=blahblah");
> and reads the broj_tel values into an array. (one-way encrypt password
> before sending like this!)
>
> 2) Now you have a list of broj_tel's from table 2 (but on your local
> machine). Now create a CSV file of all the fields in table1 that have
> a broj_tel from table2 (see the array for broj_tel's from table2).
>
> 3) Now use the php ftp functions to copy this CSV data file to your
> host to a designated directory, e.g. /data/ or whatever.
>
> 4) Then use
>
file_get_contents("http://www.yourdomain.com/cgi-bin/insert_values_from_tabl
e1.php?username=fred&password=blahblah");
> to call a script on the host to insert the values using the new CSV
> datafile.
>
> This may seem complicated (it's actually quite easy to implement) but
> at least it can be automated easily without the use of phpmyadmin etc.
> You could also gzip the data before sending.
>
> Note that you run the local script to trigger everything off. It is
> that script which drives the whole thing.
>
> There's probably a much easier way to do this. If this is the case
> then someone please tell me too :o)
>
> Cheers,
> Mike Youell.
>

Sponsored Links