Join records from the same table

Discussion in 'PHP' started by WebRob, Mar 18, 2008.

0
  1. #1
    I am using ZenCart and have created a custom query for retrieving order data. The only problem is that when I have products with attributes it creates 2 records with the same order. This is because that each product gets a record for the attributes it gets.

    Here is my query:
    
SELECT zo.orders_id as v_orders_id,
customers_id as v_customers_id,
customers_name as v_customers_name,
customers_company as v_customers_company,
customers_street_address as v_customers_street_address,
customers_suburb as v_customers_suburb,
customers_city as v_customers_city,
customers_postcode as v_customers_postcode,
customers_country as v_customers_country,
customers_telephone as v_customers_telephone,
customers_email_address as v_customers_email_address,
date_purchased as v_date_purchased,
orders_status_name as v_orders_status_name,
products_model as v_products_model,
products_id as v_products_id,
products_name as v_products_name,
products_options as v_products_options,
products_options_values as v_products_options_values
FROM ".TABLE_ORDERS_PRODUCTS." zop LEFT JOIN ".TABLE_ORDERS_PRODUCTS_ATTRIBUTES." opa
ON(zop.orders_products_id = opa.orders_products_id), ".TABLE_ORDERS." zo, ".TABLE_ORDERS_STATUS." zos
WHERE zo.orders_id = zop.orders_id
AND zop.orders_id BETWEEN '1' AND '800'
AND zo.orders_status = zos.orders_status_id
AND zop.products_id = '147'
    PHP:
    I am using a class that helps me output the following
    
<table>
  <ROW>
    <V_ORDERS_ID>796</V_ORDERS_ID>
    <V_CUSTOMERS_ID>509</V_CUSTOMERS_ID>
    <V_CUSTOMERS_NAME>rob milburn</V_CUSTOMERS_NAME>
    <V_CUSTOMERS_COMPANY/>
    <V_CUSTOMERS_STREET_ADDRESS>2511     florida</V_CUSTOMERS_STREET_ADDRESS>
    <V_CUSTOMERS_SUBURB>5</V_CUSTOMERS_SUBURB>
    <V_CUSTOMERS_CITY>huntington beach</V_CUSTOMERS_CITY>
    <V_CUSTOMERS_POSTCODE>92648</V_CUSTOMERS_POSTCODE>
    <V_CUSTOMERS_COUNTRY>United States</V_CUSTOMERS_COUNTRY>
    <V_CUSTOMERS_TELEPHONE>714-849-3124</V_CUSTOMERS_TELEPHONE>
    <V_CUSTOMERS_EMAIL_ADDRESS>rob@piermarketing.com</V_CUSTOMERS_EMAIL_ADDRESS>
    <V_DATE_PURCHASED>2008-02-29 13:29:55</V_DATE_PURCHASED>
    <V_ORDERS_STATUS_NAME>Processing</V_ORDERS_STATUS_NAME>
    <V_PRODUCTS_MODEL/>
	<V_PRODUCTS_ID>147</V_PRODUCTS_ID>
	<V_PRODUCTS_NAME>Business Cards</V_PRODUCTS_NAME>
	<V_PRODUCTS_OPTIONS>Name</V_PRODUCTS_OPTIONS>
	<V_PRODUCTS_OPTIONS_VALUES>rob milburn</V_PRODUCTS_OPTIONS_VALUES>
  </ROW>
  <ROW>
	<V_ORDERS_ID>796</V_ORDERS_ID>
	<V_CUSTOMERS_ID>509</V_CUSTOMERS_ID>
	<V_CUSTOMERS_NAME>rob milburn</V_CUSTOMERS_NAME>
	<V_CUSTOMERS_COMPANY/>
	<V_CUSTOMERS_STREET_ADDRESS>2511 florida</V_CUSTOMERS_STREET_ADDRESS>
	<V_CUSTOMERS_SUBURB>5</V_CUSTOMERS_SUBURB>
	<V_CUSTOMERS_CITY>huntington beach</V_CUSTOMERS_CITY>
	<V_CUSTOMERS_POSTCODE>92648</V_CUSTOMERS_POSTCODE>
	<V_CUSTOMERS_COUNTRY>United States</V_CUSTOMERS_COUNTRY>
	<V_CUSTOMERS_TELEPHONE>714-849-3124</V_CUSTOMERS_TELEPHONE>
	<V_CUSTOMERS_EMAIL_ADDRESS>rob@piermarketing.com</V_CUSTOMERS_EMAIL_ADDRESS>
	<V_DATE_PURCHASED>2008-02-29 13:29:55</V_DATE_PURCHASED>
	<V_ORDERS_STATUS_NAME>Processing</V_ORDERS_STATUS_NAME>
	<V_PRODUCTS_MODEL/>
	<V_PRODUCTS_ID>147</V_PRODUCTS_ID>
	<V_PRODUCTS_NAME>Business Cards</V_PRODUCTS_NAME>
	<V_PRODUCTS_OPTIONS>Address</V_PRODUCTS_OPTIONS>
	<V_PRODUCTS_OPTIONS_VALUES>2511 florida</V_PRODUCTS_OPTIONS_VALUES>
  </ROW>
</table>
    PHP:
    Now I just need to figure out how to combine this line <V_PRODUCTS_OPTIONS_VALUES> becuase as it is now, this is jsut the 1 order, but since there are multiple <V_PRODUCTS_OPTIONS_VALUES> it creates new records for each instance.

    How can I accomplish this?
     
    WebRob, Mar 18, 2008 IP