-
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
-
Re: SQL Query
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
-
Re: SQL Query
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
-
Re: SQL Query
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
-
Re: SQL 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
-
Re: SQL Query
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....
-
Re: SQL Query
You could load this in myphpadmin sql and after running export to csv file.
Code:
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
-
Re: SQL Query
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
-
Re: SQL Query
You are using orders so why not:
Code:
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`
-
Re: SQL Query
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