1054 Unknown column 'ci.customers_info_id' in 'on clause'

[alyxsmom]

I have literally spent all day & numerous database versions (each time I've copied the latest database to a new version so I'm always working on a copy, so thankfully haven't touched any live data) attempting to import a user database from a different cms (e107) into Zen. I have read the forums here and there and elsewhere to come up with a method of getting what limited data I have from the e107 database into the new zen database.

When I browse the database through phpmyadmin, I can see the fields I have data for (basically customers_id/addressbook_id/info_id all match and are in each of those seperate tables as well; nickname and password) and all of the automatic fields (null, etc). I do not have data for address, gender, phone, etc.

So, when I try to login and/or retrieve a customer password it tells me that email address doesn't exist. When I go into the admin screen and click customers I get this error:

1054 Unknown column 'ci.customers_info_id' in 'on clause'
in:
[select count(*) as total from zen_customers c left join zen_customers_info ci on c.customers_id= ci.customers_info_id left join zen_address_book a on c.customers_id = a.customers_id and c.customers_default_address_id = a.address_book_id ]
If you were entering information, press the BACK button in your browser and re-check the information you had entered to be sure you left no blank fields.

So, based on a post here, I popped that into the dev toolkit and get this:

/var/www/html/store/admin/customers.php

Line #985 : $customers_query_raw = "select c.customers_id, c.customers_lastname, c.customers_firstname, c.customers_email_address, c.customers_group_pricing, a.entry_country_id, a.entry_company, ci.customers_info_date_of_last_logon, ci.customers_info_date_account_created " . $new_fields . " from " . TABLE_CUSTOMERS . " c left join " . TABLE_CUSTOMERS_INFO . " ci on c.customers_id= ci.customers_info_id left join " . TABLE_ADDRESS_BOOK . " a on c.customers_id = a.customers_id and c.customers_default_address_id = a.address_book_id " . $search . " order by $disp_order";

/var/www/html/store/admin/includes/modules/newsletters/product_notification.php

Line #212 : where c.customers_id = ci.customers_info_id

Line #242 : where c.customers_id = ci.customers_info_id


For what its worth, I'm running MySQL 4.1.12, Zen Cart 1.3.0.2, Database Patch Level: 1.3.0.2

In all honesty, I really don't know what to do with this information. Its late and I'm tired and braindead, so sorry if this doesn't make much sense. I give up for tonight. Anyone have some insight?

TIA~
~Vicki

[alyxsmom]

I fixed this. Here's what I did in case you are interested.

Problem 1:

I did another compare (Beyond Compare) of the live store database and the new attempt at importing the user database. This time, I included structure, add drop table, add auto_increment, full data inserts and selected all tables in one export (one export for each database) so I could see the structure and data of the entire database in one file. This time, I saw a couple of major difference in the files.

1. The live database listed out all of the customers information, row after row. The new database did not show any data in the fields.
2. The live database had information in the zen_address_format table whereas the new database did not because I did not include that table in the loading of data. I didn't include it because I never saw it posted anywhere that it was important in the few discussions of relevant customer tables that I could find. So, here is a list of the tables I used to import the customer info into:

zen_address_book
zen_address_format
zen_customers
zen_customers_info


Problem 2:
Since I could now see how the structure and data actually related to one another, I realized I needed to take a closer look at the database I wanted to pull information from. Upon closer investigation of the imported data, I noticed the data from the e107 database csv file was really mixed up. Due to unverified users not appearing in the list and/or if a user was deleted at some point, the database filled the 'holes' in the sequence with the next available verified member ( ie: user 235 doesn't exist so fill that hole with user 1148). Also, some members had signature files with commas in the string. The CSV export used those as seperators and filled the next column with the rest of the sentence, so the rest of that members data was in the wrong column field.

So, I did a manual cleanup of the data in a spreadsheet.
1. Deleting the signatures and fixing the rest of the row.
2. Re-ordering the list in correct increments by moving out of sequence rows and inserting rows where there were holes (added in increment # and put '' - 2 single quotes, using the click and drag to fill function - in all the empty columns).

Then, I copied columns of data over from the e107 spreadsheet into each respective zen table spreadsheet, filling any columns I didn't have data for with '' (2 single quotes, using the click and drag to fill function).

A couple of notes on this
1. I assumed the database would 'prefer' to have some information filled in that the customer would not be able to update themselves, ie: account creation date/time so I filled in that info (click and drag to fill in the spreadsheet) with the date/time that I was importing the data.

2. I didn't know what to put in the entry zone field in the zen_address_book table, so I just put a 1 in it.

SIDE NOTE: I'm not sure if either one of these was truly important, but it didn't break it LOL.


I then imported all the newly formatted data into the test database and it worked.


Since I couldn't find any instructions detailing how to import a wildly different user database into zen cart, I kept detailed notes of what I did. I did this so I would know what worked and what didn't in case I ever needed to do it again. I can post this information in a seperate post if you would like.