drop down list from database table jos_users

Can't find anything on this yet.
I currently have a wizard created form with a drop down list of manually added users to pick from. The form is displaying correctly to a menu linked page and saving data to the database table (jos_chronoforms_legion_app.

In the form, instead of using the drop down I have now I'd like to retrieve usernames from the database table jos_username.

It looks like Profile Page plugin can be used for this but I'm lost on how to go forward. The explanations are confusing. I have the Profile plugin enabled with these settings:

Table name: jos_users
Target field name: username


This is what the drop down looks in Form Code/HTML Code:

<div class="form_item">
  <div class="form_element cf_dropdown">
    <label class="cf_label" style="width: 200px;">Referring Legion member</label>
    <select class="cf_inputbox" id="select_26" size="1" title=""  name="select_26">
    <option value="">Choose Option</option>
      <option value="Absolute Killer">Absolute Killer</option>
<option value="DDS">DDS</option>
<option value="o0nzl0o">o0nzl0o</option>

    </select>
    
  </div>
  <div class="cfclear"> </div>
</div>

I'd appreciate some help to get started.. thanks

Helwoe

Hi Helwoe,
The Profile CFPlugin cannot give you a list of users, though it can later be used to display the properties for a single user.

To retrieve the list of all users, you'll need to query the Database directly:

<?php

$db =& JFactory::getDBO();
$db->setQuery('SELECT username FROM #__users');
$userlist = $db->loadAssocList();

Once we've got the list in $userlist, what's left to do is run a foreach-loop, and build our drop down..

$optns = array();
foreach ($userlist as $u) {
  $optns[] = JHTML::_('select.option', $u['username']);
}
?>

... form here ...
<div class="form_item">
  <div class="form_element cf_dropdown">
    <label class="cf_label" style="width: 200px;" for="select_26">Referring Legion member</label>
<?php
echo JHTML::_('select.genericlist', $optns, 'select_26', 'class="cf_inputbox" size="1" title=""');
?>
  </div>
  <div class="cfclear"> </div>
</div>
... form here ...

This uses the JHTML- and JHTMLSelect classes to build the actual dropdown, though you could echo the <select> and <option> tags yourself if you prefer it that way..

/Fredrik

Edit: Fixed minor typo. JTHML => JHTML
Edit: Fixed array indexing of $u.

Thanks for the information.
Where would I put this code? From code/html code?

Helwoe

Hi Helwoe,
Yes. It would replace the current dropdown code in your Form HTML code.

/Fredrik

Thanks.
So this code:

    <?php

    $db =& JFactory::getDBO();
    $db->setQuery('SELECT username FROM #jos_users');
    $userlist = $db->loadAssocList();
    $optns = array();
    foreach ($userlist as $u) {
      $optns[] = JTHML::_('select.option', $u);
    }
    ?>

    ... form here ...
    <div class="form_item">
      <div class="form_element cf_dropdown">
        <label class="cf_label" style="width: 200px;" for="select_26">Referring Legion member</label>
    <?php
    echo JTHML::_('select.genericlist', $optns, 'select_26', 'class="cf_inputbox" size="1" title=""');
    ?>
      </div>
      <div class="cfclear"> </div>
    </div>
    ... form here ...
    

would go in between lines (marked </select>)?
Or replace them entirely?

<div class="form_item">
<div class="form_element cf_dropdown">
<label class="cf_label" style="width: 200px;">Were you referred here by a Legion member?</label>
<select class="cf_inputbox" id="select_29" size="1" title="" name="select_29">
<option value="">Choose Option</option>
<option value="Yes">Yes</option>
<option value="No">No</option>

</select>

</div>
<div class="cfclear"> </div>
</div>

<div class="form_item">
<div class="form_element cf_dropdown">
<label class="cf_label" style="width: 200px;">Referring Legion member</label>
<select class="cf_inputbox" id="select_26" size="1" title="" name="select_26">
<option value="">Choose Option</option>
<option value="Absolute Killer">Absolute Killer</option>
<option value="DDS">DDS</option>
<option value="o0nzl0o">o0nzl0o</option>

</select>

Either way I put it in I get errors after applying them. And when I go back to form code what I pasted in is all stripped out?!?

Helwoe

Hi Helwoe,
The first part can go pretty much anywhere as long as it's before the second part. It will not output any text, so it will not interfere with the form layout.

<?php
$db =& JFactory::getDBO();
$db->setQuery('SELECT username FROM #__users');
$userlist = $db->loadAssocList();
$optns = array();
foreach ($userlist as $u) {
  $optns[] = JHTML::_('select.option', $u['username']);
}
?>

The second part - minus the "... form here ..." placeholders should replace the whole piece of code you posted in your first post.

<div class="form_item">
 <div class="form_element cf_dropdown">
  <label class="cf_label" style="width: 200px;" for="select_26">Referring Legion member</label>
<?php
echo JHTML::_('select.genericlist', $optns, 'select_26', 'class="cf_inputbox" size="1" title=""');
?>
 </div>
 <div class="cfclear"> </div>
</div>

Please post the error messages you got.

/Fredrik

Edit: Fixed minor typos. JTHML => JHTML, #jos_users => #__users
Edit: Fixed array indexing of $u.

Sorry for being such a php noob. Thanks again..

I get the following errors:

Warning: Invalid argument supplied for foreach() in /home/content/t/h/e/thelegionadmin/html/administrator/components/com_chronocontact/admin.chronocontact.php(2736) : eval()'d code on line 34
Referring Legion member
Fatal error: Class 'JTHML' not found in /home/content/t/h/e/thelegionadmin/html/administrator/components/com_chronocontact/admin.chronocontact.php(2736) : eval()'d code on line 43

34: foreach ($userlist as $u) {
43: echo JTHML::_('select.genericlist', $optns, 'select_26', 'class="cf_inputbox" size="1" title=""');

Should I have something in there for title "" ?

Helwoe

Hi Helwoe,
Seems a typo got the better end of me; JTHML should in fact be JHTML.
And I'm not sure how I managed to write #jos_users instead of #__users (the #__ will be replaced by the table prefix - usually 'jos_' - that you've set in the site configuration).

I'll update my previous posts in a second or two.

/Fredrik

Thank, I should have caught that typo sorry.
When I use #__users I get
array
array
array

in the drop down. #jos_users creates blank in then drop down.

Helwoe

Sorry (again).
Seems my mind isn't at 100% capacity in all this heat..
Since I'm using the loadAssocList() method, $u should be $u['username'] in the JHTML-line of code (though not within the foreach command). I'll see if I can update my previous posts before my phone runs out of battery..

/Fredrik

It works. Thank you!
I found this to be very interesting and pushing me into either taking some classes or grabbing some books.

Thanks again Fredrik

PS. I'll trade with you about a month of rain for some of that sunshine/heat.