FUDforum
Fast Uncompromising Discussions. FUDforum will get your users talking.

Home » Imported messages » comp.lang.php » Copying data between databases
Show: Today's Messages :: Polls :: Message Navigator
Switch to threaded view of this topic Create a new topic Submit Reply
Copying data between databases [message #174777] Thu, 07 July 2011 04:41 Go to next message
Sloan is currently offline  Sloan
Messages: 2
Registered: July 2011
Karma: 0
Junior Member
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
Re: Copying data between databases [message #174778 is a reply to message #174777] Thu, 07 July 2011 04:51 Go to previous messageGo to next message
Mick Gurling is currently offline  Mick Gurling
Messages: 2
Registered: June 2011
Karma: 0
Junior Member
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
Re: Copying data between databases [message #174779 is a reply to message #174777] Thu, 07 July 2011 04:56 Go to previous messageGo to next message
The Natural Philosoph is currently offline  The Natural Philosoph
Messages: 993
Registered: September 2010
Karma: 0
Senior Member
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.
Re: Copying data between databases [message #174780 is a reply to message #174778] Thu, 07 July 2011 05:00 Go to previous messageGo to next message
Sloan is currently offline  Sloan
Messages: 2
Registered: July 2011
Karma: 0
Junior Member
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(at)myemail(dot)net> wrote in message
news:iv3e02$30v$2(at)dont-email(dot)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
>
>
Re: Copying data between databases [message #174781 is a reply to message #174780] Thu, 07 July 2011 06:09 Go to previous messageGo to next message
The Natural Philosoph is currently offline  The Natural Philosoph
Messages: 993
Registered: September 2010
Karma: 0
Senior Member
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(at)myemail(dot)net> wrote in message
> news:iv3e02$30v$2(at)dont-email(dot)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
>>
>>
Re: Copying data between databases [message #174782 is a reply to message #174777] Thu, 07 July 2011 06:28 Go to previous message
alvaro.NOSPAMTHANX is currently offline  alvaro.NOSPAMTHANX
Messages: 277
Registered: September 2010
Karma: 0
Senior Member
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
--
  Switch to threaded view of this topic Create a new topic Submit Reply
Previous Topic: multi-page form
Next Topic: Earn $24.00 per Sign-Up!...Get Paided on all FREE MEMBERS!....{Sign-Up FREE}
Goto Forum:
  

-=] Back to Top [=-
[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sun Nov 10 07:18:07 GMT 2024

Total time taken to generate the page: 0.02565 seconds