Dropdown list from db - help

Hi there

I have had a search around the net, for a good example,on how to create a drop down list with the data showing all the website's users contact details shown from the database table (MYSQLv5) jos_contact_details. Please could you tell me what is wrong with my code 🙄 😢 ;

<div class="form_item">
  <div class="form_element cf_heading">
    <h2 class="cf_text">Report Uploads</h2>
  </div>
  <div class="cfclear"> </div>
</div>
<div class="form_item">
  <div class="form_element cf_dropdown">
    <label class="cf_label" style="width: 150px;">Select Account</label>
    <select class="cf_inputbox" id="select_1" size="1" title=""  name="'id'">  
    <div class="form_item">
  <div class="form_element cf_dropdown">
    <label class="cf_label" style="width: 150px;">
      Contacts</label>
    <select class="cf_inputbox validate-selection" id="articles" size="1" name="articles">
      <option value="">--?--</option>
isSite() ) {return;}
$db =& JFactory::getDBO();
$query = "
  SELECT `id`, `name`
    FROM `#jos_contact_details`
    WHERE `id` = 1
      AND `state` = 1 ;
";
$db->setQuery($query);
$options = $db->loadAssocList();
foreach ( $options as $o ) {
  echo "<option value="".$o[id]."">".$o[id]."</option>";
}
?>
    </select>

<div class="form_item">
  <div class="form_element cf_fileupload">
    <label class="cf_label" style="width: 150px;">Upload</label>
    <input class="cf_fileinput cf_inputbox" title="" size="20" id="file_5" name="file_5" type="file" />
    
  </div>
  <div class="cfclear"> </div>
</div>

<div class="form_item">
  <div class="form_element cf_button">
    <input value="Submit" name="button_2" type="submit" /><input type="reset" name="reset" value="Reset"/>
  </div>
  <div class="cfclear"> </div>
</div>

Thank You

Hi technologychick,

This line

FROM `#jos_contact_details`

should be

FROM `#__contact_details`

. You need to either use the actual prefix jos_table_name or the Joomla! prefix placeholder #__table_name

I try to use the placeholder version as the code is then portable to any Joomla! installation.

Bob

PS There's a good tutorial in this article from The ChronoForms Book if I do say so myself.

Thanks for the advice

But no look, there is still something is still wrong 😟

Please can someone help?

http://www.stockmatters.co.uk/index.php?option=com_chronocontact&chronoformname=Report_Uploads

Hi,
I can see quite a few issues within the code:

    <option value="">--?--</option>
isSite() ) {return;}
$db =& JFactory::getDBO();

This line seems to be chopped off..
There should be an if construct, and there is no isSite() function; what you should be using is the isSite() method of the JApplication instance of the site (usually available through the global variable $mainframe):

<?
if (!$mainframe->isSite()) {return;}

The following lines of code have improper HTML-tokens where PHP-code is expected:

$db->setQuery($query);
$options = $db->loadAssocList();
foreach ( $options as $o ) {
  echo "<option value="".$o[id]."">".$o[id]."</option>";
}
?>

The class instance operator should be litterary ->, not ->
The same applies to the "begin php-code" and "end php-code" tags (<? ?>, not <? ?>)
There are two " tokens on the "echo-option" line, outside the echoed string. This causes PHP to think these are referenced items, though it actually does not make any sense as we're not referencing to any instances of a class or native type (& is the reference operator in PHP). What I believe you're trying to do, is to insert a " into the echoed string, to be XHTML1.0 compliant - for this, you'll have to escape the quote using \, within the string.
The array index should be a string, unless you've explicitly defined a constant named id. If there is no constant named id, PHP will try using the string "id" instead and generate a notice.

The fixed code should look like this:

$db->setQuery($query);
$options = $db->loadAssocList();
foreach ($options as $o) {
  echo "<option value=\"" . $o["id"] . "\">" . $o["id"] . "</option>";
}
?>

/Fredrik

Thanks for the quick responce nml375

I'm sorry for the cut off code, it was a cut and paste error 😶

I followed your advice even though I am not well up on PHP and need all the help i can to coomplete this simple task 😢

My code now looks like this;

<div class="form_item">
  <div class="form_element cf_dropdown">
    <label class="cf_label" style="width: 150px;">
      Account</label>
    <select class="cf_inputbox validate-selection"
        id="account" size="1" name="account">
      <option value=''>--?--</option>
<?php
if (!$mainframe->isSite()) {return;}
$db =& JFactory::getDBO();
$query = "
  SELECT `id`, `name`
    FROM `#__contact_details`
    WHERE `id` = 1
      AND `state` = 1 ;
";
$db->setQuery($query);
$options = $db->loadAssocList();
foreach ($options as $o) {
  echo "<option value=\"" . $o["id"] . "\">" . $o["id"] . "</option>";
}
?>
    </select>
  </div>
  <div class="cfclear"> </div>
</div>

But it still doesn't work how I want it to. All I want is to have a dropdown box in a form that links to the site users, so u can select one at a time and upload files to their account.
I would love it if you could help me on this issue, it has made me feel so stupid.

Thanks

Hi technologychick,

There is no 'state' column in the table so this query will never return any results, it needs to be 'published' I think. You probably also want to show the user name in the dropdown

<?php
if (!$mainframe->isSite()) {return;}
$db =& JFactory::getDBO();
$query = "
  SELECT `id`, `name`
    FROM `#__contact_details`
    WHERE `id` = 1
      AND `published` = 1 ;
";
$db->setQuery($query);
$options = $db->loadAssocList();
foreach ($options as $o) {
  echo "<option value='".$o['id']."'>".$o['name']."</option>";
}
?>

Bob

I want to use this code to extract First & Last Name of logged in user recipients from the DB. So if cf_user_id 67 wants to see its recipients it will look in a drop-down box.

Now here is what I'm trying in this statement:

SELECT `cf_user_id`, `first_name`, 'last_name'
FROM `#__chronoforms_add_recipients`


WHERE `id` = 1
AND `published` = 1 ;

Now I'm not sure about the WHERE & AND. Can you help me out?

Thanks

Hi about2flip,

Let me check that I understand - you want a list of all users who are have entries in the #__chronoforms_add_recipients table AND are currently logged in??

I believe that you get the info on logged in users from the jos_session table so that would need to be joined.

But I can't see from your snippet here where the recipient ids are stored?

Bob

No.

The logged in user is able to create a personal list of people he wants to send info to.
I have them create this through a form called add recipients.

Now the form that has the drop-down box should be able to list the users list of people from the recipients table.

Now the recipient table has cf_id, record, uid, ip, cf_user_id, FN, LN, email address, etc...
So I need the drop down box to get the First Name, Last Name of cf_user_id.

This what I have not sure if it right, because it does not work.

<div class="form_item">
  <div class="form_element cf_dropdown">
    <label class="cf_label" style="width: 150px;">Select Recipient</label>
    <select class="cf_inputbox" id="recipients" size="1" title=""  name="select_5">
        <option value="Select Recipient">Select Recipient</option>
<?php
if (!$mainframe->isSite() ) {return;}
$db =& JFactory::getDBO();
$query = "
	SELECT `first_name`, 'last_name'
		FROM `#__chronoforms_add_recipient`
";
$db->setQuery($query);
$options = $db->loadAssocList();
foreach ( $options as $o ) {
	echo "<option value='".$o[id]."'>".$o[title]."</option>";
}
?>
		</select>
	</div>
	<div class="cfclear"> </div>
</div>

Thanks

Hi about2flip,

In the database query you are asking for the first_name and last_name* columns

SELECT `first_name`, 'last_name'

but when you use the results you are specifying id and title which you haven't asked for and don't exist in the table anyhow:

echo "<option value='".$o[id]."'>".$o[title]."</option>";

Bob

PS1 You need backticks `` round `last_name`

PS2 The value should be empty for the default option:

<option value="">Select Recipient</option>

Hi Bob,

Thanks for the support.

Couple of things that don't seem to work:

echo "<option value='".$o[first_name]."'>".$o[last_name]."</option>";

Only seems to grab just the last_name.

Second. I need the drop down to work for only the logged in user, and it should only show what belongs to cf_user_id. if I log out, it still shows the list.

echo "<option value='".$o[cf_user_id]."'>".$o[first_name]."</option>";

Should it read like this? I'm to sure how to do concatenation for last_name.

THanks

Hi about2flip,

Well, you hae first_name in the option value which is the result that will be returned when the form is submitted and last_name between the <option> & </option> tags which is the part that will be visible in the drop-down.

You may need to think through what you actually want this drop-down to do?

Bob

Hi Bob,

I do know what I need it to do.

I need the logged in user to be able to see it's list of users to receive information.
I need the first and last name to display in the drop down box.
my php programming is not advanced yet...I am still learning, so if you could please help me out I will greatly appreciate it.

Thanks

Hi about2flip,

But what values do you want the form to return from the drop-down?

Bob

PS I'm really sorry but the forum is very busy with the hew releases and there really isn't time to run HTML tuturials here :-(

The First and Last Names of the logged in user, that was created by the logged in user through a CF form I made called recipients. How would i filter it just for logged in users.

For Example:

Bob Janes created a list of recipeints, and now he wants to send a recipient information. He looks through a drop down list, and selects the person in his list.

I'm not asking for an HTML lesson only help with a tutorial in your book.

Thanks

If anyone is reading this maybe you can help. I really need to finish this part of the project.

Hi abotu2flip,

To check which users are logged in you need to check the userid column in the jos_session table.

I still have no idea what values you are expecting the drop-down to return when the form is submitted.

Bob

Hi about2flip,

If you want the First and Last name to display then you need them both between the <option . . .> and </option> tags

echo "<option value='????'>".$o[first_name]." ".$o[last_name]."</option>";

You still haven't answered the question about what value you want the form to return when submitted though so I have no idea what now goes in the ???? space.

Bob

Thanks Bob.

cf_id would be the value.
Do you know how I can just list the names the belong to the logged in user. Is this correct?

<?php
$user =& JFactory::getUser();
$user_id = $user->get('id');  <=====should this be id or cf_user_id?
if (!$mainframe->isSite() ) {return;}
$db =& JFactory::getDBO();
$query = "SELECT CONCAT_WS(' ', first_name, last_name) as name, cf_id FROM #__chronoforms_add_recipient WHERE cf_user_id'='".$user_id.";
 
";
$db->setQuery($query);
$options = $db->loadAssocList();
foreach ( $options as $o ) {
	echo "<option value='".$o['cf_id']."'>".$o['name']."</option>";
}
?>

UPDATE: GOT IT TO WORK

This is my final code to get it work:

<?php
$user =& JFactory::getUser();
$user_id = $user->get('id');
if (!$mainframe->isSite() ) {return;}
$db =& JFactory::getDBO();
$query = "SELECT CONCAT_WS(' ', first_name, last_name) as name, cf_id FROM #__chronoforms_add_recipient WHERE `cf_user_id`=".$user_id.";
"; 
$db->setQuery($query);
$options = $db->loadAssocList();
foreach ( $options as $o ) {
	echo "<option value='".$o['cf_id']."'>".$o['name']."</option>";
}
?>