Displaying data from a database when a drop down entry is selected

[shanehoward]

I need help in displaying data from a database when a drop down entry is selected.
I have a form that I created for submitting videoconferencing requests.  There are
about 5 forms from a drop down list.  I have it working to when they select a entry is
goes to the appropriate form.  What I need help with is that on each form there is a
drop down list that lists all my school districts that will be requesting a
videoconference.  What I would like it to do is when they use this drop down list and
select a entry that is pulls info from a database (jos_contxtd_detail) and pull from the
district manager, email address, telephone, and ip address of the video unit.  All this in
in the database.  When they select it I would like it to display that info in the form
before they submit it.

Here is sample of the drop down list (dname_list) is the element name for this

1;Select District;none
0;Abbott ISD;abbott
0;Academy ISD;academy
0;Aquilla ISD;aquilla

You can view the forms at www.edlink12.net
on the left side the button "test" is where I am working on the forms.
I have been looking at scripts and just can not get it figured out.
Any help would be appreciated.

[Gman10]

I have a similar problem.

I get the selected value.
Send the value to a hidden text field.
But i can't read this value into the query list!

if I find a solution then I'll let you know.

[boldee]

So if im getting this right you guys want to display a confirmation page that displays all the data for the particular record in your db?

Gman
You might want to take a look at my multipage with confirm package that my help you with the mechanics of what you are trying to do

I have noticed this coming up a lot in these forums recently so i will write an example package, i think if you are a newbie to scripting or ff all this can be a little confusing.

Stay tuned.

[facile]

When a limited set of records is in game as in the OP's example, it is more convenient to save all records into a javascript array when loading the form, so that no submit must be done to retrieve the record from the sql table.

I made a small sample for this case named Use select list to show record details.

Nick, I guess your sample will use the submit approach which is more suitable for large tables?

[boldee]

I think it might be best just to use a sort method for larger db's and then let the user select from the sorted list to see entries in more detail.I am currently working on a sort package with 4 different examples so i will post in the packages section when its complete.

[shanehoward]

Let me try again so I make sure that I am explaining like I think I am.
If I were to select example: Riesel ISD from my drop down list, when it is selected on the same page right under that drop down list I would like some info displayed that is pulled from a db (com_contxtd, contact component in joomla), it would be like the Contact Name for that school, their phone, email, etc..about 4 records.  My issue it that when a district scheules a videoconference using these forms, depending on the size of the district they might not know who their techincal contact is, I want to make it easier for them so that just have to select their district and it will populate that info from the database that I know is correct.  I hope this makes sense.  And I want to say thanks for the help on this.  This is a great component
Shane

[boldee]

I think the question is how do you want to display the data if you want to display in a query list then show details of that particular entry then its easy enough, is that what you would like to do?

[shanehoward]

I think so, I am still real new to this so I appreciate your patience.  I just would like that info displayed when they select that drop down and when the form is submitted I would like it included in the confirmation email and db
thanks again
shane

[boldee]

I will show you some example code when i get home from work, ive been working on somthing similar to what you want to do so should be easy enough to explain 

[shanehoward]

That would be great
Thank you

[boldee]

Ok Shane ive made you a small form that will sort a list by catagory display the result of that sort in a query list then display details of individual db entries.

The example uses the 'user' and 'groups' tables of joomla/mambo so it will work on any site.

The form
Page 1 of the form has a select list populated from the 'groups' table.
Page 2 has a query list that displays the result of the sort using the user table.
Page 3 displays the individual record details.

The code
On page 1 if you take a look at the select list you will see a php query and a foreach loop that populates the select list with the group types from the 'groups' table. You may not want to use this method for your own select list but i think its worth showing as an example.

If you look at the script tab for the select list you will see a small redirect function, this redirects to page 2 of the form and sets the parameter 'sort' to the value of the select list. Confused lol, in other words what you selected in the select list is passed to page 2 where it is displayed in a query list.

Have a look at the query list query and you can see how the sort happens, a varaible called '$record' is asigned the value of the parameter 'sort', then the user table is queried and ask to display all database entries whos group id's match the value of '$record'. So if you selected registered from the select list for example the query would display all records that have the group id number of registered.

While still in the query list take a look at columns and open up the one called username, in value you will see another redirect similar to the one used in the select list. This one creates a parameter for the id number of the entry you would like to look at the details for and redirects you to page 3.

Now go and edit the form and look at the before form piece, here you will see the piece library initialisation ff_InitLib this is important as we use some of the pieces in the library through the form. Next the variable '$id' is asigned the value of the paratemer 'id' then the 'user' table is queried and asked to display the record that matches the id number this is then asigned to the global variable '$record'.

On page 3 there is a static html field called 'Details' open it up and you will see some php code, the first part calls the global variable record and checks to see if it has a value. If it does the code return a small html table which use the code $record-> folowed by the column name of the table to display the value for the particular id number.

I suggest you look at the form and then read this again it will make far more sense then, let me know how you get on.

[Gman10]

The willingness to help on this site is amazing!

This would be perfect. It's exactly what i'm trying to achieve.
Unfortunately i'm having problems installing the packet, has anybody else got this problem?

[Gman10]

Sorry! It did install just without a package name.

Thanks for sharing your code, you've saved me hours of frustration.

[boldee]

aye its just a form i didnt package it up, the form is part of a package i am working on with 4 examples of sorting a db list using different methods.

[KaiLg]

Hi!

I am using the form you made. Actually you need to be logged in if you want to see the datails. Where do I have to change what so that everybody can see the details???

Form (before form)

Code:

// load the standard form creation utilities
$this->execPieceByName('ff_InitLib');

global $record;
$id = ff_getParam('ff_param_id');
$record = null;
if ($id) {
   $rows = ff_select(
        "select * "
        ." from #__users "
        ." where id=$id"
    );
    $record = $rows[0];
} // if

Select List (action script)

Code:

function ff_sort_action(element, action)
{
    switch (action) {
        case 'select':
        case 'change':
            ff_redirectParent(
            'index.php?option=com_facileforms'+
            '&ff_name=select'+
            '&ff_page=2'+
            '&ff_border=0'+
            '&ff_param_sort='+escape(element.value)
            )       
     break;
        default:;
 } // switch
} // ff_sort_action

query list

Code:

global $database;

$record=mosGetParam($_REQUEST, 'ff_param_sort', '');

$database->setQuery(
    "select * from #__users ".
    "WHERE `gid` LIKE '%$record%'".
    "ORDER BY name ASC"
);
$rows = $database->loadObjectList();