Report Automation and Joining Tables

[riomaha]

I am going to look into what you had provided. For now, this is what I am doing

$order_text = "SELECT o.orders_id, customers_email_address, customers_telephone, order_total, products_name, op.products_price, final_price, op.products_quantity, date_purchased, ot.value, orders_products_id, order_tax, o.orders_status, o.payment_method, op.products_id, mr.card, mr.moneris_orderid";
	$order_text = $order_text . " FROM (". TABLE_ORDERS ." o LEFT JOIN ". TABLE_ORDERS_PRODUCTS ." op ON o.orders_id = op.orders_id) , ". TABLE_ORDERS_TOTAL ." ot, " . TABLE_MONERIS_ORDERIDS . " mr";
	$order_text = $order_text . " WHERE o.orders_id = ot.orders_id ";
	$order_text = $order_text . " AND date_purchased >= $str_startdate";
	$order_text = $order_text . " AND date_purchased < $str_enddate";
	$order_text = $order_text . " AND ot.class = 'ot_shipping'";
	$order_text = $order_text . " AND mr.orders_id = o.orders_id ";
	$order_text = $order_text . "ORDER BY orders_id ASC";

$order_textdetails = $db->Execute($order_text);

  while (!$order_textdetails->EOF) {
$stroid = $order_textdetails->fields['orders_id'];

$order_product = "SELECT count(op.orders_id) as madcount";
	$order_product = $order_product . " FROM (". TABLE_ORDERS ." o LEFT JOIN ". TABLE_ORDERS_PRODUCTS ." op ON o.orders_id = op.orders_id) ";
	$order_product = $order_product . " WHERE o.orders_id = $stroid";
	$order_product = $order_product . " AND date_purchased >= $str_startdate";
	$order_product = $order_product . " AND date_purchased < $str_enddate";
	$order_product = $order_product . " GROUP BY op.orders_id ASC";

	$order_productdetails = $db->Execute($order_product);

$recordcount = mysql_query($order_productdetails);
$num_rows = mysql_num_rows($recordcount); 
		  while (!$order_productdetails->EOF) {

for ($i = 0, $n = $order_productdetails->fields['madcount']; $i < $n; $i++) {
Display Line 0001
Display Line 0002
Display Line 0003
}

[riomaha]

Alright, I have not been able to get it to work at all. I went back to the drawing board and simplified my statements so that I can get through it but still no luck.

First thing is my select statement

    $order_text = "SELECT o.orders_id, customers_email_address, customers_telephone, order_total, products_name, op.products_price, final_price, op.products_quantity, date_purchased, orders_products_id, o.orders_status, o.payment_method, op.products_id";
    $order_text = $order_text . " FROM (". TABLE_ORDERS ." o LEFT JOIN ". TABLE_ORDERS_PRODUCTS ." op ON o.orders_id = op.orders_id)";
    $order_text = $order_text . " WHERE date_purchased >= $str_startdate";
    $order_text = $order_text . " AND date_purchased < $str_enddate"; 

That records by oddly enough it shows the first line as order 1 but then the next line shows order 1 followed by order 2, and the third line shows order1, 2, 3 and keeps on going like that.

the end result that I am trying to achieve is like this

OID-----Line#----Text
100-----001----- [email protected]
100-----002----- 2 x Kitkat Bars
100-----003----- 2 x Mars Bar
100-----004----- Final Price $4
101-----001----- [email protected]
101-----002----- 2 x Bounty Bars
101-----003----- Final Price $2

etc.

[mc12345678]

Alright, I have not been able to get it to work at all. I went back to the drawing board and simplified my statements so that I can get through it but still no luck.

First thing is my select statement

    $order_text = "SELECT o.orders_id, customers_email_address, customers_telephone, order_total, products_name, op.products_price, final_price, op.products_quantity, date_purchased, orders_products_id, o.orders_status, o.payment_method, op.products_id";
    $order_text = $order_text . " FROM (". TABLE_ORDERS ." o LEFT JOIN ". TABLE_ORDERS_PRODUCTS ." op ON o.orders_id = op.orders_id)";
    $order_text = $order_text . " WHERE date_purchased >= $str_startdate";
    $order_text = $order_text . " AND date_purchased < $str_enddate"; 

That records by oddly enough it shows the first line as order 1 but then the next line shows order 1 followed by order 2, and the third line shows order1, 2, 3 and keeps on going like that.

the end result that I am trying to achieve is like this

OID-----Line#----Text
100-----001----- [email protected]
100-----002----- 2 x Kitkat Bars
100-----003----- 2 x Mars Bar
100-----004----- Final Price $4
101-----001----- [email protected]
101-----002----- 2 x Bounty Bars
101-----003----- Final Price $2

etc.

Here's something that I would expect would work.. Uses echo to display on screen, doesn't use any type of tab delimiter, or specific screen arrangement, but you should be able to fill that part in as necessary. Basically used spaces between each field.

$order_text = "SELECT o.orders_id, customers_email_address, customers_telephone, order_total, products_name, op.products_price, final_price, op.products_quantity, date_purchased, orders_products_id, o.orders_status, o.payment_method, op.products_id";
    $order_text .= " FROM (". TABLE_ORDERS ." o LEFT JOIN ". TABLE_ORDERS_PRODUCTS ." op ON o.orders_id = op.orders_id)";
    $order_text .= " WHERE date_purchased >= :str_startdate:";
    $order_text .= " AND date_purchased < :str_startdate:";

$order_text = $db->bindVars($order_text, ':str_startdate:', $str_startdate, 'date');
$order_text = $db->bindVars($order_text, ':str_enddate:', $str_enddate, 'date');

$order_data = $db->Execute($order_text);

if ($order_data->RecordCount > 0) {
 echo 'OID' . ' ' . 'Line#' . ' ' . 'Text' . "\n";
} else {
 echo 'No records' . "\n";
}
while (!$order_data->EOF) {
  $order_id = $order_data->fields['orders_id'];
  $order_email = $order_data->fields['customers_email_address'];
  $order_final_price = $order_data->fields['final_price'];
  $linenumber = 1;
  
  $line = $order_id . ' ' . $linenumber . ' ' . $order_email . "\n";
  echo $line;
  while (!$order_data->EOF && $order_data->fields['orders_id'] == $order_id) {
    $linenumber++;
    $line = $order_id . ' ' . $linenumber . ' ' . $order_data->fields['products_quantity'] . ' x ' . $order_data->fields['products_name'] . "\n";
    echo $line;
    $order_data->MoveNext();
  }

  $linenumber++;
  $line = $order_id . ' ' . $linenumber . ' ' . 'Final Price ' . $order_final_price . "\n";

  echo $line;

  if (!$order_data->EOF) {
    $order_data->MoveNext();
  }
}

'Final Price ' really should be in a language define as a constant to support multi-language use of this code...

[riomaha]

You are GENIUS. Thank you for getting me steps closer to my end goal. I do agree with you that "Final Price" needs to be carried out in a language file.

There is a one odd behavior in your code that I am trying to pin down and might take me sometime. See a semi actual extract from my database. When I run your code, it generates the file as expected but it is skipping order numbers. You will notice that Order 6383 is missing. This occurs throughout the extract where there is one or two missing orders in between. The only patterns I could find is that there is not a single sequential order in the entire extract and the first lien of the extract is "No Records".

6382----1----mad....ca
6382----2----1 x Request XXXX
6382----3----Final Price 15.0000
6384----1----dimit......com
6384----2----1 x Request YYYYYY
6384----3----Final Price 15.0000

I did modify the select statement to add where OID equal to one of the missing orders to see if there is a record to be displayed and I do get results back. So I know the order information are there.

I looked closely at this code

if ($order_data->RecordCount > 0) {
 echo 'OID' . ' ' . 'Line#' . ' ' . 'Text' . "\n";
} else {
 echo 'No records' . "\n";
} 

and tried to echo the record count. it always shows ZERO . It never showed the column headers. So I changed it to

if (!$order_data->RecordCount = 0) {
 echo 'OID' . ' ' . 'Line#' . ' ' . 'Text' . "\n";
 echo ($order_data->RecordCount) . "\n";
} else {
 echo 'No records' . "\n";
 echo ($order_data->RecordCount) . "\n";
} 

That did get rid of "No Records" message at the start of the extract. and showed the column headers

Here's something that I would expect would work.. Uses echo to display on screen, doesn't use any type of tab delimiter, or specific screen arrangement, but you should be able to fill that part in as necessary. Basically used spaces between each field.

$order_text = "SELECT o.orders_id, customers_email_address, customers_telephone, order_total, products_name, op.products_price, final_price, op.products_quantity, date_purchased, orders_products_id, o.orders_status, o.payment_method, op.products_id";
    $order_text .= " FROM (". TABLE_ORDERS ." o LEFT JOIN ". TABLE_ORDERS_PRODUCTS ." op ON o.orders_id = op.orders_id)";
    $order_text .= " WHERE date_purchased >= :str_startdate:";
    $order_text .= " AND date_purchased < :str_startdate:";

$order_text = $db->bindVars($order_text, ':str_startdate:', $str_startdate, 'date');
$order_text = $db->bindVars($order_text, ':str_enddate:', $str_enddate, 'date');

$order_data = $db->Execute($order_text);

if ($order_data->RecordCount > 0) {
 echo 'OID' . ' ' . 'Line#' . ' ' . 'Text' . "\n";
} else {
 echo 'No records' . "\n";
}
while (!$order_data->EOF) {
  $order_id = $order_data->fields['orders_id'];
  $order_email = $order_data->fields['customers_email_address'];
  $order_final_price = $order_data->fields['final_price'];
  $linenumber = 1;
  
  $line = $order_id . ' ' . $linenumber . ' ' . $order_email . "\n";
  echo $line;
  while (!$order_data->EOF && $order_data->fields['orders_id'] == $order_id) {
    $linenumber++;
    $line = $order_id . ' ' . $linenumber . ' ' . $order_data->fields['products_quantity'] . ' x ' . $order_data->fields['products_name'] . "\n";
    echo $line;
    $order_data->MoveNext();
  }

  $linenumber++;
  $line = $order_id . ' ' . $linenumber . ' ' . 'Final Price ' . $order_final_price . "\n";

  echo $line;

  if (!$order_data->EOF) {
    $order_data->MoveNext();
  }
}

'Final Price ' really should be in a language define as a constant to support multi-language use of this code...

[mc12345678]

Here's something that I would expect would work.. Uses echo to display on screen, doesn't use any type of tab delimiter, or specific screen arrangement, but you should be able to fill that part in as necessary. Basically used spaces between each field.

$order_text = "SELECT o.orders_id, customers_email_address, customers_telephone, order_total, products_name, op.products_price, final_price, op.products_quantity, date_purchased, orders_products_id, o.orders_status, o.payment_method, op.products_id";
    $order_text .= " FROM (". TABLE_ORDERS ." o LEFT JOIN ". TABLE_ORDERS_PRODUCTS ." op ON o.orders_id = op.orders_id)";
    $order_text .= " WHERE date_purchased >= :str_startdate:";
    $order_text .= " AND date_purchased < :str_startdate:";

$order_text = $db->bindVars($order_text, ':str_startdate:', $str_startdate, 'date');
$order_text = $db->bindVars($order_text, ':str_enddate:', $str_enddate, 'date');

$order_data = $db->Execute($order_text);

if ($order_data->RecordCount > 0) {
 echo 'OID' . ' ' . 'Line#' . ' ' . 'Text' . "\n";
} else {
 echo 'No records' . "\n";
}
while (!$order_data->EOF) {
  $order_id = $order_data->fields['orders_id'];
  $order_email = $order_data->fields['customers_email_address'];
  $order_final_price = $order_data->fields['final_price'];
  $linenumber = 1;
  
  $line = $order_id . ' ' . $linenumber . ' ' . $order_email . "\n";
  echo $line;
  while (!$order_data->EOF && $order_data->fields['orders_id'] == $order_id) {
    $linenumber++;
    $line = $order_id . ' ' . $linenumber . ' ' . $order_data->fields['products_quantity'] . ' x ' . $order_data->fields['products_name'] . "\n";
    echo $line;
    $order_data->MoveNext();
  }

  $linenumber++;
  $line = $order_id . ' ' . $linenumber . ' ' . 'Final Price ' . $order_final_price . "\n";

  echo $line;

  if (!$order_data->EOF) {
    $order_data->MoveNext();
  }
}

'Final Price ' really should be in a language define as a constant to support multi-language use of this code...

You are GENIUS. Thank you for getting me steps closer to my end goal. I do agree with you that "Final Price" needs to be carried out in a language file.

There is a one odd behavior in your code that I am trying to pin down and might take me sometime. See a semi actual extract from my database. When I run your code, it generates the file as expected but it is skipping order numbers. You will notice that Order 6383 is missing. This occurs throughout the extract where there is one or two missing orders in between. The only patterns I could find is that there is not a single sequential order in the entire extract and the first lien of the extract is "No Records".

6382----1----mad....ca
6382----2----1 x Request XXXX
6382----3----Final Price 15.0000
6384----1----dimit......com
6384----2----1 x Request YYYYYY
6384----3----Final Price 15.0000

I did modify the select statement to add where OID equal to one of the missing orders to see if there is a record to be displayed and I do get results back. So I know the order information are there.

I looked closely at this code

if ($order_data->RecordCount > 0) {
 echo 'OID' . ' ' . 'Line#' . ' ' . 'Text' . "\n";
} else {
 echo 'No records' . "\n";
} 

and tried to echo the record count. it always shows ZERO . It never showed the column headers. So I changed it to

if (!$order_data->RecordCount = 0) {
 echo 'OID' . ' ' . 'Line#' . ' ' . 'Text' . "\n";
 echo ($order_data->RecordCount) . "\n";
} else {
 echo 'No records' . "\n";
 echo ($order_data->RecordCount) . "\n";
} 

That did get rid of "No Records" message at the start of the extract. and showed the column headers

Oops... Couple of things, the RecordCount is a function and therefore should have () after it... I thought about that later, but time had expired for any further edits... Also, because of the MoveNexts that are done in the internal loop, the fact that no additional data is being collected from the query result after the internal loop, and nothing is changing the value of $order_id or $order_data->fields['orders_id'] between the assignment to $order_id and the internal while loop, the additional MoveNext at the end is unnecessary...

For understanding, the assignment that is/was performed above by using $order_data->RecordCount = 0 always evaluates as true because it is saying make $order_data->RecordCount the value of 0 and if that happens successfully then perform the action of the if... == is use for comparison or === to exactly match including data type.

Also, the comment about 'Final Price ' was made before I had added the "header row" which the data of that should also be captured in a Language file... Ie. anywhere that text is hard-coded should be replaced by a constant that is defined in a language... There are a few exceptions to that within the ZC code for example the admin logging of actions performed is (to date) hard-coded in English.

$order_text = "SELECT o.orders_id, customers_email_address, customers_telephone, order_total, products_name, op.products_price, final_price, op.products_quantity, date_purchased, orders_products_id, o.orders_status, o.payment_method, op.products_id";
    $order_text .= " FROM (". TABLE_ORDERS ." o LEFT JOIN ". TABLE_ORDERS_PRODUCTS ." op ON o.orders_id = op.orders_id)";
    $order_text .= " WHERE date_purchased >= :str_startdate:";
    $order_text .= " AND date_purchased < :str_startdate:";

$order_text = $db->bindVars($order_text, ':str_startdate:', $str_startdate, 'date');
$order_text = $db->bindVars($order_text, ':str_enddate:', $str_enddate, 'date');

$order_data = $db->Execute($order_text);

if ($order_data->RecordCount() > 0) {
 echo 'OID' . ' ' . 'Line#' . ' ' . 'Text' . "\n";
} else {
 echo 'No records' . "\n";
}
while (!$order_data->EOF) {
  $order_id = $order_data->fields['orders_id'];
  $order_email = $order_data->fields['customers_email_address'];
  $order_final_price = $order_data->fields['final_price'];
  $linenumber = 1;
  
  $line = $order_id . ' ' . $linenumber . ' ' . $order_email . "\n";
  echo $line;
  while (!$order_data->EOF && $order_data->fields['orders_id'] == $order_id) {
    $linenumber++;
    $line = $order_id . ' ' . $linenumber . ' ' . $order_data->fields['products_quantity'] . ' x ' . $order_data->fields['products_name'] . "\n";
    echo $line;
    $order_data->MoveNext();
  }

  $linenumber++;
  $line = $order_id . ' ' . $linenumber . ' ' . 'Final Price ' . $order_final_price . "\n";

  echo $line;

}

[riomaha]

Thank you, I should have realized that recordcount but would have taken me a while as I am not all knowing of PHP. Now I can start optimizing(or destroying) my extract.

Thank again for your support.

[mc12345678]

Thank you, I should have realized that recordcount but would have taken me a while as I am not all knowing of PHP. Now I can start optimizing(or destroying) my extract.

Thank again for your support.

Well the absence of the parentheses () after RecordCount would not have been totally obvious unless other ZC code had been reviewed for the same function. It is a ZC database function added to provide handy information to be used such as above. As for the assignment aspect, yeah the fact that there are three different "uses" of the = symbol is not blatantly obvious to all... Have had that happen on accident where I just didn't press the key fully twice or something and it can also be a useful function to make an assignment in a test statement, but should be on purpose.. :)

Good luck, btw, liked the SQL statement, I mean it does provide a lot of extra data in the rows, but removes the need to perform subsequent sql queries based on previous queries (Ie. ask the server once and all the data is present)... Still trying to figure out "resource" wise (computer memory, access time, etc...) where that balance is in requesting a lot of data at once and possibly asking for data more than once in a bit more of a "targeted" query for each request...

For example on the extreme, could run one query to return every field, table, etc. within the database into a single variable, but that would somewhat defeat the purpose of having the database as a resource (not to mention would need to update the variable periodically to catch any changes).