| Copying data between databases [message #174777] |
Thu, 07 July 2011 04:41  |
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  |
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 |
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 |
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 |
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 |
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
--
|
|
|
|
Calendar
Search
Help
Members
Register
Login
Home
]