cvs Feed mod
Posted: Tue May 11, 2010 11:22 am
Need help please
a have a php script that is creating cvs file from products db.
but not changeing nothing in db a want the script to do a small modification in cvs file
curently i want to and some text(same for whole column) in one of the columns in cvs file
prodname column i want to ad some text before data from db
regards
Simon
a have a php script that is creating cvs file from products db.
but not changeing nothing in db a want the script to do a small modification in cvs file
curently i want to and some text(same for whole column) in one of the columns in cvs file
prodname column i want to ad some text before data from db
Code: Select all
<?php
include("../init.php");
$shopdomain="http://www.xxxxxxxxxxxx.xxxx/";
$noImage="$shopdomain/templates/default/images/ProductDefault.gif";
//$q="SELECT DISTINCT isc_products.productid, isc_products.prodname, isc_products.prodprice, isc_products.proddesc, best_prod_images.imagefile, prod_cat.categoryid, prod_cat.xcatname, isc_brands.brandname, isc_products.prodretailprice ";
///$q.=" ,img_large.image_large,img_thumb.image_thumb,img_tiny.image_tiny ";
$q="SELECT DISTINCT isc_products.productid, isc_products.prodname, isc_products.prodprice, isc_products.proddesc, best_prod_images.imagefile, prod_cat.xcatname, isc_brands.brandname, isc_products.prodretailprice ";
$q.=" FROM ((isc_products LEFT JOIN ";
$q.=" (SELECT isc_product_images.imageprodid, isc_product_images.imageid, isc_product_images.imagefile, isc_product_images.imageisthumb, isc_product_images.imagesort ";
$q.=" FROM isc_product_images INNER JOIN ";
$q.=" (SELECT isc_product_images.imageprodid, Min(isc_product_images.imageisthumb) AS bestimage ";
$q.=" FROM isc_product_images ";
$q.=" WHERE (((isc_product_images.imagesort)<=1)) ";
$q.=" GROUP BY isc_product_images.imageprodid) as ";
$q.=" best_image ";
$q.=" ON (isc_product_images.imageprodid = best_image.imageprodid) AND (isc_product_images.imageisthumb = best_image.bestimage) ";
$q.=" WHERE (((isc_product_images.imagesort)<=1))) as ";
$q.=" best_prod_images ";
$q.=" ON isc_products.productid = best_prod_images.imageprodid) LEFT JOIN ";
/*$q.=" (SELECT prod_cats.productid, isc_categories.categoryid, prod_cats.xcatname ";
$q.=" FROM (SELECT isc_categoryassociations.productid, Max(isc_categories.catname) AS xcatname ";
$q.=" FROM isc_categoryassociations INNER JOIN isc_categories ON isc_categoryassociations.categoryid = isc_categories.categoryid ";
$q.=" GROUP BY isc_categoryassociations.productid) as prod_cats INNER JOIN isc_categories ON prod_cats.xcatname = isc_categories.catname) as prod_cat ";
*/
$q.=" (SELECT isc_categoryassociations.productid, isc_categories.catname as xcatname ";
$q.=" FROM isc_categories INNER JOIN isc_categoryassociations ON isc_categories.categoryid = isc_categoryassociations.categoryid) as prod_cat ";
$q.=" ON isc_products.productid = prod_cat.productid) LEFT JOIN isc_brands ON isc_products.prodbrandid = isc_brands.brandid ";
/*
$q.=" ) left join (SELECT isc_product_images.imageprodid, isc_product_images.imagefile as image_large FROM isc_product_images WHERE isc_product_images.imageisthumb=0) as img_large on img_large.imageprodid=isc_products.productid ";
$q.=" ) left join (SELECT isc_product_images.imageprodid, isc_product_images.imagefile as image_thumb FROM isc_product_images WHERE isc_product_images.imageisthumb=1) as img_thumb on img_thumb.imageprodid=isc_products.productid ";
$q.=" ) left join (SELECT isc_product_images.imageprodid, isc_product_images.imagefile as image_tiny FROM isc_product_images WHERE isc_product_images.imageisthumb=2) as img_tiny on img_tiny.imageprodid=isc_products.productid ";
*/
$q.=" WHERE (((isc_products.prodvisible)=1)) ";
//$query = "SELECT catname, catparentid, categoryid FROM [|PREFIX|]categories order by catsort desc, catname asc";
//header("Content-type: text/x-comma-separated-values");
//header("Content-Disposition: attachment; filename=\"feed.csv\"");
$csvdata="";
$header = "\"Product ID\",\"Product Name\",\"Product Price\",\"Product URL\",\"Product Description\",\"Product Image URL\",\"Product Category\",\"Brand Name\",\"Best Image\",\"Thumb Image\",\"Tiny Image\"\n";
$csvdata.=$header;
$result = $GLOBALS['ISC_CLASS_DB']->Query($q);
while ($row = $GLOBALS['ISC_CLASS_DB']->Fetch($result)) {
//add in a quote " replacer?
$imagefile=$noImage;
if($row['imagefile']<>"") $imagefile="$shopdomain/product_images/".$row['imagefile'];
$bestimagefile=$noImage;
$qbest="SELECT isc_product_images.imageprodid, isc_product_images.imagefile as image_large FROM isc_product_images WHERE (isc_product_images.imageisthumb=0) and (isc_product_images.imageprodid=" .$row['productid'] . ") limit 0,1";
$rbest = $GLOBALS['ISC_CLASS_DB']->Query($qbest);
while ($rowbest = $GLOBALS['ISC_CLASS_DB']->Fetch($rbest)) {$bestimagefile="$shopdomain/product_images/".$rowbest['image_large'];}
$thumbimagefile=$noImage;
$qbest="SELECT isc_product_images.imageprodid, isc_product_images.imagefile as image_thumb FROM isc_product_images WHERE (isc_product_images.imageisthumb=1) and (isc_product_images.imageprodid=" .$row['productid'] . ") limit 0,1";
$rbest = $GLOBALS['ISC_CLASS_DB']->Query($qbest);
while ($rowbest = $GLOBALS['ISC_CLASS_DB']->Fetch($rbest)) {$thumbimagefile="$shopdomain/product_images/".$rowbest['image_thumb'];}
$tinyimagefile=$noImage;
$qbest="SELECT isc_product_images.imageprodid, isc_product_images.imagefile as image_tiny FROM isc_product_images WHERE (isc_product_images.imageisthumb=2) and (isc_product_images.imageprodid=" .$row['productid'] . ") limit 0,1";
$rbest = $GLOBALS['ISC_CLASS_DB']->Query($qbest);
while ($rowbest = $GLOBALS['ISC_CLASS_DB']->Fetch($rbest)) {$tinyimagefile="$shopdomain/product_images/".$rowbest['image_tiny'];}
$desc=rawurlencode($row['proddesc']);
$desc=str_replace("%0D%0A","%3Cbr%3E",$desc);
$desc=rawurldecode($desc);
$desc=str_replace('"','""',$desc);
if(trim($desc)=="") $desc="No Description Available";
//with URL to category
//echo "\"". $row['productid'] . "\",\"". trim($row['prodname']) . "\",\"". number_format($row['prodprice'],2,".","") . "\",\"". ProdLink($row['prodname']) . "\",\"" . $desc . "\",\"". $imagefile . "\",\"". CatLink($row['categoryid'],$row['xcatname']) . "\",\"". $row['brandname'] . "\"\n";
//with straight cat name
$csvdata.= "\"". $row['productid'] . "\",\"" . ($row['prodname']) . "\",\"". number_format($row['prodprice'],2,".","") . "\",\"". ProdLink($row['prodname']) . "\",\"" . $desc . "\",\"". $imagefile . "\",\"". $row['xcatname'] . "\",\"". $row['brandname'] . "\",\"". $bestimagefile . "\",\"". $thumbimagefile . "\",\"". $tinyimagefile . "\"\n";
}
//. "\"". str_replace(chr(10),"<br>",str_replace('"','"',nl2br($row['proddesc']))) . "\", "
$mimetype="text/x-comma-separated-values";
header("Pragma: public"); // required
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Cache-Control: private",false); // required for certain browsers
header("Content-Transfer-Encoding: binary");
header("Content-Type: " . $mimetype);
//header("Content-Length: " . $filesize);
//header("Content-type: text/x-comma-separated-values");
header("Content-Disposition: attachment; filename=\"feed.csv\"");
echo $csvdata;
Simon