Author Topic: how to make a query [SOLVED]  (Read 239 times)

guardiano78

  • Jr. Member
  • **
  • Posts: 190
how to make a query [SOLVED]
« on: March 16, 2017, 20:10:16 pm »
Hello,
i have some problem with queries with alias and more then one table.

This is the mysql query:
Code: [Select]
SELECT a.company denominazione, a.address_1 indirizzo, a.zip cap, a.city comune, b.state_2_code provincia, a.phone_1 telefono
FROM jos_virtuemart_userinfos a, jos_virtuemart_states b
WHERE a.virtuemart_user_id = '617' AND a.address_type = 'BT' AND a.virtuemart_state_id = b.virtuemart_state_id

Now i have to translate the query with jdatabase:
Code: [Select]
$db = JFactory::getDBO();
$query = $db->getQuery(true);
 //$query->select(array('a.company denominazione', 'a.address_1 indirizzo', 'a.zip cap', 'a.city comune', 'b.state_2_code provincia', 'a.phone_1 telefono'));
 $query->select(array('a.company', 'a.address_1', 'a.zip', 'a.city', 'b.state_2_code', 'a.phone_1'));
//$query->from(array('#__virtuemart_userinfos a', '#__virtuemart_states b'));
$query->from($db->quoteName('#__virtuemart_userinfos', 'a'));
$query->from($db->quoteName('#__virtuemart_states', 'b'));       
$query->where("a.virtuemart_user_id = '".$user_id."'", 'AND');
$query->where("a.address_type = 'BT'", 'AND');
$query->where("a.virtuemart_state_id = b.virtuemart_state_id");
$db->setQuery($query);
What's wrong?

Thanks!!

GJC Web Design

  • 3rd party VirtueMart Developer
  • Hero Member
  • *
  • Posts: 7282
  • Virtuemart, Joomla & php developer
    • GJC Web Design
  • VirtueMart Version: 2.6.22 & 3.0.16
Re: how to make a query
« Reply #1 on: March 16, 2017, 20:56:31 pm »
what happens when u run it?

what error?

also why not use the VM users model to get this info? R u not in VM?
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

guardiano78

  • Jr. Member
  • **
  • Posts: 190
Re: how to make a query
« Reply #2 on: March 17, 2017, 08:21:14 am »
Hi GJC,
thanks for your suggestion.
I hope this is the correct code for import user model in my custom module:
Code: [Select]
if (!class_exists('VmConfig'))
    {
        require JPATH_ROOT . '/administrator/components/com_virtuemart/helpers/config.php';
        VmConfig::loadConfig();
    }

Now, how can i display personal data of logged in user, in my default.php

Thank you

guardiano78

  • Jr. Member
  • **
  • Posts: 190
Re: how to make a query
« Reply #3 on: March 17, 2017, 10:07:56 am »
Ok, i solved!!

default.php
Code: [Select]
$CompanyData = $userModel->getUserAddressList($user->id, 'BT');
$CompanyInfos = get_object_vars($CompanyData[0]);

Thank you for help!!

Milbo

  • Virtuemart Projectleader
  • Administrator
  • Super Hero
  • *
  • Posts: 8809
  • VM3.2 Cached and Optimized
    • VM3 Extensions
  • VirtueMart Version: VirtueMart 3 on joomla 3
Re: how to make a query [SOLVED]
« Reply #4 on: March 17, 2017, 21:18:40 pm »
yeh you help yourself :-) But yes you do it now the right way
I should fix your bug, please support the VirtueMart project and become a member
______________________________________
Extensions approved by the core team: http://extensions.virtuemart.net/

guardiano78

  • Jr. Member
  • **
  • Posts: 190
Re: how to make a query [SOLVED]
« Reply #5 on: March 18, 2017, 15:19:42 pm »
Hi Milbo,
i agree :-)
but for me, the problem remains that I have not yet understood out how to make queries with multiple tables and aliases :-(

Bye!

Milbo

  • Virtuemart Projectleader
  • Administrator
  • Super Hero
  • *
  • Posts: 8809
  • VM3.2 Cached and Optimized
    • VM3 Extensions
  • VirtueMart Version: VirtueMart 3 on joomla 3
Re: how to make a query [SOLVED]
« Reply #6 on: March 18, 2017, 17:53:39 pm »
Just use the manual method, as we do, without "query object". You can also debug the sql used in vm. You can add in any model function "setDebugSql(1)", to sql of exeSortSearchListQuery which shows you the used sql for gathering ids for lists.
I should fix your bug, please support the VirtueMart project and become a member
______________________________________
Extensions approved by the core team: http://extensions.virtuemart.net/