how to make a query [SOLVED]

[guardiano78]

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

This is the mysql query:

Code Select Expand


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 Expand


$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?

[guardiano78]

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

Code Select Expand


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

Code Select Expand


$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

[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.