Sunday, October 24, 2010

Getting Data from Zen Cart via mySQL Queries

I have a project where I need to move data from a few Zen Cart installations to Infusionsoft setup. It's pretty easy to import data into Infusionsoft, you just have to decide what you want to import.

Zen Cart is a shopping cart that has a goal of being easy to use. While it is still on its way to that goal, many non-technical people use it and run an e-commerce site without too many problems. There is no built in way to export data out of Zen Cart and the modules available still require some customizing to get exactly what you want. An easier way would be to log in to your database via phpAdmin or some other external program that accesses your database and query the database for exactly what you need.

Here is an example query:
START AFTER THIS LINE

SELECT zen_customers.customers_firstname,
zen_customers.customers_lastname,
zen_customers.customers_email_address,
zen_customers.customers_telephone,
zen_customers.customers_fax,
zen_orders.customers_street_address,
zen_orders.customers_suburb,
zen_orders.customers_city,
zen_orders.customers_postcode,
zen_orders.customers_state,
zen_orders.customers_country,
zen_orders.date_purchased,
zen_orders_products.products_name,
zen_orders_products.products_price
FROM
zen_customers, zen_orders, zen_orders_products
WHERE zen_customers.customers_id = zen_orders.customers_id
AND
zen_orders.orders_id = zen_orders_products.orders_id;
END BEFORE THIS LINE


I will try to explain this for you.
SELECT is a kind of self explanatory. It means what you want to select, kind of like, "I want".
Then there are some words before a period. That is the table you want data from.
The words after the period is the field that contains the data. If this was excel, think of the table like the "sheet" and the field as the "column".
FROM is also pretty self explanatory. It means "where from".
WHERE might be a little tough for me to explain. Since the query is asking for data from three different tables, I have to add some conditions to the query so I don't get a large output of data that doesn't match up.
The AND is another condition included in the WHERE.

Here is another query example:
START AFTER THIS LINE

SELECT zen_customers.customers_firstname,
zen_customers.customers_lastname,
zen_orders.date_purchased,
zen_orders_products.products_name,
zen_orders_products.products_price
FROM
zen_customers, zen_orders, zen_orders_products
WHERE zen_customers.customers_id = zen_orders.customers_id
AND
zen_orders.orders_id = zen_orders_products.orders_id;
END BEFORE THIS LINE


That query will give the Customers first and last name, the date they purchased, the product they purchased and the products price.
Since the data is coming from 3 different tables, there has to be something in common that is setup by the WHERE & AND lines.

I wanted to write about this so I could remember how to do it and help anyone else that was looking to do something similar.
Post a Comment