trying to get value from mysql data
Hello, i'm using a script that exports orders from Magento and for some reason I can't get it to show the quantity ordered? I can see the lines in the code (if($fieldvalue == "quantity"). I attached the table that it's calling "sales_flat_order_item"
// FUNCTION AREA
function get_DB_Row($query){
$result = @mysql_query($query);
$numresults = @mysql_num_rows($result);
for ($i = 0; $i < $numresults; $i++)
{
$row = @mysql_fetch_array($result);
}
return $row;
}
// Using this temporary function until I find correct way to pull country names from the database according to two digit code
// Put in your own until a database polled solution is uncovered
function getCountryName($code){
switch($code) {
case "GB" : $country = "United Kingdom";
break;
case "IE" : $country = "Ireland";
break;
}
return $country;
}
// INITILISATION AREA
$sitename = "boyesen.com"; // update this to customise your filename
$datestring = date('j-m-y');
$filename = $sitename."-".$datestring;
$output = "";
$fieldlist = array("orderid","firstname","lastname","address","city","region","country",
"postcode","telephone","email","itemcode","itemname","quantity","shipping_amount","shipping_instructions","order_date");
$checklist = array("country", "quantity");
// FIELDLIST INSTRUCTIONS
// The order of the CSV fields are set here as well as the field titles
// The field titles must correspond to a number of SQL Variables being set with the AS operator below
// If you change the default titles be sure to also update the SQL command accordingly
// Please note that 'country' and 'quantity' values must be processed but should still be put in your prefered sequence
$numfields = sizeof($fieldlist);
// ********************* NOW START BUILDING THE CSV
// Create the column headers
for($k =0; $k < $numfields; $k++) {
$output .= $fieldlist[$k];
if ($k < ($numfields-1)) $output .= ", ";
}
$output .= "\n";
$orderquery = "SELECT * FROM sales_order"; // Query to find unprocessed orders
$orderresult = mysql_query($orderquery);
$numorders = @mysql_num_rows($orderresult);
for($i =0; $i < $numorders; $i++) {
$order = @mysql_fetch_array($orderresult); // Place each order into an array for proccessing
$orderid = $order['entity_id']; // Grab the orderid for use in the main SQL command
// The following SQL command will find all the individual items associated with this order
$itemquery = "SELECT sales_flat_order_item.order_id AS orderid,
sales_flat_order_item.product_type,
sales_flat_order_item.quote_item_id,
sales_flat_order_item.parent_item_id AS parentid,
sales_flat_order_item.sku AS itemcode,
sales_flat_order_item.name AS itemname,
sales_flat_order_item.qty_ordered AS qty_ordered,
sales_flat_quote_address.shipping_amount AS shipping_amount,
sales_flat_order_item.qty_shipped AS qty_shipped,
sales_flat_quote_address.email AS email,
sales_flat_quote_address.prefix AS title,
sales_flat_quote_address.firstname AS firstname,
sales_flat_quote_address.lastname AS lastname,
sales_flat_quote_address.street AS address,
sales_flat_quote_address.city AS city,
sales_flat_quote_address.region AS region,
sales_flat_quote_address.country_id AS country,
sales_flat_quote_address.postcode AS postcode,
sales_flat_quote_address.telephone AS telephone,
sales_flat_quote_address.shipping_description AS shipping_instructions,
sales_flat_quote_address.created_at AS order_date
FROM sales_flat_order_item
INNER JOIN sales_flat_quote_item
ON sales_flat_order_item.quote_item_id = sales_flat_quote_item.item_id
INNER JOIN sales_flat_quote_address
ON sales_flat_quote_item.quote_id = sales_flat_quote_address.quote_id
WHERE sales_flat_order_item.order_id = $orderid
AND sales_flat_quote_address.address_type = 'shipping'
AND sales_flat_order_item.product_type <> 'configurable'
AND sales_flat_order_item.qty_shipped < sales_flat_order_item.qty_ordered";
$itemresult = mysql_query($itemquery); // Run the query
$numitems = @mysql_num_rows($itemresult); // Check the number of items in the order
for($j =0; $j < $numitems; $j++) {
$item = @mysql_fetch_array($itemresult); // Place the item in an array for processing
// Output the order item values in the same sequence set in the fieldlist to match headers
for($m =0; $m < sizeof($fieldlist); $m++) {
$fieldvalue = $fieldlist[$m];
if(in_array($fieldvalue, $checklist)) { // check if on special processing list
if($fieldvalue == "country") $output .= getCountryName($item[$fieldvalue]);
if($fieldvalue == "quantity") {
$parentid = $item['parentid'];
if($parentid == 'NULL') { // simple product sold on its own (no parent)
$qty_ordered = $item["qty_ordered"];
$qty_shipped = $item["qty_shipped"];
} else { // simple product was part of bundle or configurable group
// If so, use the parent quanities to calculate
$parentitem = get_DB_row("SELECT * FROM sales_flat_order_item WHERE item_id = $parentid");
$qty_ordered = $parentitem["qty_ordered"];
$qty_shipped = $parentitem["qty_shipped"];
}
$output .= ($qty_ordered - $qty_shipped);
}
} else {
//$output .= $item[$fieldvalue];
$output .= str_replace("\n", " ", $item[$fieldvalue]);
}
if ($m < ($numfields-1)) $output .= ", ";
}
$output .= "\n";
}
}
// Send the CSV file to the browser for download
header("Content-type: text/x-csv");
header("Content-Disposition: attachment; filename=$filename.csv");
echo $output;
exit;
?>
// FUNCTION AREA
function get_DB_Row($query){
$result = @mysql_query($query);
$numresults = @mysql_num_rows($result);
for ($i = 0; $i < $numresults; $i++)
{
$row = @mysql_fetch_array($result);
}
return $row;
}
// Using this temporary function until I find correct way to pull country names from the database according to two digit code
// Put in your own until a database polled solution is uncovered
function getCountryName($code){
switch($code) {
case "GB" : $country = "United Kingdom";
break;
case "IE" : $country = "Ireland";
break;
}
return $country;
}
// INITILISATION AREA
$sitename = "boyesen.com"; // update this to customise your filename
$datestring = date('j-m-y');
$filename = $sitename."-".$datestring;
$output = "";
$fieldlist = array("orderid","firstname","lastname","address","city","region","country",
"postcode","telephone","email","itemcode","itemname","quantity","shipping_amount","shipping_instructions","order_date");
$checklist = array("country", "quantity");
// FIELDLIST INSTRUCTIONS
// The order of the CSV fields are set here as well as the field titles
// The field titles must correspond to a number of SQL Variables being set with the AS operator below
// If you change the default titles be sure to also update the SQL command accordingly
// Please note that 'country' and 'quantity' values must be processed but should still be put in your prefered sequence
$numfields = sizeof($fieldlist);
// ********************* NOW START BUILDING THE CSV
// Create the column headers
for($k =0; $k < $numfields; $k++) {
$output .= $fieldlist[$k];
if ($k < ($numfields-1)) $output .= ", ";
}
$output .= "\n";
$orderquery = "SELECT * FROM sales_order"; // Query to find unprocessed orders
$orderresult = mysql_query($orderquery);
$numorders = @mysql_num_rows($orderresult);
for($i =0; $i < $numorders; $i++) {
$order = @mysql_fetch_array($orderresult); // Place each order into an array for proccessing
$orderid = $order['entity_id']; // Grab the orderid for use in the main SQL command
// The following SQL command will find all the individual items associated with this order
$itemquery = "SELECT sales_flat_order_item.order_id AS orderid,
sales_flat_order_item.product_type,
sales_flat_order_item.quote_item_id,
sales_flat_order_item.parent_item_id AS parentid,
sales_flat_order_item.sku AS itemcode,
sales_flat_order_item.name AS itemname,
sales_flat_order_item.qty_ordered AS qty_ordered,
sales_flat_quote_address.shipping_amount AS shipping_amount,
sales_flat_order_item.qty_shipped AS qty_shipped,
sales_flat_quote_address.email AS email,
sales_flat_quote_address.prefix AS title,
sales_flat_quote_address.firstname AS firstname,
sales_flat_quote_address.lastname AS lastname,
sales_flat_quote_address.street AS address,
sales_flat_quote_address.city AS city,
sales_flat_quote_address.region AS region,
sales_flat_quote_address.country_id AS country,
sales_flat_quote_address.postcode AS postcode,
sales_flat_quote_address.telephone AS telephone,
sales_flat_quote_address.shipping_description AS shipping_instructions,
sales_flat_quote_address.created_at AS order_date
FROM sales_flat_order_item
INNER JOIN sales_flat_quote_item
ON sales_flat_order_item.quote_item_id = sales_flat_quote_item.item_id
INNER JOIN sales_flat_quote_address
ON sales_flat_quote_item.quote_id = sales_flat_quote_address.quote_id
WHERE sales_flat_order_item.order_id = $orderid
AND sales_flat_quote_address.address_type = 'shipping'
AND sales_flat_order_item.product_type <> 'configurable'
AND sales_flat_order_item.qty_shipped < sales_flat_order_item.qty_ordered";
$itemresult = mysql_query($itemquery); // Run the query
$numitems = @mysql_num_rows($itemresult); // Check the number of items in the order
for($j =0; $j < $numitems; $j++) {
$item = @mysql_fetch_array($itemresult); // Place the item in an array for processing
// Output the order item values in the same sequence set in the fieldlist to match headers
for($m =0; $m < sizeof($fieldlist); $m++) {
$fieldvalue = $fieldlist[$m];
if(in_array($fieldvalue, $checklist)) { // check if on special processing list
if($fieldvalue == "country") $output .= getCountryName($item[$fieldvalue]);
if($fieldvalue == "quantity") {
$parentid = $item['parentid'];
if($parentid == 'NULL') { // simple product sold on its own (no parent)
$qty_ordered = $item["qty_ordered"];
$qty_shipped = $item["qty_shipped"];
} else { // simple product was part of bundle or configurable group
// If so, use the parent quanities to calculate
$parentitem = get_DB_row("SELECT * FROM sales_flat_order_item WHERE item_id = $parentid");
$qty_ordered = $parentitem["qty_ordered"];
$qty_shipped = $parentitem["qty_shipped"];
}
$output .= ($qty_ordered - $qty_shipped);
}
} else {
//$output .= $item[$fieldvalue];
$output .= str_replace("\n", " ", $item[$fieldvalue]);
}
if ($m < ($numfields-1)) $output .= ", ";
}
$output .= "\n";
}
}
// Send the CSV file to the browser for download
header("Content-type: text/x-csv");
header("Content-Disposition: attachment; filename=$filename.csv");
echo $output;
exit;
?>
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Wow, you have a lot of work to do here! I cannot follow your logic, but I hope I can help you get started with the debugging process.
Step one: turn on error_reporting(E_ALL); and remove each and every @ from your function calls.
Step two: review the logic carefully. I took your function get_DB_Row() and annotated it below. I'll try to show you the right way to do this in a subsequent post. The code posted above will only return the last row of the results set, and will fail silently - this is the kind of program logic that does not leave disaster to chance.
Consider getting rid of the for loops and use while() to iterate over the query results sets. Your code will suddenly become a lot easier to read.
Learn how to do data visualization with this function:
http://us2.php.net/manual/en/function.var-dump.php
Buy this book and give yourself some time to work through the examples:
http://www.sitepoint.com/books/phpmysql4/
Step one: turn on error_reporting(E_ALL); and remove each and every @ from your function calls.
Step two: review the logic carefully. I took your function get_DB_Row() and annotated it below. I'll try to show you the right way to do this in a subsequent post. The code posted above will only return the last row of the results set, and will fail silently - this is the kind of program logic that does not leave disaster to chance.
Consider getting rid of the for loops and use while() to iterate over the query results sets. Your code will suddenly become a lot easier to read.
Learn how to do data visualization with this function:
http://us2.php.net/manual/en/function.var-dump.php
Buy this book and give yourself some time to work through the examples:
http://www.sitepoint.com/books/phpmysql4/
function get_DB_Row($query)
{
// RUN THE QUERY BUT SUPPRESS ANY ERROR MESSAGES
$result = @mysql_query($query);
// MAKES NO TEST FOR SUCCESS OR FAILURE OF THE QUERY?
// ASSIGN A VARIABLE TO THE NUMBER OF ROWS BUT SUPPRESS ANY ERROR MESSAGES
$numresults = @mysql_num_rows($result);
// ITERATE OVER THE RESULTS SET FOR SOME NUMBER OF TIMES (OR NOT)
for ($i = 0; $i < $numresults; $i++)
{
// REPLACE THE VALUE OF $row WITH A ROW OF THE RESULTS SET BUT SUPPRESS ANY ERROR MESSAGES
$row = @mysql_fetch_array($result);
}
// RETURN ONLY THE LAST ROW OF THE RESULTS SET (IF ANY)
return $row;
}
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I applied the code to a testing page (http://boyesenc.nexcess.net/testing.php) and it's pulling the information i need. I'm still not sure why the code i have is not pullying any quantity? I removed the code that subtacts items shipped and now it's just bring over a null value, yet it's populated in the database? weird, i'll keep plugging away.
"not sure why the code i have is not pullying any quantity?"
Could be lots of things -- that is why we use data visualization and test for error conditions. Have you got error_reporting(E_ALL) set?
Could be lots of things -- that is why we use data visualization and test for error conditions. Have you got error_reporting(E_ALL) set?
ASKER
I got it by removing some of the code. i'll have to play around with the tutorials you sent. They are launching the site this week, so i was pushed to a deadline. I appreciate the help though!
ASKER