Solved

trying to get value from mysql data

Posted on 2010-09-09
7
805 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 100 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 109

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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 109

Accepted Solution

by:
Ray Paseur earned 400 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 109

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

805 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