From: Michael B Allen [mailto:[EMAIL PROTECTED]
> I'd like to move some bookmarks in one database to another. The format
> of the Active PHP Bookmarks 'apb_bookmarks' table is (minorly
> trucated):
>
> +------------------+------------------+------+-----+---------+
> -----------+
> | Field | Type | Null | Key | Default
> | Extra |
> +------------------+------------------+------+-----+---------+
> -----------+
> | bookmark_id | int(11) unsigned | | PRI | NULL
> | auto_incr |
> | group_id | int(10) unsigned | | MUL | 0
> | |
> | bookmark_title | varchar(255) | | |
> | |
> | bookmark_url | varchar(255) | | |
> | |
> | bookmark_descrip | varchar(255) | YES | | NULL
> | |
> | bookmark_creatio | datetime | | | 0000-
> | |
> | bookmark_private | char(1) | | | 0
> | |
> | bookmark_last_hi | datetime | YES | | NULL
> | |
> | user_id | int(10) unsigned | | MUL | 0
> | |
> | bookmark_deleted | char(1) | | | 0
> | |
> +------------------+------------------+------+-----+---------+
> -----------+
>
> I want to move this into the online-bookmarks 'bookmark' table which
> has the format:
>
> +-------------+---------------+------+-----+---------+--------
> --------+
> | Field | Type | Null | Key | Default | Extra
> |
> +-------------+---------------+------+-----+---------+--------
> --------+
> | user | char(20) | | | |
> |
> | title | char(70) | | MUL | |
> |
> | url | char(200) | | | |
> |
> | description | char(200) | YES | | NULL |
> |
> | private | enum('0','1') | | | 0 |
> |
> | date | timestamp(14) | YES | | NULL |
> |
> | childof | int(11) | | | 0 |
> |
> | id | int(11) | | PRI | NULL |
> auto_increment |
> | deleted | enum('0','1') | | | 0 |
> |
> +-------------+---------------+------+-----+---------+--------
> --------+
>
> The only field mappings I care about are:
>
> bookmark_title -> title
> bookmark_url -> url
> bookmark_description -> description
>
> Now I know I can generate insert statements and then run that
> script on
> the target but is there a better way? The databases do not have direct
> access as each is running on localhost only.
While it's a little clunky, why not do this on the first server:
CREATE TABLE apb_bookmarks_tmp
SELECT '' AS user,
bookmark_title AS title,
bookmark_url AS url,
bookmark_description AS description,
0 AS private,
NULL AS date,
0 AS childof,
NULL AS id,
0 AS deleted
FROM apb_bookmarks;
I'm assuming that by "these are the only field mappings that I care about," you mean
that those are the only fields you want brought over. If not, then replace the NULLs
and 0s with the proper field names.
Once you've done this, all you need to do is mysqldump the table, scp it over the the
new host, and import it. If you had indices on the original table, you'll have to
manually create them on the new table -- CREATE TABLE ... SELECT FROM doesn't carry
those over.
HTH!
--
Mike Johnson
Web Developer
Smarter Living, Inc.
phone (617) 886-5539
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
