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!!
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?
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
Ok, i solved!!
default.php
$CompanyData = $userModel->getUserAddressList($user->id, 'BT');
$CompanyInfos = get_object_vars($CompanyData[0]);
Thank you for help!!
yeh you help yourself :-) But yes you do it now the right way
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!
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.