Page 1 of 3 123 LastLast
Results 1 to 10 of 22
  1. #1
    Join Date
    Jul 2004
    Location
    Las Vegas
    Posts
    477
    Plugin Contributions
    0

    Default Help with a sql query

    Hello,

    I need to select all products from a certain category out of my current database to export to another database.

    How do I make the mysql query do it


    This is what I was thinking but didnt work:

    SELECT * ROWS FROM `zen_products` WHERE `master_categories_id` = 69

    All Im trying to do is select all products that are in master category 69

    Im sure this is really wrong however this is my first attempt at mysql syntax.
    User of zencart

  2. #2
    Join Date
    Jul 2004
    Location
    Las Vegas
    Posts
    477
    Plugin Contributions
    0

    Default Re: Help with a sql query

    I got:

    SELECT `master_categories_id` = 69 FROM `zen_products`

    which got me closer I think, meaning i didnt get an error but still quite right.
    User of zencart

  3. #3
    Join Date
    Jan 2004
    Posts
    65,317
    Blog Entries
    7
    Plugin Contributions
    228

    Default Re: Help with a sql query

    Quote Originally Posted by batteryman View Post

    SELECT * ROWS FROM `zen_products` WHERE `master_categories_id` = 69
    remove the word ROWS, and you'll get all the meta information about your products, but without descriptions

    If you want descriptions too, use:

    Code:
    select p.*, pd.* from products p, products_description pd where p.products_id = pd.products_id and p.master_categories_id = 69;
    .

    Zen Cart - putting the dream of business ownership within reach of anyone!
    Donate to: DrByte directly or to the Zen Cart team as a whole

    Remember: Any code suggestions you see here are merely suggestions. You assume full responsibility for your use of any such suggestions, including any impact ANY alterations you make to your site may have on your PCI compliance.
    Furthermore, any advice you see here about PCI matters is merely an opinion, and should not be relied upon as "official". Official PCI information should be obtained from the PCI Security Council directly or from one of their authorized Assessors.

  4. #4
    Join Date
    Jul 2004
    Location
    Las Vegas
    Posts
    477
    Plugin Contributions
    0

    Default Re: Help with a sql query

    Quote Originally Posted by DrByte View Post
    If you want descriptions too, use:

    Code:
    select p.*, pd.* from products p, products_description pd where p.products_id = pd.products_id and p.master_categories_id = 69;
    I had to change your sql to include my prefix zen_

    Code:
    select p.*, pd.* from zen_products p, zen_products_description pd where p.products_id = pd.products_id and p.master_categories_id = 69;
    Which then produces the result:
    MySQL returned an empty result set (i.e. zero rows). (Query took 0.0010 sec)
    So what do you think I should do, also what is p. and pd. and how do they represent what they represent?

    I have seen those called in another module and wondered what they were then as well.
    User of zencart

  5. #5
    Join Date
    Jan 2004
    Posts
    65,317
    Blog Entries
    7
    Plugin Contributions
    228

    Default Re: Help with a sql query

    if you're getting 0 results, then you have nothing matching the master-categories-id = 69.


    The p. and pd. are table-aliases... note "products p, products_description pd" ... that's where the aliases are defined.
    .

    Zen Cart - putting the dream of business ownership within reach of anyone!
    Donate to: DrByte directly or to the Zen Cart team as a whole

    Remember: Any code suggestions you see here are merely suggestions. You assume full responsibility for your use of any such suggestions, including any impact ANY alterations you make to your site may have on your PCI compliance.
    Furthermore, any advice you see here about PCI matters is merely an opinion, and should not be relied upon as "official". Official PCI information should be obtained from the PCI Security Council directly or from one of their authorized Assessors.

  6. #6
    Join Date
    Jul 2004
    Location
    Las Vegas
    Posts
    477
    Plugin Contributions
    0

    Default Re: Help with a sql query

    Quote Originally Posted by DrByte View Post
    if you're getting 0 results, then you have nothing matching the master-categories-id = 69.


    The p. and pd. are table-aliases... note "products p, products_description pd" ... that's where the aliases are defined.
    Im feel so stupid right now, I was running the query in my new install. OOops!

    Thank you Dr. Byte
    User of zencart

  7. #7
    Join Date
    Jul 2004
    Location
    Las Vegas
    Posts
    477
    Plugin Contributions
    0

    Default Re: Help with a sql query

    Oh, big question,

    Because I used the query you suggested where the product and description are displayed together in the results. How do I import this information into a new database so that the information goes into its table?
    User of zencart

  8. #8
    Join Date
    Aug 2007
    Location
    Tampa
    Posts
    15
    Plugin Contributions
    0

    help question Re: Help with a sql query

    I too have a SQL question. I need all (Several) Order Product Attributes to be in their own field and Exported to CVS. I know how to Export using MyPHP but I don't have enough SQL knowledge to combine all the OrderProductAttributes to one line for each product ordered.

  9. #9
    Join Date
    Jul 2004
    Location
    Las Vegas
    Posts
    477
    Plugin Contributions
    0

    Default Re: Help with a sql query

    Quote Originally Posted by BWB View Post
    I too have a SQL question. I need all (Several) Order Product Attributes to be in their own field and Exported to CVS. I know how to Export using MyPHP but I don't have enough SQL knowledge to combine all the OrderProductAttributes to one line for each product ordered.
    What are the tables you want to export from and what are the fields you want included??
    User of zencart

  10. #10
    Join Date
    Aug 2007
    Location
    Tampa
    Posts
    15
    Plugin Contributions
    0

    Default Re: Help with a sql query

    Quote Originally Posted by batteryman View Post
    What are the tables you want to export from and what are the fields you want included??
    The file is Orders_Products_Attributes and the fields are
    orders_id orders_products_id products_options products_options_values The problem is I have several options and option values for products but need all of them on separated on a single row per order/product. And to make things worse there can be the more than one of the same product with different attributes per order.

 

 
Page 1 of 3 123 LastLast

Similar Threads

  1. 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
  2. Replies: 4
    Last Post: 17 Oct 2013, 10:55 PM
  3. Need help with SQL query. Want to make membership that expires.
    By TecBrat in forum Managing Customers and Orders
    Replies: 1
    Last Post: 18 Jun 2010, 03:43 PM
  4. Help with SQL query
    By Danielle in forum General Questions
    Replies: 3
    Last Post: 9 Jun 2008, 05:24 AM
  5. Help with SQL query returning multiple rows
    By acetate in forum General Questions
    Replies: 3
    Last Post: 9 Mar 2008, 03:23 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