CODEWITHSUNDEEP
phpmysqlsqljoomla
user2102266
user2102266

Reputation: 539

Retrieving mutiple row data from sql by php executed under joomla

I have a sql query working fine. However after execution (php form in joomla inserted with sourcerer plugin) resulting array stays empty.

sql code:

SELECT `comid`,`comname`,`comlogo`    
FROM ffd_companies,ffd_comusercon,ffd_users    
WHERE companyid=comid AND userid=id AND id=1;

code in joomla->customHTMLmodule->sourcerer:

{source}
<?php
$user = JFactory::getUser();
echo $user->id;

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select($db->quoteName(array('comid','comname')));
$query->from($db->quoteName(array('ffd_companies','ffd_comusercon','ffd_users')));
$query->where($db->quoteName('companyid').' = '.$db->quoteName('comid').' AND '.$db->quote('userid') .' = '. $db->quote('id').' AND '. $db->quote('userid') .' = '.$db->quote($user->id));
$db->setQuery($query);
$row = $db->loadRowList();
print_r($row);
?>
{/source}

Tables & Relations :

SQL query: SELECT * FROM ffd_users; id ... standar joomla users table nothing changed

SQL query: SELECT * FROM ffd_comusercon; (Index table for Many to many rel. between. users and companies) companyid (FOREIGN KEY OF comid ) userid (FOREIGN KEY OF id )

SQL query: SELECT * FROM ffd_companies; comid comname comtype comlogo comyedek1 comyedek2 omyedek3 comyedek4 comyedek5 comyedek6 comyedek7 comyedek8

Upvotes: 0

Views: 733

Answers (2)

di3sel
di3sel

Reputation: 2012

This should work:

<?php
$user = JFactory::getUser();
echo $user->id;

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select($db->quoteName(array('comid','comname')))
  ->from($db->quoteName(array('ffd_companies','ffd_comusercon','ffd_users')))
  ->where($db->quoteName('companyid').' = '.$db->quoteName('comid'))
  ->where($db->quote('userid') .' = '. $db->quote('id'))
  ->where($db->quote('userid') .' = '.$db->quote($user->id));
$db->setQuery($query);

$row = $db->loadObjectList();
var_dump($row);
?>

Update query (with left joins), it's only a guess without database structure, but i guess you get the point

$query->select('COMP.id AS comid, COMP.name as comname')->
  from($db->quoteName('#__companies') . ' COMP')->
  join('LEFT', $db->quoteName('#__comusercon') . ' COMPX ON COMP.id = COMPX.company_id')->
  join('LEFT', $db->quoteName('#__users') . ' USR ON USR.id = COMPX.user_id')->
  where('USR.id = ' . $db->quote($user->id))->
  group('COMP.id');

Upvotes: 2

user2102266
user2102266

Reputation: 539

After countless of rewrite finally it gave same result with the pure sql.

<?php
$user = JFactory::getUser();
$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select($db->quoteName(array('comid', 'comname','comlogo') ) );
$query->from($db->quoteName(array('ffd_companies',ffd_comusercon,'ffd_users') ) );
$query->where($db->quoteName('companyid') .' = '. $db->quoteName('comid') .' AND '. $db->quoteName('userid') .' = '. $db->quoteName('id') .' AND '. $db->quoteName('userid')  .' = '. $db->quote($user->id)
);
$db->setQuery($query);
$row = $db->loadRowList();
print_r($row);
?>

Upvotes: 0

Related Questions