Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.php > #2457 > unrolled thread

Copying data between databases

Back to article view | Back to comp.lang.php

Contents

  Copying data between databases "Sloan" <sloan@sloansweb.com> - 2011-07-06 23:41 -0500
    Re: Copying data between databases Mick Gurling <nospam@myemail.net> - 2011-07-07 04:51 +0000
      Re: Copying data between databases "Sloan" <sloan@sloansweb.com> - 2011-07-07 00:00 -0500
        Re: Copying data between databases The Natural Philosopher <tnp@invalid.invalid> - 2011-07-07 07:09 +0100
    Re: Copying data between databases The Natural Philosopher <tnp@invalid.invalid> - 2011-07-07 05:56 +0100
    Re: Copying data between databases "Álvaro G. Vicario" <alvaro.NOSPAMTHANX@demogracia.com.invalid> - 2011-07-07 08:28 +0200

#2457 — Copying data between databases

Hi all,

I'm a bit new to PHP, but have been programming in other languages for quite 
a while. I'm pretty good a SQL, including MySQL.

Here's my question:

How do I connect to two databases (on the same server) to use an INSERT .... 
ON DUPLICATE KEY UPDATE in PHP were the source table in one database, and 
the destination table is in another?

I've written the SQL statement with explicit db.table.field references so I 
think I can use mysql_query, but which link do I use, or how can I pass both 
links?

Thanks in advance!

Sloan
 

[toc] | [next] | [standalone]

#2458

http://www.tonymarston.net/php-mysql/databaseobjects.html

Use his objects or read how he uses the database connections.

Mick

On Wed, 06 Jul 2011 23:41:57 -0500, Sloan wrote:

> Hi all,
> 
> I'm a bit new to PHP, but have been programming in other languages for
> quite a while. I'm pretty good a SQL, including MySQL.
> 
> Here's my question:
> 
> How do I connect to two databases (on the same server) to use an INSERT
> .... ON DUPLICATE KEY UPDATE in PHP were the source table in one
> database, and the destination table is in another?
> 
> I've written the SQL statement with explicit db.table.field references
> so I think I can use mysql_query, but which link do I use, or how can I
> pass both links?
> 
> Thanks in advance!
> 
> Sloan

[toc] | [prev] | [next] | [standalone]

#2460

Hi Mick,

Thanks for a quick answer. I read a bit of his site, but it seems to be a 
bit of overkill for what I need. I need to setup a cron task to update data 
in a second DB, with some modifications, and set it up as a cron task. 
Here's an example of the kind of SQL statement I'm talking about:

INSERT INTO `some_db_jml62`.`jos_jshopping_products_to_categories`
	(
		`product_id`, `category_id`, `product_ordering`

	)
	SELECT `product_id`, `category_id`, `product_ordering`
	FROM `some_db_jml61`.`jos_jshopping_products_to_categories`
	ON DUPLICATE KEY UPDATE
		SET
			`some_db_jml62`.`jos_jshopping_products_to_categories`.`product_id` = 
`some_db_jml61`.`product_id`.`product_id`,
			`some_db_jml62`.`jos_jshopping_products_to_categories`.`category_id` = 
`some_db_jml61`.`product_id`.`category_id`,
			`some_db_jml62`.`jos_jshopping_products_to_categories`.`product_ordering` 
= `some_db_jml61`.`product_id`.`product_ordering`

Is this the right group to be asking, or is there a MySQL / PHP group?

Again, thanks for the quick reply!


"Mick Gurling" <nospam@myemail.net> wrote in message 
news:iv3e02$30v$2@dont-email.me...
> http://www.tonymarston.net/php-mysql/databaseobjects.html
>
> Use his objects or read how he uses the database connections.
>
> Mick
>
> On Wed, 06 Jul 2011 23:41:57 -0500, Sloan wrote:
>
>> Hi all,
>>
>> I'm a bit new to PHP, but have been programming in other languages for
>> quite a while. I'm pretty good a SQL, including MySQL.
>>
>> Here's my question:
>>
>> How do I connect to two databases (on the same server) to use an INSERT
>> .... ON DUPLICATE KEY UPDATE in PHP were the source table in one
>> database, and the destination table is in another?
>>
>> I've written the SQL statement with explicit db.table.field references
>> so I think I can use mysql_query, but which link do I use, or how can I
>> pass both links?
>>
>> Thanks in advance!
>>
>> Sloan
>
> 

[toc] | [prev] | [next] | [standalone]

#2461

Sloan wrote:
> Hi Mick,
> 
> Thanks for a quick answer. I read a bit of his site, but it seems to be 
> a bit of overkill for what I need. I need to setup a cron task to update 
> data in a second DB, with some modifications, and set it up as a cron 
> task. Here's an example of the kind of SQL statement I'm talking about:
> 
> INSERT INTO `some_db_jml62`.`jos_jshopping_products_to_categories`
>     (
>         `product_id`, `category_id`, `product_ordering`
> 
>     )
>     SELECT `product_id`, `category_id`, `product_ordering`
>     FROM `some_db_jml61`.`jos_jshopping_products_to_categories`
>     ON DUPLICATE KEY UPDATE
>         SET
>             
> `some_db_jml62`.`jos_jshopping_products_to_categories`.`product_id` = 
> `some_db_jml61`.`product_id`.`product_id`,
>             
> `some_db_jml62`.`jos_jshopping_products_to_categories`.`category_id` = 
> `some_db_jml61`.`product_id`.`category_id`,
>             
> `some_db_jml62`.`jos_jshopping_products_to_categories`.`product_ordering` 
> = `some_db_jml61`.`product_id`.`product_ordering`
> 
> Is this the right group to be asking, or is there a MySQL / PHP group?
> 

comp.databases.mysql

That sort of approach should work if the databases are on the same server.

but not if they are on separate ones.

Which I assumed was the case.


> Again, thanks for the quick reply!
> 
> 
> "Mick Gurling" <nospam@myemail.net> wrote in message 
> news:iv3e02$30v$2@dont-email.me...
>> http://www.tonymarston.net/php-mysql/databaseobjects.html
>>
>> Use his objects or read how he uses the database connections.
>>
>> Mick
>>
>> On Wed, 06 Jul 2011 23:41:57 -0500, Sloan wrote:
>>
>>> Hi all,
>>>
>>> I'm a bit new to PHP, but have been programming in other languages for
>>> quite a while. I'm pretty good a SQL, including MySQL.
>>>
>>> Here's my question:
>>>
>>> How do I connect to two databases (on the same server) to use an INSERT
>>> .... ON DUPLICATE KEY UPDATE in PHP were the source table in one
>>> database, and the destination table is in another?
>>>
>>> I've written the SQL statement with explicit db.table.field references
>>> so I think I can use mysql_query, but which link do I use, or how can I
>>> pass both links?
>>>
>>> Thanks in advance!
>>>
>>> Sloan
>>
>>

[toc] | [prev] | [next] | [standalone]

#2459

Sloan wrote:
> Hi all,
> 
> I'm a bit new to PHP, but have been programming in other languages for 
> quite a while. I'm pretty good a SQL, including MySQL.
> 
> Here's my question:
> 
> How do I connect to two databases (on the same server) to use an INSERT 
> .... ON DUPLICATE KEY UPDATE in PHP were the source table in one 
> database, and the destination table is in another?
> 
> I've written the SQL statement with explicit db.table.field references 
> so I think I can use mysql_query, but which link do I use, or how can I 
> pass both links?
> 
> Thanks in advance!
> 
> Sloan
> 
> 
open the one database get the data,. open the other, insert the data.

you can have two database handles open together, you know.

i.e
$link = mysql_connect($locale_remote_database, 'web-user', '2123utr789');
$link2 = mysql_connect($locale_database, 'other-web-user', 'abc0987');

Just use the different handles in the select and insert statements.

[toc] | [prev] | [next] | [standalone]

#2462

El 07/07/2011 6:41, Sloan escribió/wrote:
> I'm a bit new to PHP, but have been programming in other languages for
> quite a while. I'm pretty good a SQL, including MySQL.
>
> Here's my question:
>
> How do I connect to two databases (on the same server) to use an INSERT
> .... ON DUPLICATE KEY UPDATE in PHP were the source table in one
> database, and the destination table is in another?
>
> I've written the SQL statement with explicit db.table.field references
> so I think I can use mysql_query, but which link do I use, or how can I
> pass both links?

If they are on the same server, you don't need to open two links: you 
just need a MySQL user that has the appropriate permissions.

The MySQL syntax would be something like this:

INSERT INTO target (foo, bar)
SELECT foo, bar
FROM source
WHERE id=314

And you are not restricted to mysql_query(), you have three or four 
database libraries that can talk to MySQL. I've particularly found PDO 
quite handy.


-- 
-- http://alvaro.es - Álvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programación web: http://borrame.com
-- Mi web de humor satinado: http://www.demogracia.com
--

[toc] | [prev] | [standalone]

Back to top | Article view | comp.lang.php

csiph-web