Thread: SQL Query

Page 1 of 2 12 LastLast
Results 1 to 10 of 17
  1. #1
    Join Date
    Apr 2008
    Location
    UK
    Posts
    326
    Plugin Contributions
    1

    help question 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

  2. #2
    Join Date
    Aug 2005
    Location
    Arizona
    Posts
    27,761
    Plugin Contributions
    9

    Default 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
    Zen-Venom Get Bitten

  3. #3
    Join Date
    Apr 2008
    Location
    UK
    Posts
    326
    Plugin Contributions
    1

    Default 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

  4. #4
    Join Date
    Aug 2005
    Location
    Arizona
    Posts
    27,761
    Plugin Contributions
    9

    Default 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
    Zen-Venom Get Bitten

  5. #5
    Join Date
    Apr 2008
    Location
    UK
    Posts
    326
    Plugin Contributions
    1

    Default 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

  6. #6
    Join Date
    Aug 2005
    Location
    Arizona
    Posts
    27,761
    Plugin Contributions
    9

    Default Re: SQL Query

    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....
    Zen-Venom Get Bitten

  7. #7
    Join Date
    Jun 2008
    Location
    Washington, DC
    Posts
    785
    Plugin Contributions
    7

    Default 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
    • 446F63746F722057686F •

  8. #8
    Join Date
    Apr 2008
    Location
    UK
    Posts
    326
    Plugin Contributions
    1

    Default 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

  9. #9
    Join Date
    Jun 2008
    Location
    Washington, DC
    Posts
    785
    Plugin Contributions
    7

    Default 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`
    • 446F63746F722057686F •

  10. #10
    Join Date
    Apr 2008
    Location
    UK
    Posts
    326
    Plugin Contributions
    1

    Default 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

 

 
Page 1 of 2 12 LastLast

Similar Threads

  1. Replies: 9
    Last Post: 12 Jul 2023, 12:26 AM
  2. v154 Help with a SQL Query for Query Builder
    By lindasdd in forum Managing Customers and Orders
    Replies: 2
    Last Post: 24 Mar 2016, 01:18 PM
  3. v151 SQL query setup. How do I TEST a query 'off-line'?
    By lewisasding in forum General Questions
    Replies: 3
    Last Post: 8 Mar 2013, 12:24 AM
  4. Replies: 2
    Last Post: 15 Aug 2011, 04:58 AM
  5. Sql Query
    By bebijuteria in forum General Questions
    Replies: 9
    Last Post: 3 Nov 2007, 05:49 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
disjunctive-egg
Zen-Cart, Internet Selling Services, Klamath Falls, OR