News:

Looking for documentation? Take a look on our wiki

Main Menu

how to make a query [SOLVED]

Started by guardiano78, March 16, 2017, 20:10:16 PM

Previous topic - Next topic

guardiano78

Hello,
i have some problem with queries with alias and more then one table.

This is the mysql query:

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:

$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

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 https://www.gjcwebdesign.com
VM4 AusPost Shipping Plugin - e-go Shipping Plugin - VM4 Postcode Shipping Plugin - Radius Shipping Plugin - VM4 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
https://extensions.joomla.org/profile/profile/details/67210
Contact for any VirtueMart or Joomla development & customisation

guardiano78

Hi GJC,
thanks for your suggestion.
I hope this is the correct code for import user model in my custom module:

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

Ok, i solved!!

default.php

$CompanyData = $userModel->getUserAddressList($user->id, 'BT');
$CompanyInfos = get_object_vars($CompanyData[0]);


Thank you for help!!

Milbo

yeh you help yourself :-) But yes you do it now the right way
Should I fix your bug, please support the VirtueMart project and become a member
______________________________________
Extensions approved by the core team: http://extensions.virtuemart.net/

guardiano78

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

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.
Should I fix your bug, please support the VirtueMart project and become a member
______________________________________
Extensions approved by the core team: http://extensions.virtuemart.net/