How can i export multiple table values as a single file specific store

Question

I need to export different tables values as same file,

Name - sku - category url - image path

Name:

category_products_entity_varchar :: [ value as name] {attribute_id = 71}

Sku:

catalog_products_entity :: [sku]

category url:

category_products_entity_varchar [value as category] {attribute_id = 57}

image path :

category_products_entity_media_gallery [value as image path]

Answer 1

QUERY :

SELECT cpev.value as Name,cpe.sku,gp.value category_url, ea.value image_path FROM catalog_product_entity cpe inner join (SELECT entity_id,value FROM catalog_product_entity_varchar WHERE attribute_id="71") cpev on cpev.entity_id = cpe.entity_id inner join (SELECT entity_id,value FROM catalog_product_entity_media_gallery) ea on ea.entity_id=cpe.entity_id inner join ( SELECT category_id, product_id FROM catalog_category_product) eavv on cpe.entity_id =eavv.product_id inner join ( SELECT entity_id,value FROM catalog_category_entity_varchar WHERE attribute_id='57') gp on eavv.category_id=gp.entity_id LIMIT 0 , 30

SCRIPT :

<?php

ini_set("memory_limit","-1");
ini_set('max_execution_time', 180000); 

error_reporting(E_ALL | E_STRICT);

ini_set('display_errors', 1);

require_once('app/Mage.php');
Mage::app();

$products = Mage::getModel("catalog/product")->getCollection()->addAttributeToSelect('name');
$products->addAttributeToFilter('status', 1);

$fopen = fopen('products.csv', 'w');
$csvHeader = array("Name","sku","Category","image", "GalleryImages");// Add the fields you need to export
fputcsv( $fopen , $csvHeader,",");

foreach ($products as $product){ 

   $_product = Mage::getModel("catalog/product")->load($product->getId());
   $gallery_images=$_product->getMediaGalleryImages();

   $image =Mage::getBaseUrl('media')."catalog/product".$_product->getImage();

   foreach($gallery_images as $g_image) {
       $g_images.=$g_image['url']."\n";
   }
   $sku = $_product->getSku();
   $name= $_product->getName();
   $categories = $product->getCategoryIds();
   fputcsv($fopen, array($name,$sku,implode(",",$categories),$image,$g_images), ",");
}
echo"Done";
fclose($fopen );