Bounty Ended with 50 reputation awarded by Community
2 deleted 6 characters in body
source | link

Working on the the example you gave...you need to change this:

$path = Mage::getResourceModel('core/url_rewrite')
    ->getRequestPathByIdPath('product/456', $store);

to this

$path = Mage::getResourceModel('core/url_rewrite')
    ->getRequestPathByIdPath('product/456/16', $store);

where 16 is the category id.
To generalize, the SEF urls for products with category paths included are kept in the table core_url_rewrite table with the column id_path looking like product/{product_id}/{category_id}.

Working on the the example you gave...you need to change this:

$path = Mage::getResourceModel('core/url_rewrite')
    ->getRequestPathByIdPath('product/456', $store);

to this

$path = Mage::getResourceModel('core/url_rewrite')
    ->getRequestPathByIdPath('product/456/16', $store);

where 16 is the category id.
To generalize, the SEF urls for products with category paths included are kept in the table core_url_rewrite table with the column id_path looking like product/{product_id}/{category_id}.

Working on the the example you gave...you need to change this:

$path = Mage::getResourceModel('core/url_rewrite')
    ->getRequestPathByIdPath('product/456', $store);

to this

$path = Mage::getResourceModel('core/url_rewrite')
    ->getRequestPathByIdPath('product/456/16', $store);

where 16 is the category id.
To generalize, the SEF urls for products with category paths included are kept in the table core_url_rewrite with the column id_path looking like product/{product_id}/{category_id}.

1
source | link

Working on the the example you gave...you need to change this:

$path = Mage::getResourceModel('core/url_rewrite')
    ->getRequestPathByIdPath('product/456', $store);

to this

$path = Mage::getResourceModel('core/url_rewrite')
    ->getRequestPathByIdPath('product/456/16', $store);

where 16 is the category id.
To generalize, the SEF urls for products with category paths included are kept in the table core_url_rewrite table with the column id_path looking like product/{product_id}/{category_id}.