Author Topic: Code to Query two Joomla3 / Virtuemart3 tables and get field values  (Read 176 times)

its4yougr

  • Beginner
  • *
  • Posts: 26
    • Κατασκευή ιστοσελίδων Θεσσαλονίκη - Web Design
  • VirtueMart Version: VM3
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]
<?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



Κατασκευή Ιστοσελίδων Θεσσαλονίκη - http://www.its4you.gr/
WebDesign Internet Marketing - http://www.webdesign-internetmarketing.com/

GJC Web Design

  • 3rd party VirtueMart Developer
  • Hero Member
  • *
  • Posts: 7281
  • Virtuemart, Joomla & php developer
    • GJC Web Design
  • VirtueMart Version: 2.6.22 & 3.0.16
Re: Code to Query two Joomla3 / Virtuemart3 tables and get field values
« Reply #1 on: March 10, 2017, 14:38:25 pm »
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
GJC Web Design
VirtueMart and Joomla Developers - php developers http://www.gjcwebdesign.com
VM3 AusPost Shipping Plugin - e-go Shipping Plugin - VM3 Postcode Shipping Plugin - Radius Shipping Plugin - VM3 NZ Post Shipping Plugin - AusPost Estimator
Samport Payment Plugin - EcomMerchant Payment Plugin - ccBill payment Plugin
VM2 Product Lock Extension - VM2 Preconfig Adresses Extension - TaxCloud USA Taxes Plugin - Virtuemart  Product Review Component
http://extensions.joomla.org/profile/profile/details/67210
Contact for any VirtueMart or Joomla development & customisation

PRO

  • Global Moderator
  • Super Hero
  • *
  • Posts: 10275
  • VirtueMart Version: 3+
Re: Code to Query two Joomla3 / Virtuemart3 tables and get field values
« Reply #2 on: March 10, 2017, 18:27:32 pm »

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

I do NOT do development work for hire.

its4yougr

  • Beginner
  • *
  • Posts: 26
    • Κατασκευή ιστοσελίδων Θεσσαλονίκη - Web Design
  • VirtueMart Version: VM3
Re: Code to Query two Joomla3 / Virtuemart3 tables and get field values
« Reply #3 on: March 10, 2017, 20:38:20 pm »
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
Κατασκευή Ιστοσελίδων Θεσσαλονίκη - http://www.its4you.gr/
WebDesign Internet Marketing - http://www.webdesign-internetmarketing.com/

Milbo

  • Virtuemart Projectleader
  • Administrator
  • Super Hero
  • *
  • Posts: 8794
  • VM3.2 Cached and Optimized
    • VM3 Extensions
  • VirtueMart Version: VirtueMart 3 on joomla 3
Re: Code to Query two Joomla3 / Virtuemart3 tables and get field values
« Reply #4 on: March 10, 2017, 20:48:07 pm »
VmModel::getModel('orders')->getOrder($orderId);

Is all what you need
I should fix your bug, please support the VirtueMart project and become a member
______________________________________
Extensions approved by the core team: http://extensions.virtuemart.net/

its4yougr

  • Beginner
  • *
  • Posts: 26
    • Κατασκευή ιστοσελίδων Θεσσαλονίκη - Web Design
  • VirtueMart Version: VM3
Re: Code to Query two Joomla3 / Virtuemart3 tables and get field values
« Reply #5 on: March 11, 2017, 07:16:34 am »
I thank you very much for your reply ! ! !
After about 2 days of trying I got the following solution from another forum:
Code: [Select]
<?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 ! ! !
Κατασκευή Ιστοσελίδων Θεσσαλονίκη - http://www.its4you.gr/
WebDesign Internet Marketing - http://www.webdesign-internetmarketing.com/