SQL Query

[confused_aswell]

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

[kobra]

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

[confused_aswell]

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

[kobra]

Doesn't your computer have Google access??

http://www.mysql.com/products/connector/odbc/
http://msdn.microsoft.com/en-us/libr...52(VS.85).aspx

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

[confused_aswell]

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

[kobra]

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....

[confused_aswell]

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

[skipwater]

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?

[hugo13]

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

[confused_aswell]

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