Mysql multi-table query construction

[gjh42]

I am upgrading the ancient Orders Export mod to add functionality I need, make it fully configurable and more modern in coding. (The new JK Order Export doesn't do what I need, and does not use ZC standard db interfacing anyway.)

The original used mysql_connect() and mysql_fetch_array() which I was able to convert to current Zen Cart handling, but I am stuck on querying the db for order attribute info. The original had attribute output added to it, but I don't know if that ever functioned correctly. I have rebuilt the select construction, but the form of the segments added matches the original.

//building the SELECT...

    case 'v_products_options':
      $filelayout[] = 'v_products_options';
      $filelayout_sql .= ' products_options as v_products_options,';
      break;
    case 'v_products_options_values':
      $filelayout[] = 'v_products_options_values';
      $filelayout_sql .= ' products_options_values as v_products_options_values,';
      break;
//(other select cases are fully functional, and these get added to the select, just do not return results)
//...

$filelayout_sql .= " 
FROM ".TABLE_ORDERS_PRODUCTS." op LEFT JOIN ".TABLE_ORDERS_PRODUCTS_ATTRIBUTES." opa
ON(op.orders_products_id = opa.orders_products_id), ".TABLE_ORDERS." o, ".TABLE_ORDERS_STATUS." os"; 

(I added

if (in_array('v_orders_comment',$filelayout)) $filelayout_sql .= ", ".TABLE_ORDERS_STATUS_HISTORY." osh"; 

which gets the comments correctly.)

I get fields for the option name and value in the download, but no output in the fields.
Any ideas?

[DrByte]

When I run this query (based on constructing it from your code) in phpMyAdmin against a live database I do get data for those fields, when there is data to display:

select
products_options as v_products_options,
products_options_values as v_products_options_values
FROM orders_products op LEFT JOIN orders_products_attributes opa
ON(op.orders_products_id = opa.orders_products_id), orders o, orders_status os

There are some rows where the output is null / blank but that's where there's no matching data. Other rows do show data.

That said, the query is going to give you multiple duplicate rows for each order if you have more than one attribute on that order. Hopefully the rest of your code accounts for that.
You'll see that core ZC code does specific separate queries to pull attribute info when needed, and then loops through those results when displaying relevant data. But it never does any spreadsheet-style output such as what you're attempting, so some adaptation will be required.

[gjh42]

THanks! I later found that I was getting the attribute output; not sure if it was working all along or I touched something that made it work. THat's what happens with late-night last-minute coding:)

I did find that the order comments field was playing havoc with the output, causing dozens of duplicated rows with comments randomly showing up in rows from different orders. I saw that the attributes query used the left join/on... structure, and would duplicate that for comments, but not sure how to have that in the same query with the attributes left join.

I anticipated that there would be duplicate rows for multiple products per order, and duplicate rows for each product with attributes, and will work out appropriate stripping once I get the basic output straightened out. (These duplications would have been in the original Order Export results too.)