Solved

Need to auto-populate form fields with data from a MySQL database dependent on selection from a drop down list.

Posted on 2008-10-20
7
1,408 Views
Last Modified: 2013-12-13
I have created a form that has a drop down list (Called ExistingFacility)which is populated by a collumn in a MySQL database. The form is being used to either write a new row to the database, or update an existing row. If the user selects an exisiting facility I need to auto populate the remaining fields with the data from the corosponding row in the database. I beleive this can be done through javascript but I have no experince with JS....

I have attached my code below
<form name="form" method="post" enctype="multipart/form-data" action="save_file1.php">
  <p class="style1">UPLOAD SPREADSHEET</p>
  <p class="style1 style3">Please Remember to Convert Your .XLS File to .CSV in Excel Before Uploading.</p>
  <p>
    <input type="file" name="file" />
    <br />
    <input type="submit" name="submit" value="submit" />
  </p>
  <p>After clicking submit you will be redirected to view the report. Please verify information and upload any photos before closing the web browser.</p>
</form>
 
<form action="AddFacility.php" method="post" name="form1" class="style1">
  <p>CREATE A NEW FACILITY OR EDIT AN EXISTING ONE</p>
  <table width="989" border="1">
    <tr>
      <?   
 
 
mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB"); 
 
$sql="SELECT FacilityName,ID FROM users WHERE FacilityName <>'' Order By FacilityName Asc";
$result=mysql_query($sql); 
 
$options=""; 
 
while ($row=mysql_fetch_array($result)) { 
 
    $id=$row["ID"]; 
    $thing=$row["FacilityName"]; 
    $options.="<OPTION VALUE=\"$FacilityName\">".$thing; 
} 
?>
      <td>Overwrite Existing:</td>
      <td><select name="ExistingFacilities" id="ExistingFacilities">
          <option value="New Facility">New Facility
          <option value="New Entry">
              <?=$options?>
                  </select>      </td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td width="150">Facility Name:</td>
      <td width="307"><input name="FacilityName" type="text" id="FacilityName" size="50" maxlength="50"></td> <!--I need this autopopulated with FacilityName from the database-->
      <td width="510">This <span class="style3">MUST EXACTLY MATCH</span> the facility name used in all audit reports.</td>
    </tr>
    <tr>
      <td>Contact Name:</td>
      <td><input name="ContactName" type="text" id="ContactName" size="50" maxlength="50"></td><!--I need this autopopulated with ContactName from the database-->
      <td>The name of the person who will have access to the audit reports.</td>
    </tr>
    <tr>
      <td>Contact E-Mail:</td>
      <td><input name="Contact E-Mail" type="text" id="Contact E-Mail" size="50" maxlength="50"></td><!--I need this autopopulated with ContactEmail from the database-->
      <td>The e-mail address which audit report information will be sent.</td>
    </tr>
 
    <tr>
      <td>Username:</td>
      <td><input name="NewUsername" type="text" id="NewUsername" size="50" maxlength="50"></td><!--I need this autopopulated with Username from the database-->
      <td>Username to log into reports.</td>
    </tr>
    <tr>
      <td>Password:</td>
      <td><input name="Password" type="text" id="Password" size="50" maxlength="50"></td><!--I need this autopopulated with Password from the database-->
      <td>Password to log into reports.</td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td><input type="submit" name="SubmitNewFacility" id="SubmitNewFacility" value="Submit"></td>
    </tr>
  </table>
  <p>&nbsp;  </p>
</form>

Open in new window

0
Comment
Question by:dannyg280
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 16

Expert Comment

by:brad2575
ID: 22757574
you CAN do it with javascript but it can get complicated and be a bit messy (especially if you have a LOT if data/fields).

The best way to do it would be if they select an existing one, then on select for that field reload the page they are on and pass the selected value to the page itself.  Then when the page loads again check for this value, if it is there then load the data for that value.

Does that make sence?  If not I can write out the logic for you.
0
 

Author Comment

by:dannyg280
ID: 22757629
I understand what you're saying... it would greatly help if you could help me a bit with the code..

BTW: I attached a bit more code then I needed to last time..
<form action="AddFacility.php" method="post" name="form1" class="style1">
  <p>CREATE A NEW FACILITY OR EDIT AN EXISTING ONE</p>
  <table width="989" border="1">
    <tr>
      <?   
 
 
mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB"); 
 
$sql="SELECT FacilityName,ID FROM users WHERE FacilityName <>'' Order By FacilityName Asc";
$result=mysql_query($sql); 
 
$options=""; 
 
while ($row=mysql_fetch_array($result)) { 
 
    $id=$row["ID"]; 
    $thing=$row["FacilityName"]; 
    $options.="<OPTION VALUE=\"$FacilityName\">".$thing; 
} 
?>
      <td>Overwrite Existing:</td>
      <td><select name="ExistingFacilities" id="ExistingFacilities">
          <option value="New Facility">New Facility
          <option value="New Entry">
              <?=$options?>
                  </select>      </td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td width="150">Facility Name:</td>
      <td width="307"><input name="FacilityName" type="text" id="FacilityName" size="50" maxlength="50"></td> <!--I need this autopopulated with FacilityName from the database-->
      <td width="510">This <span class="style3">MUST EXACTLY MATCH</span> the facility name used in all audit reports.</td>
    </tr>
    <tr>
      <td>Contact Name:</td>
      <td><input name="ContactName" type="text" id="ContactName" size="50" maxlength="50"></td><!--I need this autopopulated with ContactName from the database-->
      <td>The name of the person who will have access to the audit reports.</td>
    </tr>
    <tr>
      <td>Contact E-Mail:</td>
      <td><input name="Contact E-Mail" type="text" id="Contact E-Mail" size="50" maxlength="50"></td><!--I need this autopopulated with ContactEmail from the database-->
      <td>The e-mail address which audit report information will be sent.</td>
    </tr>
 
    <tr>
      <td>Username:</td>
      <td><input name="NewUsername" type="text" id="NewUsername" size="50" maxlength="50"></td><!--I need this autopopulated with Username from the database-->
      <td>Username to log into reports.</td>
    </tr>
    <tr>
      <td>Password:</td>
      <td><input name="Password" type="text" id="Password" size="50" maxlength="50"></td><!--I need this autopopulated with Password from the database-->
      <td>Password to log into reports.</td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td><input type="submit" name="SubmitNewFacility" id="SubmitNewFacility" value="Submit"></td>
    </tr>
  </table>
  <p>&nbsp;  </p>
</form>

Open in new window

0
 
LVL 16

Accepted Solution

by:
brad2575 earned 500 total points
ID: 22757799
This should work for you.  I put notes in the code below.  NOTE the php code may not be exactly correct for the syntax (been a couple months) but the logic is good.  I have used this process this way many times.

If you have any questions let me know.

Changes I made:

I added the php code for the query
I updated the code on the select box so on change it auto submits the form
I added PHP variables to each value field on the form
<form action="AddFacility.php" method="post" name="form1" class="style1">
  <p>CREATE A NEW FACILITY OR EDIT AN EXISTING ONE</p>
  <table width="989" border="1">
    <tr>
      <?   
 
 
mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB"); 
 
$sql="SELECT FacilityName,ID FROM users WHERE FacilityName <>'' Order By FacilityName Asc";
$result=mysql_query($sql); 
 
$options=""; 
 
while ($row=mysql_fetch_array($result)) { 
 
    $id=$row["ID"]; 
    $thing=$row["FacilityName"]; 
    $options.="<OPTION VALUE=\"$FacilityName\">".$thing; 
} 
 
 
// NOTE the code syntax may be a bit off for PHP, been a couple months, but the logic should be good
 
// if the form field value is not NULL then select the data for this facility from the database and 
// populate the form fields below 
if ($_Get("ExistingFacilities")) != ''{
	$sql="SELECT facilitydata from facilitytable WHERE FacilityName == '" . $_Get("ExistingFacilities")) . "' ";
	$result=mysql_query($sql); 
	
	// then for this part you can set up variable for each form field (declare them above/outside the if check)
	// and just put the value in the code below for the 
	//	value=="<? $variableNameValue?>"   
	//  in each of the form fields below.  If you declare them outside the if check then even if 
	// they are NOT set because the value of the ExistingFacilities is NULL (it was not selected) 
	// then it will just fill in a blank value for each field
	
}
 
?>
      <td>Overwrite Existing:</td>
      <td><select name="ExistingFacilities" id="ExistingFacilities" onchange="form1.submit()">
          <option value="New Facility">New Facility
          <option value="New Entry">
              <?=$options?>
                  </select>      </td>
      <td> </td>
    </tr>
    <tr>
      <td width="150">Facility Name:</td>
      <td width="307"><input name="FacilityName" type="text" id="FacilityName" size="50" maxlength="50" value=="<? $variableNameValue?>" ></td> <!--I need this autopopulated with FacilityName from the database-->
      <td width="510">This <span class="style3">MUST EXACTLY MATCH</span> the facility name used in all audit reports.</td>
    </tr>
    <tr>
      <td>Contact Name:</td>
      <td><input name="ContactName" type="text" id="ContactName" size="50" maxlength="50" value=="<? $variableNameValue?>" ></td><!--I need this autopopulated with ContactName from the database-->
      <td>The name of the person who will have access to the audit reports.</td>
    </tr>
    <tr>
      <td>Contact E-Mail:</td>
      <td><input name="Contact E-Mail" type="text" id="Contact E-Mail" size="50" maxlength="50" value=="<? $variableNameValue?>"></td><!--I need this autopopulated with ContactEmail from the database-->
      <td>The e-mail address which audit report information will be sent.</td>
    </tr>
 
    <tr>
      <td>Username:</td>
      <td><input name="NewUsername" type="text" id="NewUsername" size="50" maxlength="50" value=="<? $variableNameValue?>"></td><!--I need this autopopulated with Username from the database-->
      <td>Username to log into reports.</td>
    </tr>
    <tr>
      <td>Password:</td>
      <td><input name="Password" type="text" id="Password" size="50" maxlength="50" value=="<? $variableNameValue?>"></td><!--I need this autopopulated with Password from the database-->
      <td>Password to log into reports.</td>
    </tr>
    <tr>
      <td> </td>
      <td> </td>
      <td><input type="submit" name="SubmitNewFacility" id="SubmitNewFacility" value="Submit"></td>
    </tr>
  </table>
  <p>   </p>
</form>

Open in new window

0
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 

Author Comment

by:dannyg280
ID: 22758849
One super n00b question.... When you say "Declare the variables outside the if statment"... what is the syntext? For instance, for the email vaible would I just do $ExistingEmail=""; or is there a proper way to declare the variable without giving it a value?
0
 

Author Comment

by:dannyg280
ID: 22758918
Let me re-phrase my question.... if the database field for the email is "email" and I declare the variable as $ExistingEmail = $email
before the if stament then the $ExistingEmail value would be empty because the query would not have run through the if statment yet... so wouldn't I want to declare the variable inside the if statement?

***Again, I'm a beginner so please forgive me...
0
 
LVL 16

Expert Comment

by:brad2575
ID: 22759491
the only problem with declaring it inside the if statement is that you may get warnings saying the variable does not exist when it gets down to your form value section.  If the variable $email was not set because it did not get to the if statement you could get warnings (not errors just warnings) that the $email does not exist.

It should still work if you put it in the if statement but you would just have to be careful if you get warnings you would know what they were.

0
 

Author Closing Comment

by:dannyg280
ID: 31507808
Thank You for your help. I did have to mess with your syntext a bit. For one, I had to change my form action to $_SERVER['PHP_SELF'] since it was calling a seperate script. The method was set to POST so I had to change the GET method in your update of the script. (Also, GET and POST must be uppercase and in the form $_GET["ExistingFacilities']. A few other minor syntext changes and it worked great. Thanks.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
Boost your ability to deliver ambitious and competitive web apps by choosing the right JavaScript framework to best suit your project’s needs.
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question