Thread: Opencart sql

    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2010
    Posts
    2
    Rep Power
    0

    Opencart sql


    hi all

    im using opencart as a webshop and need to get at some data via sql.

    the tables are oc_order and oc_order_option if anyone is familiar.

    obvously i need to extract the data from the the two tables but the oc_order_option has the options as a list.

    Code:
    oc_order_product
    ===========
    order_product_id name
    28               BANANA
    
    oc_order_product_option
    ===============
    order_option_id order_id    order_product_id product_option_id product_option_value_id name
    1               27          28               411               0                       YELLOW   
    2               27          28               412               0                       FRUIT
    now what i want is?

    Code:
    Order ID	 Product Name Product Options
    27               BANANA        YELLOW FRUIT

    any help much appreciated
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2010
    Posts
    2
    Rep Power
    0
    further info

    my current code is

    Code:
    SELECT
      oc_order.order_id,
      oc_order.email,
      oc_order_status.name,
      oc_order_product.model,
      oc_order_product.quantity,
      oc_order_option.value
    FROM
      oc_order
    INNER JOIN oc_order_status
    ON
      oc_order.order_status_id = oc_order_status.order_status_id
    INNER JOIN oc_order_product
    ON
      oc_order.order_id = oc_order_product.order_id
    inner JOIN oc_order_option
    ON
      oc_order.order_id = oc_order_option.order_id
    where product_option_value_id = 0

    that gives me
    Code:
    47	aaaaaaaaaaa@live.co.uk	Complete	KJAHSD123213123123	1	LARGE
    47	aaaaaaaaaaa@live.co.uk	Complete	KJAHSD123213123123	1	RED
    48	bbbbbbbbbbb@yahoo.com	Complete	JGHFHGK76827642382	1	SMALL
    48	bbbbbbbbbbb@yahoo.com	Complete	JGHFHGK76827642382	1	GREEN
    56	ccccccccccccc@aol.com	Complete	FKHSDFJKH287438933	1	MEDIUM
    56	ccccccccccccc@aol.com	Complete	FKHSDFJKH287438933	1	GREEN
    but i need
    Code:
    47	aaaaaaaaaaa@live.co.uk	Complete	KJAHSD123213123123	1	LARGE, RED
    48	bbbbbbbbbbb@yahoo.com	Complete	JGHFHGK76827642382	1	SMALL, GREEN
    56	ccccccccccccc@aol.com	Complete	FKHSDFJKH287438933	1	MEDIUM, GREEN

    help please.

    many thanks
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,235
    Rep Power
    4279
    your table layouts match neither your sample data not your query

    pretty hard to solve a problem with that type of vague information, so i'll just modify your query based on the comma-delimited output you want --
    Code:
    SELECT oc_order.order_id
         , oc_order.email
         , oc_order_status.name
         , oc_order_product.model
         , oc_order_product.quantity
         , GROUP_CONCAT(oc_order_option.value) AS option_values
      FROM oc_order
    INNER 
      JOIN oc_order_status
        ON oc_order_status.order_status_id = oc_order.order_status_id
    INNER 
      JOIN oc_order_product
        ON oc_order_product.order_id = oc_order.order_id
    INNER 
      JOIN oc_order_option
        ON oc_order_option.order_id = oc_order.order_id
       AND oc_order_option.product_option_value_id = 0
    GROUP
        BY oc_order.order_id
         , oc_order.email
         , oc_order_status.name
         , oc_order_product.model
         , oc_order_product.quantity
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo