Hello,
I am trying to get the following MySQL query to work in Chronoforms:
I have tried different variations of the following code but can't get it to populate:
I am pulling data from the user_usergroup_map table and the user_profiles table. Any help is greatly appreciated.
Thanks,
Alex
I am trying to get the following MySQL query to work in Chronoforms:
SELECT a.email, c.*
FROM knzqw_users a
INNER JOIN knzqw_user_usergroup_map b ON a.id = b.user_id
INNER JOIN knzqw_user_profiles c ON a.id = c.user_id
WHERE b.group_id = 11 AND profile_key = 'profile.phone'
I have tried different variations of the following code but can't get it to populate:
<?php
$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query
->select($db->quoteName(array('a.email', c*)))
->from($db->quoteName('#__users', 'a'))
->join('INNER', $db->quoteName('#__user_usergroup_map', 'b') . ' ON (' . $db->quoteName('a.id') . ' = ' . $db->quoteName('b.user_id') . ')')
->join('INNER', $db->quoteName('#__user_profiles', 'c') . ' ON (' . $db->quoteName('a.id') . ' = ' . $db->quoteName('c.user_id') . ')')
->where($db->quoteName('b.group_id'). ' = ' .db->$quote('11')) AND
->where($db->quoteName('c.profile_key'). ' = ' .$db->quote('profile.phone'));
$db->setQuery($query);
$emails= $db->loadColumn();
$form->data['list'] = implode(",", $emails);
print_r($list)
I am pulling data from the user_usergroup_map table and the user_profiles table. Any help is greatly appreciated.
Thanks,
Alex
Hi Alex,
What is profile.phone' ? is that just a string or does it have some value?
What output are you expecting? $list isn't defined so that won't print anything.
How does it fail? Do you see any error message if you have site Debugging On or add a ChronoForms Debugger?
Bob
What is profile.phone' ? is that just a string or does it have some value?
What output are you expecting? $list isn't defined so that won't print anything.
How does it fail? Do you see any error message if you have site Debugging On or add a ChronoForms Debugger?
Bob
I appreciate the help but was able to figure it out.
What I am creating is a form that our analysts can use to send email and sms to officers (only about 20). I needed a way to pull information from the following tables:
[list]users[/list]
[list]user_usergroup_map[/list]
[list]user_profile[/list]
I ended up using the following code:
Its not pretty but works.
Thanks again and have a great day.
What I am creating is a form that our analysts can use to send email and sms to officers (only about 20). I needed a way to pull information from the following tables:
[list]users[/list]
[list]user_usergroup_map[/list]
[list]user_profile[/list]
I ended up using the following code:
<?php
$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query
->select($db->quoteName(array('a.email','c.profile_value')))
->from($db->quoteName('#__users', 'a'))
->join('INNER', $db->quoteName('#__user_usergroup_map', 'b') . ' ON (' . $db->quoteName('a.id') . ' = ' . $db->quoteName('b.user_id') . ')')
->join('INNER', $db->quoteName('#__user_profiles', 'c') . ' ON (' . $db->quoteName('a.id') . ' = ' . $db->quoteName('c.user_id') . ')')
->where($db->quoteName('b.group_id') . ' = ' . $db->quote('11'). 'AND' . $db->quoteName('c.profile_key') . ' = ' . $db->quote('profile.phone'));
$db->setQuery($query);
$emails= $db->loadColumn(0);
$form->data['list'] = implode(",", $emails);
$email2= $db->loadColumn(1);
$form->data['list2'] = implode(",", $email2);
?>
Its not pretty but works.
Thanks again and have a great day.
This topic is locked and no more replies can be posted.
