PHP/Query For Export To CSV

[[email protected]]

Hello Zen Forum,

Im fairly new to the forum but hope someone can give me a hand im struggling..

my code is below, i am exporting to a csv file from the zen cart database

$products_cost =
"select p.products_id, pd.products_name, p.products_quantity, ".
"p.products_cost, (p.products_quantity*p.products_cost) AS products_total_cost, ".
"p.products_type ".
"from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd ".
"where p.products_id = pd.products_id and pd.language_id='" . $_SESSION['languages_id'] . "' ".
"order by (p.products_cost) DESC, p.products_cost desc, pd.products_name";

$products_stock = $db->Execute($products_cost);

//Start Of Output for Stock File

$output = array();


$output["tx_type"]='ST';


$output["Stock_Code"]= $products_stock->fields['products_id'];


$output["Qty"]= $products_stock->fields['products_quantity'];


$output["Description"]= $products_stock->fields['products_name'];


$output["CostPriceEx"]= $products_stock->fields['products_cost'];


$output["TotalCostEx"]= $products_stock->fields['products_cost'] * $products_stock->fields['products_quantity'];

zen_sages_fwrite($output);

$stimer = microtime_float();

When running the code i get an output like below, but instead of showing each product from the database it is showing the same one ??

ST,1149,10,350gsm A6 Glossy Postcards,290.0000,2900
ST,1149,10,350gsm A6 Glossy Postcards,290.0000,2900
ST,1149,10,350gsm A6 Glossy Postcards,290.0000,2900
ST,1149,10,350gsm A6 Glossy Postcards,290.0000,2900
ST,1149,10,350gsm A6 Glossy Postcards,290.0000,2900
ST,1149,10,350gsm A6 Glossy Postcards,290.0000,2900
ST,1149,10,350gsm A6 Glossy Postcards,290.0000,2900
ST,1149,10,350gsm A6 Glossy Postcards,290.0000,2900
ST,1149,10,350gsm A6 Glossy Postcards,290.0000,2900
ST,1149,10,350gsm A6 Glossy Postcards,290.0000,2900
ST,1149,10,350gsm A6 Glossy Postcards,290.0000,2900

I have tried adding $products_stock->MoveNext();

But dosent seam to have any affect,

Thanking you all in advance

Russell

[jc8125]

Firstly, when you're outputting your results - you're not looping through the results.. you'd probably want the output inside of something like:

while (!$products_stock->EOF){
//do something for each product returned
$products_stock->MoveNext();
}

But more importantly - have you looked at the free easy populate plugin? It basically does what you're attempting to do, and it's already written.. :) You can add fields to it if needed, but it seems like you're re-inventing the wheel (unless i'm missing something). If this is for an import into another program, you might find it easier to manipulate the order of the fields/file that easy populate creates, instead of starting from scratch (just my 2¢)

[[email protected]]

Firstly, when you're outputting your results - you're not looping through the results.. you'd probably want the output inside of something like:

while (!$products_stock->EOF){
//do something for each product returned
$products_stock->MoveNext();
}

But more importantly - have you looked at the free easy populate plugin? It basically does what you're attempting to do, and it's already written.. :) You can add fields to it if needed, but it seems like you're re-inventing the wheel (unless i'm missing something). If this is for an import into another program, you might find it easier to manipulate the order of the fields/file that easy populate creates, instead of starting from scratch (just my 2¢)

Hi, many thanks for your reply, i thought the same and add EOF, now when i run the last product is looping and like this


ST,387,0,Wolf Bench Grinder - WBG200/MD200F - Home - Tools - DIY,0.0000,0
ST,360,0,Wolfcraft TS 800 Adjustable Trolley - Home - Garden,0.0000,0
ST,116,0,WWE Ruthless Aggression 6 Pack - Wrestling Game - RARE - Retro,0.0000,0
ST,419,0,Zinc Ion XY Black Folding Scooter - Outdoors - Toy,0.0000,0
ST,220,0,Zoobles Mama & Zoobling Trinka and Tito & Tonia #306 Play Set,0.0000,0
ST,220,0,Zoobles Mama & Zoobling Trinka and Tito & Tonia #306 Play Set,0.0000,0
ST,220,0,Zoobles Mama & Zoobling Trinka and Tito & Tonia #306 Play Set,0.0000,0
ST,220,0,Zoobles Mama & Zoobling Trinka and Tito & Tonia #306 Play Set,0.0000,0
ST,220,0,Zoobles Mama & Zoobling Trinka and Tito & Tonia #306 Play Set,0.0000,0
ST,220,0,Zoobles Mama & Zoobling Trinka and Tito & Tonia #306 Play Set,0.0000,0
ST,220,0,Zoobles Mama & Zoobling Trinka and Tito & Tonia #306 Play Set,0.0000,0
ST,220,0,Zoobles Mama & Zoobling Trinka and Tito & Tonia #306 Play Set,0.0000,0
ST,220,0,Zoobles Mama & Zoobling Trinka and Tito & Tonia #306 Play Set,0.0000,0
ST,220,0,Zoobles Mama & Zoobling Trinka and Tito & Tonia #306 Play Set,0.0000,0
ST,220,0,Zoobles Mama & Zoobling Trinka and Tito & Tonia #306 Play Set,0.0000,0
ST,220,0,Zoobles Mama & Zoobling Trinka and Tito & Tonia #306 Play Set,0.0000,0

the last product loops thousands of times,

Any idea how to stop the loop when the last product has been displayed, i have tried

for($i=0;$i<$products_stock->rowCount();$i++) {
$output = array();
$output["tx_type"]='ST';
$output["Stock_Code"]= $products_stock->fields['products_id'];
$output["Qty"]= $products_stock->fields['products_quantity'];
$output["Description"]= $products_stock->fields['products_name'];
$output["CostPriceEx"]= $products_stock->fields['products_cost'];
$output["TotalCostEx"]= $products_stock->fields['products_cost'] * $products_stock->fields['products_quantity'];

zen_sages_fwrite($output);
$products_stock->MoveNext();
}

but it white screens, i really appreciate any help

Thank you

Russell

[[email protected]]

for those that would like to see the full php file, please see below heavily modified from the sage intergration plugin, just need this last bit to work and its complete....


<?php
/**
* sage.php
*
* @package sage
* @copyright Portions Copyright 2003-2006 Zen Cart Development Team
* @copyright Portions Copyright 2003 osCommerce
* @copyright Portions Copyright 2003 Andrew Berezin
* @license http://www.zen-cart.com/license/2_0.txt GNU Public License V2.0
* @version $Id: sage.php, v 1.0 15.8.2007 5:54 James Fitch [email protected]$
*/
/*

TODO List
add in the function to export all stock from system to this program aswell??
*/
require('includes/application_top.php');

@define('SAGE_DIRECTORY', 'sage/');
@define('SAGE_OUTPUT_BUFFER_MAXSIZE', 1024*1024);
@define('SAGE_USE_CPATH', 'false');

define('NL', "<br />\n");

$count=0;

require(zen_get_file_directory(DIR_WS_LANGUAGES . $_SESSION['language'] .'/', 'sage.php', 'false'));

echo TEXT_SAGE_STARTED . NL;
echo TEXT_SAGE_FILE_LOCATION . DIR_FS_CATALOG . SAGE_DIRECTORY . SAGE_OUTPUT_FILENAME2 . NL;
echo TEXT_SAGE_FILE_LOCATION . DIR_FS_CATALOG . SAGE_DIRECTORY . SAGE_OUTPUT_FILENAME . NL;

if (isset($_GET['feed']) && $_GET['feed'] == "yes") {
if (is_dir(DIR_FS_CATALOG . SAGE_DIRECTORY)) {
if (!is_writeable(DIR_FS_CATALOG . SAGE_DIRECTORY)) {
echo ERROR_SAGE_DIRECTORY_NOT_WRITEABLE . NL;
die;
}
} else {
echo ERROR_SAGE_DIRECTORY_DOES_NOT_EXIST . NL;
die;
}

$stimer_feed = microtime_float();
@set_time_limit(0);

$output_buffer = "";
$output_buffer2 = "";

if(!zen_sages_fwrite('', 'open')) {
echo ERROR_SAGE_OPEN_FILE . NL;
die;
}





if(!zen_sages_fwrite2('', 'open')) {
echo ERROR_SAGE_OPEN_FILE . NL;
die;
}


$sage_update_query = "SELECT *
FROM " .SAGE_UPDATE. "";
//$ordereds = $db->Execute($sage_update_query);






$datestart = SAGE_STARTSDATE;

$dateend = SAGE_ENDSDATE;

if ($datestart == "null"){

$datestart = $ordereds ->fields['last_update'];
}else{$datestart=$datestart." 00:00:01";
}

if ($dateend == "null"){

$yesterday = mktime(0,0,0,date("m"),date("d")-1,date("Y"));
$dateend = date("Y-m-d", $yesterday);
}



$datestart='\''.$datestart.'\'';

$dateend=$dateend." 23:59:59";
$dateend='\''.$dateend.'\'';

$statustosages =SAGE_STATUS;

$orders_query = "SELECT *
FROM " . TABLE_ORDERS . " WHERE orders_status <= $statustosages AND date_purchased >= $datestart AND date_purchased <= $dateend
ORDER BY date_purchased ASC";


// $refund_query = "SELECT * FROM " . TABLE_SO_REFUNDS . "

//ORDER BY orders_id ASC";

// $refund = $db->Execute($refund_query);



$stimer = microtime_float();

$orders = $db->Execute($orders_query);

$timer['$orders->mySQL'] += microtime_float()-$stimer;



while (!$orders->EOF) { //1

$orderid=$orders->fields['orders_id'];

##################################################################### below checks for any postage charges - these will be added across number of products
$ship=200;
$ship=3;



$post_query="SELECT *
FROM " . TABLE_ORDERS_TOTAL .

" WHERE

orders_id = $orderid and


class LIKE '%ot_shipping%' "

;




$Postagecosts = $db->Execute($post_query);

$postageprice = $Postagecosts->fields['value'];

###################################################################### below checks to see if total = total of full price...

$count=0; //used for other things further down
$proid_query="SELECT *
FROM " . TABLE_ORDERS_PRODUCTS . " as t1 WHERE t1.orders_id = $orderid";

$Orderedproduct = $db->Execute($proid_query);
// get all products from that order

while (!$Orderedproduct->EOF) { //5

$prodprice=$Orderedproduct->fields['final_price'];

if ($Orderedproduct->fields[products_tax] > 0) {

$prodprice=($Orderedproduct->fields['final_price'] * ($Orderedproduct->fields[products_tax]/100)) +$Orderedproduct->fields['final_price'];

}
$prodprice = ($Orderedproduct->fields['products_quantity'])*$prodprice;
$totalprice=$prodprice+$totalprice;
$count=$count+1; // count how many products in order, handy to use as reference for other calcs....
$Orderedproduct->MoveNext();

} //5

################################################################################ ####
//get total price from file and compare that all items add up to this. Doing it this way ensures no issues with tax.

$discount=1.0;

If ($totalprice > $orders->fields['order_total']) {

$change=($totalprice -$orders->fields['order_total'] ) /$totalprice;

$discount=$discount-$change;
}

$totalprice=0;

#######################################################//end of price adjustment


$products_cost =
"select p.products_id, pd.products_name, p.products_quantity, ".
"p.products_cost, (p.products_quantity*p.products_cost) AS products_total_cost, ".
"p.products_type ".
"from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd ".
"where p.products_id = pd.products_id and pd.language_id='" . $_SESSION['languages_id'] . "' ".
"order by (p.products_cost) DESC, p.products_cost desc, pd.products_name";



$products_stock = $db->Execute($products_cost);



//Start Of Output for Stock File

while(!$products_sock->EOF) {
$output = array();
$output["tx_type"]='ST';
$output["Stock_Code"]= $products_stock->fields['products_id'];
$output["Qty"]= $products_stock->fields['products_quantity'];
$output["Description"]= $products_stock->fields['products_name'];
$output["CostPriceEx"]= $products_stock->fields['products_cost'];
$output["TotalCostEx"]= $products_stock->fields['products_cost'] * $products_stock->fields['products_quantity'];

zen_sages_fwrite($output);
$products_stock->MoveNext();
}
$stimer = microtime_float();








##################################################### switch statement to take account of exports. i.e if not UK/ireland then add VAT using T1 russ

$taxid=$orders->fields['delivery_country'];

switch ($taxid) {

case "United Kingdom":
$taxcode = "T1";
$netprice= $totprice;
$taxam=($orders->fields['order_total']/1.200) ;


break;

case "Ireland":
$taxcode = "T1";
$netprice= $totprice;
$taxam=($orders->fields['order_total']/1.200) ;
break;

default:
$taxcode = "T0";
$netprice= $totprice;
$taxam=($orders->fields['order_total']);
}

############################################### identify payment type

$paytype=$orders->fields['payment_method'];

switch ($paytype) {
case "Paypal IPN":
$purchacc = SAGE_PAYPAL;
break;
case "Paypal":
$purchacc = SAGE_EBAY;
break;
case "Credit Card":
$purchacc = 1233;
break;
case "Credit Card / Paypal":
$purchacc = SAGE_EBAY;
break;
case "Credit Card/ Paypal":
$purchacc = SAGE_EBAY;
break;
case "Cheque/Money Order":
$purchacc = SAGE_CHEQ;
break;
case "Ebay Paypal":
$purchacc = 1230;
break;
case "Credit/Debit Card (Secured by Sage Pay)":
$purchacc = 1232;
break;
case "Amazon":
$purchacc = 1231;
break;
case "Amazon FBA":
$purchacc = 1235;
break;
case "Cash":
$purchacc = 1260;
break;
case "Cheque":
$purchacc = 1200;
break;
case "Group Payment - Crownlea":
$purchacc = 2500;
break;
case "Group Payment - BNM UK":
$purchacc = 2501;
break;
case "Group Payment - BNM LTD":
$purchacc = 2502;
break;
case "Group Payment - BNM Ireland":
$purchacc = 2503;
break;
case "Group Payment - Protecta Screen":
$purchacc = 2504;
break;
case "Group Payment - OSSSC":
$purchacc = 2506;
break;
case "Group Payment - Package-IN":
$purchacc = 2507;
break;
case "Group Payment - TIC":
$purchacc = 2508;
break;
case "Group Payment - Billaman":
$purchacc = 2509;
break;

default:
$purchacc = 0000;

}

$dateprod=$orders->fields['date_purchased'];

$arr=split(" ",$dateprod); // splitting the array
$dateprod=$arr[0]; // first element of the array is month
$arr=split("-",$dateprod); // splitting the array
$yyyy=$arr[0];
$mm=$arr[1];
$dd=$arr[2];

$output2 = array();
$output2["tx_type"]=SAGE_TX2;
$output2["Purchase Acc"]=$purchacc;
$output2["nominal_code"]='4000';
$output2["dept_num"]='1';
$output2["Transaction_Date"] = $dd.'/'.$mm.'/'.$yyyy;
$output2["Reference"] = $orders->fields['orders_id'];
$output2["Details"]='Paid By -'.$orders->fields['payment_method'].' ** Customer- '.$orders->fields['customers_name'];
$output2["Tax Code"]=$taxcode;
$output2["TaxAmmount"]=number_format ($taxam, 2, '.', '');
$output2["postageprice"] = $Postagecosts->fields['value'];





$output2["NetAmmount"]= $orders->fields['order_total'] - number_format ($taxam, 2, '.', '');
$output2["order_total"] = $orders->fields['order_total'];










zen_sages_fwrite2($output2);

$totprice=0;

$orders->MoveNext();

$timer['$orders->mySQL'] += microtime_float()-$stimer;

} //1
zen_sages_fwrite('', 'close');
zen_sages_fwrite2('', 'close');
$date_updated = date('Y-m-d');
$date_updated=$date_updated." 00:00:01"; //Current date in US format $yyyy.'/'.$mm.'/'.$dd;

//$sage_updated_query = "UPDATE
//" . sage_update . " SET last_update = '$date_updated'";
//$ordered = $db->Execute($sage_updated_query);



// @chmod(DIR_FS_CATALOG . SAGE_DIRECTORY . SAGE_OUTPUT_FILENAME, 0644);

$timer_feed = microtime_float()-$stimer_feed;
$percent_other = 0;
$time_other = $timer_feed;
if(SAGE_STAT === true) {
echo 'Execution time:<br />';
echo '<table cellspace="2" border="1px">';
echo '<tr><td>Function</td><td>Execution time</td><td>Percent</td></tr>';
foreach($timer as $func => $time) {
$percent_total += ($time*100)/$timer_feed;
$time_other -= $time;
echo '<tr><td>' . $func . '</td><td align="right">' . number_format($time, 6) . '</td><td align="right">' . round(($time*100)/$timer_feed, 2) . '%</td></tr>';
}
echo '<tr><td><i>Other</i></td><td align="right"><i>' . number_format($time_other, 6) . '</i></td><td align="right"><i>' . round(($time_other*100)/$timer_feed, 2) . '%</i></td></tr>';
//echo '<tr><td><b>Total</b></td><td align="right"><b>' . number_format($timer_feed, 6) . '</b></td><td align="right"><b>' . round(($timer_feed*100)/$timer_feed, 2) . '%</b></td></tr>';
echo '</table>';
}
//$orders->RecordCount()
echo '<br>' . TEXT_SAGE_FEED_COMPLETE . ' ' . SAGE_TIME_TAKEN . ' ' . sprintf("%f " . TEXT_SAGE_FEED_SECONDS, number_format($timer_feed, 6) ) . ' ' . $count . TEXT_SAGE_FEED_RECORDS . NL . '<br>';
echo $datestart.' to '.$dateend.NL;







echo '<b><br>' . 'To View SAGE 1000 Transaction File <a title="Click Here" href="' . DIR_WS_CATALOG . SAGE_DIRECTORY . SAGE_OUTPUT_FILENAME2 . '" target="_new"><font color="#0000ff"> Click Here</font></a><br>';

echo'<b><br>'. 'To View SAGE 1000 Stock File <a title="Click Here" href="' . DIR_WS_CATALOG . SAGE_DIRECTORY . SAGE_OUTPUT_FILENAME . '" target="_new"><font color="#0000ff"> Click Here</font></a><br><br><br>';

//echo zen_image(DIR_WS_IMAGES . 'sage1000.jpg', '', 300, 300, '<br><br>');

echo zen_image(DIR_WS_IMAGES . 'sagelogo.jpg', 300, 300);





}




function zen_sages_fwrite($output, $op='fwrite') {
global $timer;$stimer = microtime_float();
static $fp;
static $output_buffer = "";
static $title_row = false;
switch ($op) {
case 'open':
$retval = $fp = fopen(DIR_FS_CATALOG . SAGE_DIRECTORY . SAGE_OUTPUT_FILENAME, "wb");
break;
case 'fwrite':
if(!$title_row) {
$title_row = $output;
}
$buf = array();
foreach($title_row as $key=>$val) {
$buf[] = (isset($output[$key]) ? $output[$key] : '');
// var_dump($key, $output[$key]);echo '<br />';
}
$output = implode(",", $buf);
if(strlen($output_buffer) > SAGE_OUTPUT_BUFFER_MAXSIZE) {
$retval = fwrite($fp, $output_buffer, strlen($output_buffer));
$output_buffer = "";
}
$output = rtrim($output) . "\n";
if(strtolower(CHARSET) != 'utf-8')
$output_buffer .= utf8_encode($output);
else
$output_buffer .= $output;
break;
case 'close':
if(strlen($output_buffer) > 0) {
$retval = fwrite($fp, $output_buffer, strlen($output_buffer));
$output_buffer = "";
}
fclose($fp);
break;
}
$timer[__FUNCTION__] += microtime_float()-$stimer;
return $retval;
}

function zen_sages_fwrite2($output2, $op='fwrite') {
static $fp2;
static $output_buffer2 = "";
static $title_row2 = false;
switch ($op) {
case 'open':
$retval = $fp2 = fopen(DIR_FS_CATALOG . SAGE_DIRECTORY . SAGE_OUTPUT_FILENAME2, "wb");
break;
case 'fwrite':
if(!$title_row2) {
$title_row2 = $output2;
}
$buf = array();
foreach($title_row2 as $key=>$val) {
$buf[] = (isset($output2[$key]) ? $output2[$key] : '');
// var_dump($key, $output2[$key]);echo '<br />';
}
$output2 = implode(",", $buf);
if(strlen($output_buffer2) > SAGE_OUTPUT_BUFFER_MAXSIZE) {
$retval = fwrite($fp2, $output_buffer2, strlen($output_buffer2));
$output_buffer2 = "";
}
$output2 = rtrim($output2) . "\n";
if(strtolower(CHARSET) != 'utf-8')
$output_buffer2 .= utf8_encode($output2);
else
$output_buffer2 .= $output2;
break;

case 'close':
if(strlen($output_buffer2) > 0) {
$retval = fwrite($fp2, $output_buffer2, strlen($output_buffer2));
$output_buffer2 = "";
}
fclose($fp2);
break;
}
return $retval;
}





function zen_sages_sanita($str, $rt=false) {
global $timer;$stimer = microtime_float();
$str = strip_tags($str);
$str = str_replace(array("\t" , "\n", "\r"), ' ', $str);
$str = preg_replace('/\s\s+/', ' ', $str);
// $str = str_replace(array("&reg;", "®", "&copy;", "©", "&trade;", "™"), ' ', $str);
$str = htmlentities(html_entity_decode($str));
$in = $out = array();
$in[] = "&reg;"; $out[] = '(r)';
$in[] = "&copy;"; $out[] = '(c)';
$in[] = "&trade;"; $out[] = '(tm)';
// $str = str_replace($in, $out, $str);
if($rt) $str = str_replace(array('>'), ' ', $str);
$str = trim($str);
$str = "\"$str\"";

$timer[__FUNCTION__] += microtime_float()-$stimer;
return $str;
}


function ftp_get_error_from_ob() {
$out = ob_get_contents();
ob_end_clean();
$out = str_replace(array('\\', '<!--error-->', '<br>', '<br />', "\n", 'in <b>'),array('/', '', '', '', '', ''),$out);
if(strpos($out, DIR_FS_CATALOG) !== false){
$out = substr($out, 0, strpos($out, DIR_FS_CATALOG));
}
return $out;
}

function microtime_float() {
list($usec, $sec) = explode(" ", microtime());
return ((float)$usec + (float)$sec);
}
?>

[jc8125]

you've got a typo..

while(!$products_sock->EOF) {
$output = array();
$output["tx_type"]='ST';
$output["Stock_Code"]= $products_stock->fields['products_id'];
$output["Qty"]= $products_stock->fields['products_quantity'];
$output["Description"]= $products_stock->fields['products_name'];
$output["CostPriceEx"]= $products_stock->fields['products_cost'];
$output["TotalCostEx"]= $products_stock->fields['products_cost'] * $products_stock->fields['products_quantity'];

zen_sages_fwrite($output);
$products_stock->MoveNext();
}
$stimer = microtime_float();

[[email protected]]

you've got a typo..

Hi thanks for that i have now changed to while(!$products_stock->EOF) {

but it produces the same results. would you have any ideas ??

Many thanks

Russell

[[email protected]]

Hi All,

I managed to work it out, so thought id post in case any one else would want to see

zen_sages_fwrite2($output2);

$totprice=0;

$orders->MoveNext();

$timer['$orders->mySQL'] += microtime_float()-$stimer;

}



$products_cost =
"select p.products_id, pd.products_name, p.products_quantity, ".
"p.products_cost, (p.products_quantity*p.products_cost) AS products_total_cost, ".
"p.products_type ".
"from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd ".
"where p.products_id = pd.products_id and pd.language_id='" . $_SESSION['languages_id'] . "' ".
"order by (p.products_quantity) DESC, p.products_cost desc, pd.products_name";



$products_stock = $db->Execute($products_cost);



//Start Of Output for Stock File

while(!$products_stock->EOF) {
$output = array();
$output["tx_type"]='ST';
$output["Stock_Code"]= $products_stock->fields['products_id'];
$output["Qty"]= $products_stock->fields['products_quantity'];
$output["Description"]= $products_stock->fields['products_name'];
$output["CostPriceEx"]= $products_stock->fields['products_cost'];
$output["TotalCostEx"]= $products_stock->fields['products_cost'] * $products_stock->fields['products_quantity'];

zen_sages_fwrite($output);
$products_stock->MoveNext();

}
$stimer = microtime_float();


//1
zen_sages_fwrite('', 'close');
zen_sages_fwrite2('', 'close');
$date_updated = date('Y-m-d');