Love Quote

Yes I do have a lover.
She will be with me anywhere/anytime.
And her name is failure.

Being Human

No Matter in which religion you are,

Be a Human First

#BeingHuman

Memorable Trip :) :) :)

Avalancheeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee.. 

AVN's Quote #1

If Life doesn't Favour you,

It means you are leading a PERFECT life . . .

#LiveUrLife

Custom Script to limit and Export product details in Magento

The following script shows how to limit and the export product details in magento to csv/xls format..



<?php 
ob_start();
require_once('./../app/Mage.php');

function test($cats)
{
$category="";
foreach ($cats as $category_id) 
       {
$cat = Mage::getModel('catalog/category')->load($category_id) ;
$category.=($cat->getName()).",";
}
return $categories = substr($category,0,-1);
}

Mage::app();

$count = 10;  // to get value of 10 records
$startAfter = 0; // to start from 0
$products = Mage::getModel("catalog/product")->getCollection();
$products->addAttributeToSelect('*')->getSelect()->limit($count,$startAfter);                   /      //limit(COUNT,NEXT_RECORD_OF )

//header for the table names

$csvHeader =array("id", "SKU", "Product Name", "category", "price", "Special Price", "Special Price From", "Special Price To", "image", "store", "Description", "short Description", "Created at", "Updated at", "Weight", "Product Type", "Url path", "Url Key", "Color", "Manufacturing Country", "Quantity", "Status", "Visibility", "Meta Title", "Meta Description", "Meta Keywords", "tax class", "Min Sale Qty", "Max Sale Qty", "Manufacturer suggested retail price");

foreach($csvHeader as $head)
{
$header.= $head."\t ";
}

$data="";
foreach ($products as $product)
{
        $id = $product->getId();
$sku = $product->getSku();
$names = $product->getName();
$store = $product->getStore()->getName();
$images = $product->getImageUrl();
$prices = $product->getPrice();
$sPrice = $product->getSpecialPrice();
$specialTo = $product->getSpecialToDate();
$specialFrom = $product->getSpecialFromDate();
$desc = preg_replace('/[\n\r]+/', ',', trim($product->getDescription()));
$sDescrip = preg_replace('/[\n\r]+/', ',', trim($product->getShortDescription()));
$creation = $product->getCreatedAt();
$updatedAt = $product->getUpdatedAt();
$type = $product->getTypeID();
$weight = $product->getWeight();
$urlKey = $product->getUrl_key();
$url = $product->getProductUrl();
$color = $product->getAttributeText('color');
$manu = $product->getAttributeText('manufacturer');
$status = $product->getAttributeText('status');
$visibility = $product->getAttributeText('visibility');
$metaTitle = $product->getMetaTitle();
$metaDesc = $product->getMetaDescription();
$metaKeywords = $product->getMetaKeyword();
$taxClassId = $product->getTaxClassId();
$qty = Mage::getModel('cataloginventory/stock_item')->loadByProduct($product)->getQty();
$productQuantity = Mage::getModel("cataloginventory/stock_item")->loadByProduct($product->getId());
$minSaleQty = $productQuantity->getMinSaleQty();
$maxSaleQty = $productQuantity->getMaxSaleQty();
$msrp = $product->getMsrp();
$cats = $product->getCategoryIds();
$categories = test($cats);

$data.=$id."\t".$sku ."\t".$names ."\t".$categories ."\t".$prices ."\t".$sPrice ."\t".$specialFrom ."\t".$specialTo ."\t".$images ."\t".$store ."\t".$desc ."\t".$sDescrip ."\t".$creation ."\t".$updatedAt ."\t".$weight ."\t".$type ."\t".$url ."\t".$urlKey ."\t".$color ."\t".$manu ."\t".$qty ."\t".$status ."\t".$visibility ."\t".$metaTitle ."\t".$metaDesc ."\t".$metaKeywords ."\t".$taxClassId ."\t".$minSaleQty ."\t".$maxSaleQty ."\t".$msrp ."\t".$cate."\n";
}

header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=reports.xls"); //filename: report.xls
header("Pragma: no-cache");
header("Expires: 0");
print "$header\n$data";
ob_flush();
?>

Importing data from CSV file to mysql database using php..........

<?php
//database connection details
$host = "localhost";
$user = "root"; //user of mysql
$password = "";
$database = "sample"; //database name
try {
if (mysql_connect($host, $user, $password)) {
     mysql_select_db ("sample");
}
else {
throw new Exception('Unable to connect');
}
}
catch(Exception $e) {
echo $e->getMessage();
}
   
           define('CSV_PATH','C:/wamp/www/');   // path where your CSV file is located
          $csv_file = CSV_PATH . "export.csv"; // Name of your CSV file
       
         if (($getfile = fopen($csv_file, "r")) !== FALSE) {
            $data = fgetcsv($getfile, 1000, ",");
           while (($data = fgetcsv($getfile, 1000, ",")) !== FALSE) {
                $num = count($data);
                for ($c=0; $c < $num; $c++) {
                     $result = $data;
                     $str = implode(",", $result);
                     $slice = explode(",", $str);        
                     $col1 = $slice[0];
                     $col2 = $slice[1];
     $col3 = $slice[2];

                    // SQL Query to insert data into DataBase
                     $query = "INSERT INTO csv_to_db VALUES('".$col1."','".$col2."','".$col3."')";
                   // csv file consists of 3 fields, hence using 3 columns(i.e., col1,col2,col3)
                     $s=mysql_query($query);
                 }
       }
}
echo "File data successfully imported to database!!";
mysql_close();
?>

~$~$~$~$~$~$~$~$~$~$~$~$~$~$~$~$~$~$~$~$~$~$~$~$~$~$~$~$~$~$~$~$~$~$~$~$~$~$

Simple way of Exporting database to Csv format using php..

For Php Newbie.............

simple way of Exporting Data in Mysql Table To Csv Format:

<?php
$host = "localhost"; //hostname
$user = "root"; //admin
$password = ""; //password of Mysql
$database = "vino"; //database name
try {
if (mysql_connect($host, $user, $password)) {
dbconnection();
}
else {
throw new Exception('Unable to connect');
}
}

catch(Exception $e) {
echo $e->getMessage();
}

function dbconnection() {
mysql_select_db ("vino");
}

$query = ('SELECT * FROM tablename');
$result = mysql_query($query) or die(mysql_error());
header ('Content-Type: text/csv');
header ('Content-Disposition: attachment;filename=export.csv');
$row = mysql_fetch_assoc($result);

if ($row) {
echocsv(array_keys($row));
}

while ($row) {
echocsv($row);
$row = mysql_fetch_assoc($result);
}

function echocsv($fields) {
$separator = '';
foreach ($fields as $field) {
if (preg_match('/\\r|\\n|,|"/', $field)) {
$field = '"' . str_replace('"', '""', $field) . '"';
}
echo $separator . $field;
$separator = ',';
}
echo "\r\n";
}
?>

~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*