How can get all products of magento estore with their position

Question

I am getting all products of a magento estore with fields like sku,product_id,price,special_price and product categories . but I am not getting product position field. I want to get all products with their positions and categories so that products showing according to magento store on basis of position also same show on POS app.

for example In my store 10 products with different categories .I need information for each one product like this

array(product_id=>1,
      name=>abc,
      product_categories=>1,2,3,
      price=>10.00,
      position of product=>1
)

similarly I want all products.please help.I tried all ways and solution that are on google. Below is the code to get all products with their categories but position field not getting.

$products = Mage::getModel('catalog/product')->getCollection()->addAttributeToSort('position');
$prodIds=$products->getAllIds();
if(count($prodIds)>0)
{
     $product = Mage::getModel('catalog/product');
     $i=0;
     foreach($prodIds as $productId)
     {
         $ids= array();
         $productCollection = Mage::getModel('catalog/product')->load($productId);
         $final['prodCat_list'][$i]['sku']= $productCollection ->getSku();
         $final['prodCat_list'][$i]['product_id']= $productId;
         $final['prodCat_list'][$i]['name']= $productCollection ->getName();
         $final['prodCat_list'][$i]['image']=$productCollection ->getThumbnailUrl(); 
         $final['prodCat_list'][$i]['description']=$productCollection ->getDescription();
         $final['prodCat_list'][$i]['short_description']=$productCollection ->getShortDescription();
         $final['prodCat_list'][$i]['weight']=$productCollection ->getWeight();
         $final['prodCat_list'][$i]['created_at']=$productCollection ->getCreatedAt();
         $final['prodCat_list'][$i]['updated_at']=$productCollection ->getUpdatedAt();
         $final['prodCat_list'][$i]['price']=$productCollection ->getPrice();
         $final['prodCat_list'][$i]['special_price']=$productCollection ->getSpecialPrice();
         $final['prodCat_list'][$i]['tax_class_id']=$productCollection ->getTaxClassId();
         $final['prodCat_list'][$i]['cat_id']= implode(',',$productCollection ->getCategoryIds());
         $ids[$i]=$result[$i]['category_ids'];
         foreach($productCollection ->getCategoryIds() as $categoryId)
         {
             $category = Mage::getModel('catalog/category')->load($categoryId);
             $final['prodCat_list'][$i]['cat_name'][$categoryId]= $category->getName();
         }
         $i++;
     }
}

Answer 1

You cannot sort the products by position field.
This field has meaning only in relation with a category. There is no position attribute for the product (unless you added one by yourself).
You can get all the data you need with one (not so simple) single select. Here goes:

$res = Mage::getSingleton('core/resource');
$eav = Mage::getModel('eav/config');
$nameattr = $eav->getAttribute('catalog_category', 'name');
$nametable = $res->getTableName('catalog/category') . '_' . $nameattr->getBackendType();
$nameattrid = $nameattr->getAttributeId();
//you could add here to select only the attributes you need but I'm lazy and I added all of them (*)
$collection = Mage::getModel('catalog/product')
            ->getCollection()->addAttributeToSelect('*');
$collection->joinTable('catalog/category_product',
    'product_id=entity_id', array('single_category_id' => 'category_id', 'position_in_category'=>'position'),
    null, 'left');
$collection->joinTable(
    $nametable,
    'entity_id=single_category_id',
    array('single_category_name' => 'value'),
    "attribute_id=$nameattrid",
    'left'
);
$collection->groupByAttribute('entity_id');
$collection->getSelect()
    ->columns(array('category_ids_values' => new Zend_Db_Expr("IFNULL(GROUP_CONCAT(`catalog_category_product`.`category_id` SEPARATOR ','), '')")))
    ->columns(array('position_values' => new Zend_Db_Expr("IFNULL(GROUP_CONCAT(`catalog_category_product`.`position` SEPARATOR ','), '')")))
    ->columns(array('category_names' => new Zend_Db_Expr("IFNULL(GROUP_CONCAT(`{$nametable}`.`value` SEPARATOR ','), '')")));

echo $collection->getSelect();

$final['prodCat_list'] = array();
$i = 0;
foreach ($collection as $product){
    $item = array();
    $item['sku']= $product->getSku();
    $item['product_id']= $product->getId();
    $item['name']= $product->getName();
    $item['image']=$product->getThumbnailUrl();
    $item['description']=$product->getDescription();
    $item['short_description']=$product->getShortDescription();
    $item['weight']=$product->getWeight();
    $item['created_at']=$product->getCreatedAt();
    $item['updated_at']=$product->getUpdatedAt();
    $item['price']=$product->getPrice();
    $item['special_price']=$product->getSpecialPrice();
    $item['tax_class_id']=$product->getTaxClassId();
    //this can be retrieved directly with $product->getCategoryIds, but they might be in a different order.
    $item['cat_id'] = $product->getCategoryIdsValues();
    $item['positions'] = $product->getPositionValues();
    $item['category_names'] = $product->getCategoryNames();

    $final['prodCat_list'][] = $item;
}

This code returns and array with product data. An element looks something like this:

       [5] => Array
            (
                [sku] => MA464LL/A
                [product_id] => 25
                [name] => Product name here
                [image] => image/link here
                [description] => Description here.
                [short_description] => Short description here
                [weight] => 10.6000
                [created_at] => 2007-08-24 14:28:50
                [updated_at] => 2008-07-28 21:27:34
                [price] => 2299.9900
                [special_price] => 
                [tax_class_id] => 2
                [cat_id] => 15,28
                [positions] => 0,0
                [category_names] => Computers,Laptops
            )