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,397 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
  • 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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

The task A number given should be formatted for easy reading by separating digits into triads. Format must be made inline via JavaScript, i.e., frameworks / functions are not welcome. So let’s take a number like this “12345678.91¿ and format i…
This article discusses how to create an extensible mechanism for linked drop downs.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
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…

760 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now