Code to Query two Joomla3 / Virtuemart3 tables and get field values

[its4yougr]

Hello to all
My SQL and PHP skills are very limited. For at least a day now I am trying to write a query for two Joomla3 - Virtuemart3 tables and get the datas but I cannot.
The tables are #__virtuemart_order_userinfos and #__virtuemart_orders common fields for both are the virtuemart_order_id the fields that I need for start from the two tables are:

1. #__virtuemart_order_userinfos :
virtuemart_order_id
company
last_name
first_name

2. #__virtuemart_orders :
virtuemart_order_id
order_number
order_total

The rest I can add, I think....

I probably need to have a JOIN for the two tables and select the correct fields based on virtuemart_order_id

I tries so far are not successful. I got this code but nothing echoes. Not even the date of order....

Code Select Expand


<?php
defined('_JEXEC') or die('Restricted access');
$user = JFactory::getUser();
$db = JFactory::getDbo();

$query = $db->getQuery(true);
$query->select($db->quoteName(array('parastatiko','virtuemart_order_id','address_type',)));
$query->from($db->quoteName('#__virtuemart_order_userinfos'));
$query->where($db->quoteName('virtuemart_order_id') . ' = '. $db->quote($row->virtuemart_order_id).' AND '. $db->quoteName('address_type') . ' = '.'"BT"');
$db->setQuery($query);
$results = $db->loadObjectList();

echo $db->loadResult();

echo $row->virtuemart_order_id;

echo $this->virtuemart_order_id;

echo $this->item->virtuemart_order_id;

$newDate = date("d-m-Y", strtotime($this->item->created));
$newTime = date("H:i", strtotime($this->item->created));
?>



Could you write for me the code for Joomla so I can add it to a php file I have created for Invoice and Receipt?

Thank you in advance

[GJC Web Design]

but VM has a wide range of model functions for these tasks

look thru the vm admin /models orders.php   for a fuction to give u order info then import the model and use the function

[PRO]

I dont know if this code works because I wrote it without testing,
This is an example to get a whole table row by order id.

<?php function getUserInfos($oid){

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select('*');
$query->from($db->quoteName('#__virtuemart_order_userinfos'));
$query->where($db->quoteName('virtuemart_order_id')." = ".$db->quote($oid));
$query->where('address_type=BT');
$db->setQuery($query);
$row = $db->loadAssoc();
//$mid=$row['virtuemart_media_id'];
return $row;
}

$orderrow=getUserInfos($order-id-here);

echo $orderrow['name-of-column'];

?>

It's much easier, and in some cases faster to just select '*' the whole row, then to select a few fields from the row.

So then, you just come back with an array

[its4yougr]

I thank you very much for your reply and your willingness to help ! !
   Let me give you more details for the default.php file that gets executed and "contains" the specific query / function you have build.
The file gets executed through this url:
http://domain_name/index.php?option=com_virtuemart&view=invoices&virtuemart_order_id='.$row->virtuemart_order_id; that exists in the file list.php under /templates/my_template_name/html/com_virtuemart/orders/.
This way I get the virtuemart_order_id. My problem is that I cannot pass this to default.php file where you write  $orderrow=getUserInfos($order-id-here);
As you allready figured out my php and Joomla codding sucks.... Sorry
Thanks

[Milbo]

VmModel::getModel('orders')->getOrder($orderId);

Is all what you need

[its4yougr]

I thank you very much for your reply ! ! !
After about 2 days of trying I got the following solution from another forum:

Code Select Expand


<?php
defined('_JEXEC') or die('Restricted access');
$user = JFactory::getUser();
$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select(array('a.virtuemart_order_id','a.order_total','a.order_number', 'b.company', 'b.last_name','b.first_name'));
$query->from($db->quoteName('#__virtuemart_orders', 'a'));
$query->join('Left', $db->quoteName('#__virtuemart_order_userinfos', 'b') . ' ON (' . $db->quoteName('a.virtuemart_order_id') . ' = ' . $db->quoteName('b.virtuemart_order_id') . ')');
$db->setQuery($query);
$resacc = $db->loadAssocList();
//echo $resacc['order_total'];
//printf ($resacc);
print_r ($resacc);
        echo $row[1]['order_total'];
        echo $row ['order_total'];
?>

Where only print_r ($resacc); works. Neither printf ($resacc), nor echo $row[1]['order_total'] nor echo $row ['order_total'] outputs anything....
print_r ($resacc); outputs an Array of two Arrays like :
Array (

• => Array ( [virtuemart_order_id] => 5 [order_total] => 50.00000 [order_number] => 25 [company] => Internet Technology Solutions [last_name] => Χατσιούλης [first_name] => Γιώργος ) [1] => Array ( [virtuemart_order_id] => 6 [order_total] => 17.50000 [order_number] => JALA04 [company] => Internet Technology Solutions [last_name] => Hatsiulis [first_name] => George ) )
  Is there any possibility to select records from there??
  I GIVE UP.....:-(
  I thank you all for your effort VERY MUCH ! ! !