SQL Query

Hi

Would anyone know how to correctly query the tables zen_address_book and zen_customers to retrieve the following information -

entry_company
entry_firstname
entry_lastname
entry_suburb
entry_city
entry_state
entry_postcode
entry_country

and

customers_email_address
customers_telephone

The reason for this, is I need to be able to download my customers via a csv file for my accounts package, as I have to input each customers manually, and this is the only way I can think of doing it. I can migrate my customers list into my accounts package from the csv file.

Any help would be appreciated.

Thanks,

Phil

You can use phpmyadmin to grab these as a csv and concatenate them in Excel or if allowed use Excel and ODBC to grab them

Hi Kobra

Thanks for the reply, I know how to export them from phpmyadmin, but not much on the rest of what you said. Can you give any examples of concatenate in csv or excel and ODBC? What is ODBC?

I would still prefer the SQL Query if it was available.

Thanks,

Phil

Doesn't your computer have Google access??

http://www.mysql.com/products/connector/odbc/
http://msdn.microsoft.com/en-us/library/ms710252%28VS.85%29.aspx

You use this, again if your host allows it, and build you own query

Hi Kobra

Thanks for the reply, yes I have access to the internet.

But what you have supplied me with still doesn't make a lot of sense, is this something I have to install on a server? I am on a hosting package.

I am simply after a sql query for the above answer, I don't really want to have to install stuff here and there to get it. If you have a suggestion on how I might get it without being too technical then I may be able to understand it, but otherwise I am not a techie so I don't understand the answers you are giving.

I appreciate you help, but I just need easy stuff to deal with.

Thanks,

Phil

I am not a techie
Well before you get into this and have to learn the hard way.....

ALWAYS - backup your DB before attempting ANY non standard access/manipulation.

This will save you much greif....

If using Excel - you can create an ODBC link to your DB to extract data without having to load anything additional to your server....Your host just has to allow "Remote" accesses that you setup.

You save this Excel sheet and you can refresh it's data anytime you need it updated.
Only one step once sheet is created....

You could load this in myphpadmin sql and after running export to csv file.

SELECT `address_book`.`entry_street_address`, 
       `address_book`.`entry_suburb`, 
       `address_book`.`entry_postcode`, 
       `address_book`.`entry_city`, 
       `address_book`.`entry_state`, 
       `customers`.`customers_firstname`, 
       `customers`.`customers_lastname`, 
       `customers`.`customers_email_address`, 
       `customers`.`customers_telephone`
FROM `address_book` `address_book`
      INNER JOIN `customers` `customers` ON 
     (`customers`.`customers_id` = `address_book`.`customers_id`)

Skip

Hi Guys

Thanks very much for the replies, Kobra I have not had time to have a look at your suggestion yet, but I will, thank you.

I was looking at what I needed to achieve again and I came up with a new sql query that I need, this is the one that I need changing -

SELECT zen_orders.customers_name, zen_orders.customers_company, zen_address_book.entry_firstname, zen_address_book.entry_lastname, zen_address_book.entry_street_address, zen_address_book.entry_suburb, zen_address_book.entry_city, zen_address_book.entry_state, zen_address_book.entry_postcode, zen_address_book.entry_country_id, zen_orders.customers_telephone, zen_orders.customers_email_address
FROM zen_address_book zen_address_book
INNER JOIN zen_orders zen_orders ON
(zen_address_book.customers_id = zen_orders.customers_id)

This sql query works, but where a customer has entered a second and third address in their zen-cart, this query takes the additional addresses and places them into the results of the query because they have the same customer_id as the main customer and therefore are not needed. So, if it is possible to change the above somehow that it ignors the additional names and addresses of the main customer then that would be great.

I am so close to the result I am after, that I am hoping so much that someone can help.

Much appreciate everyones' help.

Thanks,

Phil

You are using orders so why not:

SELECT DISTINCT 
  `orders`.`customers_id`,
  `orders`.`customers_name`,
  `orders`.`customers_company`,
  `orders`.`customers_street_address`,
  `orders`.`customers_suburb`,
  `orders`.`customers_city`,
  `orders`.`customers_postcode`,
  `orders`.`customers_state`,
  `orders`.`customers_country`,
  `orders`.`customers_telephone`,
  `orders`.`customers_email_address`
FROM
  `orders`

Hi

Because there is a field in the accounts packgage I want to import to that requires the first name and second name as well as the other information, otherwise I would have done just what you have said. Is it possible to do what I have asked?

I appreciate all your help Skipwater.

Thanks,

Phil

You will need a combination of the below sql to achieve what you want but this will get you started.

SELECT COUNT( DISTINCT `customers`.`customers_id` ) AS distinct_names, `customers`.`customers_firstname` , `customers`.`customers_lastname` , `customers`.`customers_email_address` , `customers`.`customers_telephone` , `address_book`.`entry_street_address` , `address_book`.`entry_suburb` , `address_book`.`entry_postcode` , `address_book`.`entry_city` , `address_book`.`entry_state`
FROM `address_book` `address_book`
INNER JOIN `customers` `customers` ON ( `customers`.`customers_id` = `address_book`.`customers_id` )
GROUP BY `customers`.`customers_email_address`
HAVING distinct_names = 1

And if this is going to be daily app why not modify Export invoice totals to .csv file for Excel use for your use?

without order table

SELECT address_book.entry_company, address_book.entry_firstname, address_book.entry_lastname, address_book.entry_suburb, address_book.entry_city, address_book.entry_state, address_book.entry_postcode, address_book.entry_country_id, countries.countries_name, customers.customers_telephone, customers.customers_email_address
FROM countries INNER JOIN (address_book INNER JOIN customers ON (address_book.address_book_id = customers.customers_default_address_id) AND (address_book.customers_id = customers.customers_id)) ON countries.countries_id = address_book.entry_country_id;

with order table

SELECT orders.customers_name, orders.customers_company, address_book.entry_firstname, address_book.entry_lastname, address_book.entry_street_address, address_book.entry_suburb, address_book.entry_city, address_book.entry_state, address_book.entry_postcode, address_book.entry_country_id, orders.customers_telephone, orders.customers_email_address
FROM (address_book INNER JOIN orders ON address_book.customers_id = orders.customers_id) INNER JOIN customers ON (address_book.address_book_id = customers.customers_default_address_id) AND (address_book.customers_id = customers.customers_id);

because I'm lazzy, all is done via ms-access && that works very well in phpMyAdmin

Hi Hugo

Thanks very much for your answer it has eliminated the additional addresses from the one customer who entered them. There is one small problem, if a exsisting customer has come back another day to buy something else, then that entry also gets added to the results, so you will have the customer in one line and then again further down the list.

Would it be too much to ask if you could solve that problem too?

Thanks,

Phil

Hi

Actually don't worry about it, the accounts package takes care of duplicate entries.

Thanks,

Phil

a friendly recommendation: if you are going to be querying the database, it would be a good thing to learn mysql. it is pretty easy and you can teach it to yourself (i did). get a book and a gui interface. I use navicat (for mac). it allows you to run queries and tells you your mistakes... also has a helper to help build them (although i have never quite figured out how that works). phpmyadmin is fine if you know what you are doing but wont help you learn.

I cant tell you how valuable it has been to know how to write these myself, or to be able to find what i need in the code and edit...

"Minnie Mouse" - - Another NaviCat advocate!!!

THE BEST SOFTWARE EVER -- seriously, i learned mysql using it... !! i love it!!
i have MAMP running on my mac. then i use navicat and CODA (also amazing). wow, what a combo is all i can say. (and inexpensive too!)