Importing address_book

[besheer]

I'm moving everything from a very old OSC version to Zen Cart. I've moved all the tables semi-successfully but I've had no luck with the address_book table. I'm no mySQL expert, but I'm pretty sure it's because of the way the primary key is different on both. Here's the table structure for the old one:

CREATE TABLE address_book (
  customers_id int(11) NOT NULL default '0',
  address_book_id int(11) NOT NULL default '1',
  entry_gender char(1) NOT NULL default '',
  entry_company varchar(32) default NULL,
  entry_firstname varchar(32) NOT NULL default '',
  entry_lastname varchar(32) NOT NULL default '',
  entry_street_address varchar(64) NOT NULL default '',
  entry_suburb varchar(32) default NULL,
  entry_postcode varchar(10) NOT NULL default '',
  entry_city varchar(32) NOT NULL default '',
  entry_state varchar(32) default NULL,
  entry_country_id int(11) NOT NULL default '0',
  entry_zone_id int(11) NOT NULL default '0',
  PRIMARY KEY  (address_book_id,customers_id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

And the new one:

CREATE TABLE address_book (
  address_book_id int(11) NOT NULL auto_increment,
  customers_id int(11) NOT NULL default '0',
  entry_gender char(1) NOT NULL default '',
  entry_company varchar(32) default NULL,
  entry_firstname varchar(32) NOT NULL default '',
  entry_lastname varchar(32) NOT NULL default '',
  entry_street_address varchar(64) NOT NULL default '',
  entry_suburb varchar(32) default NULL,
  entry_postcode varchar(10) NOT NULL default '',
  entry_city varchar(32) NOT NULL default '',
  entry_state varchar(32) default NULL,
  entry_country_id int(11) NOT NULL default '0',
  entry_zone_id int(11) NOT NULL default '0',
  PRIMARY KEY  (address_book_id),
  KEY idx_address_book_customers_id_zen (customers_id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

The primary key is different in both and the only way I could get it to import the data without errors is by changing

PRIMARY KEY (address_book_id)

to

PRIMARY KEY (address_book_id,customers_id)

and dropping and re-creating the table. But since I don't know anything about mySQL, I'm not sure how that would affect Zen Cart. And I imagine it would cause some sort of damage.

If I just try importing the data without changing the table, I get "duplicate entry" errors.

[besheer]

I found this by Ajeh in another post:

In the Zen Cart system ...

When you create an account the customer is assigned a customers_id in the customers table ...

It is auto assigned an address_book_id in the address_book table but the customers_id from the customers table is put in the record created in the address_book and the address_book_id is put in the customer table as the customers_default_address_id

Then the customers_id from the customers table is added to a new record in the customers_info

If you have totally empty customers, address_book and customers_info

The customers_id and customers_info_id and address_book_id and customers_default_address_id all match on the assumption these are clean tables meaning no auto increment has been triggered from playing, test records do not exist, etc. and there is 1 address_book entry per customer when these records are created ...

Your existing customer code means nothing to Zen Cart ... The customers_id Zen Cart uses is a unique auto assigned value ...

When importing ... you can use your existing customers code providing it is a unique numeric value but you must assure that you are assigning this value to the right fields to maintain the proper relationship

customers table:
customers_id
customers_default_address_id

address_book table:
customers_id

customers_info table:
customers_info_id

Once you have pre-populated these tables ... then the internal system will maintain these records for you with new/changed data ...

EDITED for customers_info_id to customers_id relationship

I think the secret to this mystery lies somewhere in here. I've manually deleted all extra addresses in the address book (old one allowed multiple addresses per customer).

I renamed (replace all) address_book_id to something else so it would allow the auto increment when importing the table. Then after a successful import, deleted that extra field. So now I have an address_book table, customers, customer info, and all that stuff. But when I click on a customer to see their details, I get this error message:

Warning: reset(): Passed variable is not an array or object in /home/public_html/admin/includes/classes/object_info.php on line 29

Warning: Variable passed to each() is not an array or object in /home/public_html/admin/includes/classes/object_info.php on line 30

[besheer]

In reference to this part:

you must assure that you are assigning this value to the right fields to maintain the proper relationship

customers table:
customers_id
customers_default_address_id

address_book table:
customers_id

customers_info table:
customers_info_id

Does anyone have any idea what customers_default_address_id should be? In my new table it is all 1s. I'm assuming that's what's trigerring the error. Should it be identical to customers_id? If so, can anyone tell me the command to copy that column from customers_id?

[besheer]

I've verified that this is the problem: customers_default_address_id in customers table should be identical to address_book_id in address_book table. When I click on the only customer who actually does have a customers_default_address_id of 1 and address_book_id of 1 (since it was created by auto increment, so there's only one occurence of it), I don't get that error, and the address comes up correctly.

Now I just need to find out how to change customers_default_address_id to match address_book_id for every customer. Hopefully I won't have to do this manually since I have about 1000 customers. If anyone can help I would greatly appreciate it. And I hope this helps someone with the same problem in the future.

[funkdabootiest]

BUMP!

I've recently switched from an older OSC version to ZC 1.3.7

I can't really figure out how to change customers_default_address_id to the appropriate values.

[poolprorich]

Hello,

DROP TABLE IF EXISTS `address_book`;
CREATE TABLE IF NOT EXISTS `address_book` (
  
  `customers_id` int(11) NOT NULL default '0',
  `address_book_id` int(11) NOT NULL auto_increment,
  
  `entry_gender` char(1) NOT NULL default '',
  `entry_company` varchar(32) default NULL,
  `entry_firstname` varchar(32) NOT NULL default '',
  `entry_lastname` varchar(32) NOT NULL default '',
  `entry_street_address` varchar(64) NOT NULL default '',
  `entry_suburb` varchar(32) default NULL,
  `entry_postcode` varchar(10) NOT NULL default '',
  `entry_city` varchar(32) NOT NULL default '',
  `entry_state` varchar(32) default NULL,
  `entry_country_id` int(11) NOT NULL default '0',
  `entry_zone_id` int(11) NOT NULL default '0',
  PRIMARY KEY  (`address_book_id`),
  KEY `idx_address_book_customers_id_zen` (`customers_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

Notice ive changed customer_id and address_book_id around as this is how your backup file will input data

INSERT INTO `address_book` 
(`customers_id`, `address_book_id`,
 `entry_gender`, `entry_company`, 
`entry_firstname`, `entry_lastname`, 
`entry_street_address`, `entry_suburb`, 
`entry_postcode`, `entry_city`, `entry_state`, 
`entry_country_id`, `entry_zone_id`) 
VALUES(All your Data In order of INSERT INTO)

as far as primary key : KEY `idx_address_book_customers_id_zen` (`customers_id`) is not a primary it is an index key, the only primary key is PRIMARY KEY (`address_book_id`),

MySQL has no clue where to find this record. It doesn't even know that if it does find one matching, that there will not be another matching one, so it has to look through the entire table, potentially thousands of records, An index is a separate file that is sorted, and contains only the field/s you're interested in sorting on. If you create an index on employee_number, MySQL can find the corresponding record very quickly (Indexes work in very similar ways to an index in a book. Imagine paging through a technical book (or more often, an scrambled pile of notes!) looking for the topic "Optimizing MySQL". An index saves you an immense amount of time!

Hope this helps