Solved

trying to get value from mysql data

Posted on 2010-09-09
7
803 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 108

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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 108

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 108

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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
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 …

707 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now