[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 826
  • Last Modified:

Select box to populate form fields with values from MySQL db after an option is selected?

Hi experts.  

I am the computer specialist (Americorps Vista Volunteer) for the Lee County Alabama Habitat for Humanity Affiliate and am designing some web software for my affiliaite.  If successful it will  be exported to other affiliates as well.  .  

i am looking for a way to have a select box draw its options from a mysql table.   When one of the options is selected, I would like for the rest of the form to be automatically populated with information from that table based on the option that was selected.   My executive director said that some javascript will probably be needed to make this happen.  

I've put together some pictures of what I am trying to do at this website.  

http://www.habilee.org/question/question.htm

If you have some sample source code, that does this would be great.  Phpdbform does a great job of this, however I am planning to use queries from multiple tables and use joins and date formats which I don't see easily doing with phpdbform with my current experience (7 months) .  

Also, I will eventually need to code this functionality into an existing project at  http://www.calendar.leecountyhabitat.org/Page/calendar.php.  

Someday later on this year I would like to build in an online registration function for volunteers but first I would really like to learn how to get this select form working independent of phpdbform so that I can setup my own custom queries and joins.  

I thank you in advance for any help you can provide.  

Sincerely  

Doug Wong
Computer Specialist
Lee County Alabama Habitat for Humanity
1801 Market  Street
Opelika AL 36801
www.leecountyhabitat.org
0
Habilee
Asked:
Habilee
  • 3
  • 2
1 Solution
 
incrediblejohnCommented:
I'm not going to give you the whole page that I have (overkill), just the relevant parts.
At the top of your page, load the pulldown and display it IF no choice has been made.
//===============================
//Your connections are made earlier in an include:
//===============================
 include "conn.php3";

//==================
//page calls itself
//==================
if (!$dataperiod)    //this is name of pulldown
  {
//=====================================
///load stat period list into a recordset for pulldown choices
//=====================================
$data  = odbc_do($connectionSDWIS,"SELECT Monitoring_Period_Name FROM  PM WHERE Display='1'");

//==========================================
///Form Action goes here (page calls itself)
//==========================================
echo "<form action=\"/performance.php3\" method=POST >\n";    

echo "<table bgcolor=lightblue width=15%>\n";

//===========================
//display title for pulldown
//===========================
echo "<tr valign=top><td>b>Quarter</b><br>";

//===========================================
//pulldown control to display quarter choices NOTICE NAME
//===========================================
echo "<SELECT SIZE=1 NAME='dataperiod'  ONCHANGE=\"document.forms[0].submit()\">";

echo "<OPTION >\n";  //for empty choice

//==========================================
//Loop, fetching rows for pulldown control
//==========================================
do
  {
   /* Grab field data and assign to var... */
   $mpname = odbc_result($data, "Monitoring_Period_Name");
   /* Fill in the value... */
   echo "<OPTION VALUE='$mpname'>  $mpname\n";
  } while(odbc_fetch_row($data));
echo "</SELECT>";
echo "</td><td>";

echo "</td></tr>";
echo "</table>";


 echo "</form>";
echo "</div>";
echo "</body>";
echo "</html>";
exit();

}
Else
{
//===========================
//get your data using the pulldown's choice
//===========================
$data0=odbc_do($connectionSDWIS,"SELECT  * FROM PM WHERE PreviousQuarter ='$dataperiod'");

//========================
odbc_result_all($data0);
//========================
}

Cheers,
John Davis
0
 
HabileeAuthor Commented:
I've almost got this after modifying your code to work with my table.  The resulting code is posted below.  When I select something form the select box, it never carries over.  Would you explain to me this portion of the code and what I should do to it to make this work.  

How to I transfer information from this select box over to the next refresh of the form...  What is the document.forms[0]?  

            echo "<SELECT SIZE=1 NAME='select_site'  ONCHANGE=\"document.forms[0].submit($select_site)\">";


<?php

if(!$select_site){
echo "the value of select_site  in line 5 is $select_site <br>";

            //$query = "SELECT  * FROM 00_t_sites WHERE s_address ='$siteselect'";
            $query = "SELECT site_id, s_address, s_city, s_state, s_zip FROM 00_t_sites";
echo "query $query <br>";
            $result = mysql_query($query)or die(mysql_error());
            //$sitedata=mysql_query($query) or die(mysql_error());
            
            echo "<form action=\"/00_CAL/Admin/site_mod.php\"method=POST > \n";
            echo "<table >\n";
            
            
            echo "<tr valign=top><td><b>SiteSelection</b><br>";
            
            //===========================================
            //pulldown control to display quarter choices NOTICE NAME
            //===========================================
            echo "<SELECT SIZE=1 NAME='select_site'  ONCHANGE=\"document.forms[0].submit($select_site)\">";
            ?>



<?
            echo "<OPTION >\n";  //for empty choice
                  
            $num_sites = mysql_num_rows($result);
             for($j=0; $j < $num_sites; $j++)
            {
                              $row = mysql_fetch_assoc($result);
                              $optionstring = "$row[s_address]";
                              $q_site_id = "$row[site_id]";
                              //echo"<br> site_id $q_site_id <br>";
                              if  ($site_id  == $q_site_id)
                              {
                              echo "<option value =\"$q_site_id\"  selected>$optionstring</option>";
                              //echo "$site_id  $q_site_id selected <br>";
                              }
                              else
                              {
                              echo "<option value =\"$q_site_id\" >$optionstring</option>";            
                              }
            
            }//end for loop
            //___________________________________________________________
            
            echo "</SELECT>";


?>
<input name="S_Address" type="text" value="<?php echo"$F_address"  ; ?>" size="15">
<input name="S_City" type="text" value="<?php echo"$F_city"  ; ?>" size="15">
<input name="S_State" type="text" value="<?php echo"$F_state"  ; ?>" size="15">
<?


            echo "</td><td>";
            echo "</td></tr>";
            echo "</table>";
            echo "</form>";
            echo "</div>";
            echo "</body>";
            echo "</html>";
            exit();

}
else
{
            //===========================
            //get your data using the pulldown's choice
            //===========================

            $query = "SELECT  * FROM 00_t_sites WHERE s_address ='$select_site'";
echo "The query is  $query <br>";
echo "line 89 the value of siteselect is $select_site is ";


            $sitedata=mysql_query($query) or die(mysql_error());
            $row = mysql_fetch_assoc($sitedata);
$F_address = $row[s_address];
$F_city = $row[s_city];
$F_state = $row[s_state];
            
}
?>
0
 
incrediblejohnCommented:
echo "<SELECT SIZE=1 NAME='select_site'  ONCHANGE=\"document.forms[0].submit($select_site)\">";

take out the $select_site from your submit.    document.forms[0].submit() is a method   it's just like clicking a submit button

 forms.[0] means the first form object. You could name the form and use that instead. If you have more than one form object, then you need to figure out the index of the form that the Select input is in.

echo "<SELECT SIZE=1 NAME='select_site'  ONCHANGE=\"document.forms[0].submit()\">";  What you see after the ONCHANGE is javascript shorthand. The browser understands that you are referring to javascript and does whatever is asked.

You use the name of the pulldown, select_site, next time the page loads for the value to put in your query. You reference it as $select_site. Now, the sneaky or not obvious part is right at the top of an IF branch:
if(!$select_site)
//build your pulldown
else
//use $select_site for your criteria

If you wanted to build your pulldown everytime the page refreshes, then put it above the if(!$select_site). See the exclamation point in front of $select_site? Well, essentially that means, "IF $select_site doesn't exist".

The else statement pretty much says: "I've got a value now from the pulldown because select_site is not nothing.

Work with that for now. I'm hauling in potting soil to plant some hanging vines off of my balcony. . . . .

You can also call a javascript function instead of the submit method, change the values of your hidden inputs and then do a document.forms[0].submit() from the function you call.

John Davis
State of Oregon




0
 
HabileeAuthor Commented:
I finally got it working.  Thank your for your expert advice.  You just saved us a boatload of r&d time.  I'm awarding full points and an A.  Again.  Thanks a million.

Sincerely

Doug Wong
Computer Specialist
Lee County Alabama Habitat for Humanity
1801 Market  Street
Opelika AL 36801
www.leecountyhabitat.org
0
 
incrediblejohnCommented:
One other thing. I think that the document.forms(0).submit() might not work in some browsers. I think we fixed it by putting document.forms.formName.submit() or document.formName.submit()

Not exactly sure of the syntax but essentially you give the form a name="formName" in the form tag attributes and the reference that.

Thanks for the points and the grade! I just happened to have run into your problem before you did! This is the first PHP question I have ever answered.

Cheers,
John Davis
State of Oregon
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now