VirtueMart Forum

I'm working on upgrading my company's Virtuemart to the newest version. Previously I had coded my own module to display a category/product tree using SQL statements and the products were linked to the corresponding product detail page via a standard URL format and the Product ID.

I'm working on recoding that module, and I have all the queries working, but I can't for the life of me figure out how to construct the link to the detail page?

Is there a field somewhere in the database I could pull based on the products ID or something?

look in mod_virtuemart_product

you should be able to use $product->link    or something like that as long as your loading the classes etc.

See I don't know how to get it in that same scope.

Here's my code:

<?php
defined('_JEXEC') or die('Direct access to this location is not allowed.');
$document =& JFactory::getDocument();
$document->addScript(JURI::root().'modules/mod_vmtree/main.js');

$main = $params->get('id');
//$sublink = $params->get('sublink');
$sublink = 'index.php';


$db =& JFactory::getDBO();

//get base categories
$query = 'SELECT virtuemart_category_id,category_name FROM 
#__virtuemart_category_categories INNER JOIN #__virtuemart_categories_en_gb 
ON #__virtuemart_category_categories.category_child_id = #__virtuemart_categories_en_gb.virtuemart_category_id
WHERE #__virtuemart_category_categories.category_parent_id = '.$main.'
AND #__virtuemart_categories_en_gb.category_name NOT LIKE "20%" 
ORDER BY category_name ASC';

$query2 = 'SELECT virtuemart_category_id,category_name FROM 
#__virtuemart_category_categories INNER JOIN #__virtuemart_categories_en_gb 
ON #__virtuemart_category_categories.category_child_id = #__virtuemart_categories_en_gb.virtuemart_category_id
WHERE #__virtuemart_category_categories.category_parent_id = '.$main.'
AND #__virtuemart_categories_en_gb.category_name LIKE "20%" 
ORDER BY category_name DESC';

$db->setQuery($query);
$rootLinks = $db->loadAssocList();
$db->setQuery($query2);
$dateLinks = $db->loadAssocList();


//echo $query;
echo '<div id="vmtree_list" class="fullColumn">';

echo '<div><a href="'.$sublink.'"><h6 class="vmtree_category" style="background:#faa32a;color:#FFF;">Subscribe Now!</h6></a></div>';
echo '<div><h6 class="vmtree_category" style="background:#375c7e;color:#FFF;">Papers by Topic</h6></div>';

foreach($rootLinks as $root){

echo '<div><h6 class="vmtree_category">'.$root['category_name'].'</h6>';
printlinks($root['virtuemart_category_id']);
echo '</div>';
}
echo '<div><h6 class="vmtree_category" style="background:#375c7e;color:#FFF;">Papers by Date</h6></div>';

foreach($dateLinks as $root){
echo '<div><h6 class="vmtree_category">'.$root['category_name'].'</h6>';
printlinks($root['virtuemart_category_id']);
echo '</div>';
}
echo '</div>';



function printlinks($id){
$db =& JFactory::getDBO();

$query = 'SELECT #__virtuemart_products_en_gb.virtuemart_product_id,product_name FROM 
#__virtuemart_product_categories INNER JOIN #__virtuemart_products_en_gb
ON #__virtuemart_product_categories.virtuemart_product_id = #__virtuemart_products_en_gb.virtuemart_product_id
WHERE #__virtuemart_product_categories.virtuemart_category_id = '.$id.'
ORDER BY product_name ASC';
$db->setQuery($query);
$productlist = $db->loadAssocList();
if(count($productlist)>0){
echo '<ul style="margin-left:7px;">';
foreach($productlist as $link){
// JOOMLA 1.5 / VIRTUEMART 1 LINK
//echo '<li><a href="reports?page=shop.product_details&flypage=garden_flypage.tpl&product_id='.$link['product_id'].'" style="font-size:1em;font-weight:normal;">'.$link['product_name'].'</a></li>';
}
echo '</ul>';
}


}

?>


This is how I built my links to the products
//Lets build a URL for our products that will follow SEF structure.
$url = JRoute::_ ('index.php?option=com_virtuemart&view=productdetails&virtuemart_product_id=' . $product->virtuemart_product_id . '&virtuemart_category_id='. $product->virtuemart_category_id); ?>
    <a href="<?php echo $url ?>"><?php echo $product->product_name ?></a></h3>
$product->virtuemart_product_id and the category_id are from the db in an sql query similar to yours.

Right but I don't know how to get that reference " $product" to work in my case?

Oh use ypur $rootlinks. I did foreach $products as $product. Then $product->myrequest

Ok thanks! I'll try and figure it out from there!

EDIT --

I'm assuming I need to include some virtuemart file to my module in order to use $product though?

EDIT 2 --
Ah nevermind, I finally found a phrase that worked with Google and found this link: http://dev.virtuemart.net/projects/1/wiki/Developing_a_module_or_plugin_for_Virtuemart_2

EDIT 3 --
Wow I'm an idiot, got thrown off by your use of $product when all I really needed was that link structure you provided! Got it working now!
For anyone who's curious, here's the final code:
<?php
defined('_JEXEC') or die('Direct access to this location is not allowed.');


$document =& JFactory::getDocument();

$color = $params->get('color');
$fcolor = $params->get('fcolor');
$css = $params->get('css');

$db =& JFactory::getDBO();

$style = '
#newestProduct{
background:#'.$color.';
color:#'.$fcolor.';
'.$css.'
}
';
$document->addStyleDeclaration($style);

$query = 'SELECT product_sku,virtuemart_product_id FROM #__virtuemart_products
WHERE published = "1" 
AND (RIGHT(product_sku,2)!="FR") 
AND (RIGHT(product_sku,2)!="ES")
AND (RIGHT(product_sku,2)!="WB")
AND (RIGHT(product_sku,7)!="webinar")
AND (RIGHT(product_sku,6)!="charts")
ORDER BY created_on DESC 
LIMIT 1';
$db->setQuery($query);
$prod = $db->loadAssoc();

$query2 = 'SELECT product_name,product_s_desc FROM #__virtuemart_products_en_gb
WHERE virtuemart_product_id = '.$prod['virtuemart_product_id'];
$db->setQuery($query2);
$prodDetails = $db->loadAssoc();

$url = JRoute::_ ('index.php?option=com_virtuemart&view=productdetails&virtuemart_product_id=' . $prod['virtuemart_product_id'] . '&virtuemart_category_id=1');

?>
<h5>Just Published </h5>
<!--<div class="fullColumn flatBottom" id="newestProduct" style="background:url(images/product-banners/<?php echo $prod['product_sku'];?>.png);">-->
<div class="fullColumn flatBottom" id="newestProduct">
<div style="width:55%;float:right;padding:2%;">
<?php 
$flypage = 'reports?page=shop.product_details&flypage=garden_flypage.tpl&product_id='.$prod['virtuemart_product_id'];
echo '<a href="'.$url.'" style="color:#FFF;">'.$prodDetails['product_name'].'</a>';
echo '<p class="product_description">'.$prodDetails['product_s_desc'].'</p>';
$button_lbl = 'More Info';
$button_cls = 'addtocart_button buttonOrange small';
?>
<br/>
<a href="<?php echo $flypage; ?>" style="float:right;" class="buttonOrange small">More Info</a>

</div>
</div>
<div class="lineborder blank"></div>



Thanks for all the help!

I'm sorry. I didn't mean to confuse you. Glad you got it worked out.