[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

trying to get value from mysql data

Posted on 2010-09-09
7
Medium Priority
?
819 Views
Last Modified: 2012-05-10
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;
	
?>

Open in new window

// 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;
	
?>

Open in new window

sales-flat-order-item.xls
0
Comment
Question by:remedy_rider
  • 3
  • 3
7 Comments
 
LVL 17

Assisted Solution

by:nanharbison
nanharbison earned 400 total points
ID: 33642448
I see that in your fieldlist array you have the value of quantity, but I don't see a value called quantity in your query, and I think all the values in the field list array are in the query, right?
0
 

Author Comment

by:remedy_rider
ID: 33646331
I see what you mean, I changed "sales_flat_order_item.qty_ordered AS qty_ordered," to "sales_flat_order_item.qty_ordered AS quantity," and it still doesn't work. :( It has to be something simple. it's drivin' me nuts.
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 33646459
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/
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;
}

Open in new window

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 111

Accepted Solution

by:
Ray Paseur earned 1600 total points
ID: 33646506
Here is a little teaching example that shows how do a few of the basics in MySQL -- including how to run a query, check for errors, and visualize the rows of the results set.  Try adapting those principles to your application and see if you don't find the issues easier to isolate and correct.  Best of luck with it, ~Ray
<?php // RAY_mysql_example.php
error_reporting(E_ALL);


// IMPORTANT PAGES FROM THE MANUALS
// MAN PAGE: http://us2.php.net/manual/en/ref.mysql.php
// MAN PAGE: http://us2.php.net/manual/en/mysql.installation.php
// MAN PAGE: http://us.php.net/manual/en/function.mysql-error.php


// CONNECTION AND SELECTION VARIABLES FOR THE DATABASE
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";        // GET THESE FROM YOUR HOSTING COMPANY
$db_user = "??";
$db_word = "??";

// OPEN A CONNECTION TO THE DATA BASE SERVER
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-connect.php
if (!$db_connection = mysql_connect("$db_host", "$db_user", "$db_word"))
{
   $errmsg = mysql_errno() . ' ' . mysql_error();
   echo "<br/>NO DB CONNECTION: ";
   echo "<br/> $errmsg <br/>";
}

// SELECT THE MYSQL DATA BASE
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-select-db.php
if (!$db_sel = mysql_select_db($db_name, $db_connection))
{
   $errmsg = mysql_errno() . ' ' . mysql_error();
   echo "<br/>NO DB SELECTION: ";
   echo "<br/> $errmsg <br/>";
   die('NO DATA BASE');
}
// IF WE GOT THIS FAR WE CAN DO QUERIES




// ESCAPING A DATA FIELD FOR USE IN MYSQL QUERIES
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-real-escape-string.php
$safe_username = mysql_real_escape_string($_POST["username"]);




// CREATING AND SENDING A SELECT QUERY AND TESTING THE RESULTS
// MAN PAGE:http://us2.php.net/manual/en/function.mysql-query.php
$sql = "SELECT id FROM my_table WHERE username='$safe_username'";
$res = mysql_query($sql);

// IF mysql_query() RETURNS FALSE, GET THE ERROR REASONS
// MAN PAGE: http://us.php.net/manual/en/function.mysql-error.php
if (!$res)
{
   $errmsg = mysql_errno() . ' ' . mysql_error();
   echo "<br/>QUERY FAIL: ";
   echo "<br/>$sql <br/>";
   die($errmsg);
}
// IF WE GET THIS FAR, THE QUERY SUCCEEDED AND WE HAVE A RESOURCE-ID IN $res SO WE CAN NOW USE $res IN OTHER MYSQL FUNCTIONS




// DETERMINE HOW MANY ROWS OF RESULTS WE GOT
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-num-rows.php
$num = mysql_num_rows($res);
if (!$num)
{
   echo "<br/>QUERY FOUND NO DATA: ";
   echo "<br/>$sql <br/>";
}
else
{
   echo "<br/>QUERY FOUND $num ROWS OF DATA ";
   echo "<br/>$sql <br/>";
}




// ITERATE OVER THE RESULTS SET TO SHOW WHAT WE FOUND
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-fetch-assoc.php
echo "<pre>\n"; // MAKE IT EASY TO READ
while ($row = mysql_fetch_assoc($res))
{
   // MAN PAGE: http://us2.php.net/manual/en/function.var-dump.php
   var_dump($row);
}

Open in new window

0
 

Author Comment

by:remedy_rider
ID: 33664005
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.
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 33665016
"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?
0
 

Author Comment

by:remedy_rider
ID: 33665408
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!
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
The title says it all. Writing any type of PHP Application or API code that provides high throughput, while under a heavy load, seems to be an arcane art form (Black Magic). This article aims to provide some general guidelines for producing this typ…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses
Course of the Month19 days, 20 hours left to enroll

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question