Solved

Auto populate fields in a PHP form with a Drop down selection

Posted on 2014-02-09
13
18,787 Views
Last Modified: 2014-02-11
I have a form with a drop down selection which pulls the company name from a mysql database.  What I would like to do is auto - fill some of the fields in the form depending on what has been selected in the drop-down.

For instance I want to select the company name from the drop down and have the address fields populate.

Attached is the code I have so far

Any suggestions?

<form class="forma" action="addclient.php" method="post" name="user_form_top" style="height:200px">

				<?php 
					$con = mysqli_connect("localhost", "root", "", "marvin"); 
					if (mysqli_connect_errno())
					  {
					  echo "Failed to connect to MySQL: " . mysqli_connect_error();
					  }
					$sql="SELECT * FROM business";
					$result=mysqli_query($con,$sql) 
				?> 
				
	<div id="business" style="margin-top: 190px; border: 2px solid #666666; height: 180px ">
		<label id="asub" for="Bname">
			<span> Company Name</span>

			<select name="Bname" id="Bname">
				<option value="Others">Create New</option> 																
					<?php 
					while ($line = mysqli_fetch_array($result, MYSQL_ASSOC)) { 
					?> 
				
				<option value="<?php echo $line['Bname'];?>"> <?php echo $line['Bname'];?> </option>  
					<?php } ?> 	
			</select>
		</label>
		
		<br />
        
		<input type="text" name="Bname2" id="Bname2" placeholder="Select from above list or type new here" style="width:230px; margin-left:10px; margin-top: 5px;">

		<label id="asub" for="Badress">
			<span>Address</span>
				<input type="text" name="Badress" id="Badress">
		</label>

		<label id="bsub">
			<span >Suburb</span>
				<input type="text" name="PCsuburb">
		</label>

		<label  id="csub">
			<span> State</span>
				<input type="text" name="PCstate">
		</label>
	
		<label  id="csub">
			<span>PC</span>
			<input type="text" name="PC">
		</label>

		<label  id="bsub">
			<span>Phone</span>
				<input type="text" name="Bphone">
		</label>
		
		<label  id="bsub">
			<span> website</span>
				<input type="text" name="Bwebsite">
		</label>
	</div>

	<div id="menu2" style="margin-top: 20px;">
		<button type="add" name="add" value="add_btn_was_clicked">
		  <img src="images/add.png" /> <span style="color: #3682E2; font-size: 14px;"> add </span>
		</button>

</form>

Open in new window

Admin note: Code moved to Code snippet
form-b.php
0
Comment
Question by:purple_tiger
  • 5
  • 4
  • 3
  • +1
13 Comments
 
LVL 34

Expert Comment

by:Dan Craciun
Comment Utility
Use the value attribute of the input tag: http://www.w3schools.com/tags/att_input_value.asp

For ex: <input type="text" name="Badress" id="Badress" value="<? echo $Baddress; >">

HTH,
Dan
0
 

Author Comment

by:purple_tiger
Comment Utility
Thanks Dan, but I'm not sure this works - I need the Baddress input field to echo out the address in mysql that corresponds to the company selected in the drop down (if that makes sense) any ideas?
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
There are several ways to do this.  The most straightforward way is to display the company names and have the associated data base row keys be a part of the form, perhaps in a value attribute.  Then when the form is submitted, your PHP action script will receive the key and make a second query to look up the details about the selected company.  This article teaches the essentials of PHP+SQL table maintenance.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_12335-PHP-and-MySQLi-Table-Maintenance.html

A less straightforward way, but probably workable, would be to transmit all of the details for all of the companies into hidden <div> areas, and reveal the <div> according to the company that was chosen.

Another way might be to use jQuery to make an AJAX request, sending the chosen key and retrieving the company data.  This article shows the essentials of jQuery/AJAX.
http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Jquery/A_10712-The-Hello-World-Exercise-with-jQuery-and-PHP.html

Before you go too far down this path, you probably want to learn how to use MySQLi techniques for error checking and visualization.  As written, the script will fail silently if anything goes wrong on the SQL server, perhaps creating a gibberish page.  It would be much better to get useful error messages.  You will probably also find, as I did, that the MySQLi object-oriented notation was much more user-friendly than the procedural and array notations.  Some guidance on how to use the extension to get error messages, fetch the right data, etc., is available in this article, where we map the familiar but obsolete MySQL extension to the extensions that PHP will continue to support.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html

If you want to post the CREATE TABLE statements for your data base table, we might be able to show some more specific solutions.
0
 
LVL 42

Expert Comment

by:Chris Stanyon
Comment Utility
There are a couple of ways of doing this.

The first option is to build you HTML with all the relevant data (address etc) 'hidden' in the <option> and then drop it into the <input> when the <select> changes.

The second option is to make an AJAX call back to the server when the <select> changes, where you would query your database, based on a value sent in the AJAX request (Business ID for example) and then populate the <input> fields using the AJAX response.

The first option would prevent a round trip back to the server, but if you have a lot of <option>s then the amount of data stored might be a problem. The second option needs a round trip back to the server, but you wouldn't have to load up all the business details in one go.

Let me know which option you prefer and I can talk you through it
0
 

Author Comment

by:purple_tiger
Comment Utility
Thanks guys  I've looked into both options and I think the AJAX query is going to be the best option as the options in the drop down will often change as new business are added or edited.  So if I've got this right -putting all the data in a hidden div's might get complicated as the data grows. is that right?

my create table code (if this helps)
CREATE TABLE IF NOT EXISTS `business` (
  `BID` int(11) DEFAULT NULL,
  `Bname` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
  `Badress` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Bwebsite` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Bphone` varchar(12) COLLATE utf8_unicode_ci DEFAULT NULL,
  `PC` varchar(4) COLLATE utf8_unicode_ci NOT NULL,
  `PCsuburb` varchar(31) COLLATE utf8_unicode_ci NOT NULL,
  `PCstate` set('tas','vic','sa','nsw','qld','nt','wa') COLLATE utf8_unicode_ci NOT NULL,
  `BDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`Bname`)
) 

Open in new window

0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
Interesting.  I would have expected maybe the BID column to be NOT NULL AUTO_INCREMENT PRIMARY KEY.  What motivated you to use a character string as the key?
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:purple_tiger
Comment Utility
Actually I started with the BID as the primary key (that's what I thought it should be),  However I was referencing this as a foreign key in another table "customers" and when it came to reports it only gave me the ID number and not the business name so I changed it.  That was a while ago now - I've learnt a bit more since then and updating the code as I go.

I should probably mention I am a newbie so there will be errors - but I am learning quite a bit from everyone so its much appreciated :)
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
Yeah, I get it - those things happen along the way.

If you want some good learning resources for PHP and SQL, this article may be helpful.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_11769-And-by-the-way-I-am-new-to-PHP.html
0
 

Author Comment

by:purple_tiger
Comment Utility
This is great -  thank you Ray
0
 
LVL 42

Expert Comment

by:Chris Stanyon
Comment Utility
It doesn't really matter that the BName is the primary key - you just pass that along to your server in the AJAX request, query the DB in your script using that value, and then pass the resulting DB row back to your script - probably in JSON format. Your jQuery script then reads the values from the JSON and populate the form fields :)

Something like this would do it:

$('#BName').change(function() {
   $.post('yourScript.php', { BName : $(this).val() }, null, 'json' )
   .done( function(response) {
      //response now contains the JSON returned from your Server
      $('#Bname2').val( response.bname2 );
      $('#Badress').val( response.baddress );
   })
   .fail( function(x, status, error) {
      alert(error);
   });
});

Open in new window

In this code, you server script would receive the value of the dropdown in the $_POST['BName'] variable, and would need to respond with JSON containing bname2 and badress keys.

FYI - If you had used the ID as the Primary key, then you would need to perform a JOIN query to get at the BName information - joining the 2 tables on the Primary and Foreign key, and selecting information from both tables.
0
 
LVL 42

Accepted Solution

by:
Chris Stanyon earned 500 total points
Comment Utility
Just re-read through your previous comments and you asked this question:

So if I've got this right -putting all the data in a hidden div's might get complicated as the data grows. is that right?

The answer is no! You've already pulled the data from your data table in order to build the <select> in the first place, so you would just use that. I wouldn't advise loading all the data into hidden DIVs but using data attributes instead. Have a look at this quick example:

<select name="Bname" id="Bname">
   <option value="Others">Create  New</option>
   <?php while ($line = mysqli_fetch_array($result, MYSQL_ASSOC)): ?>
   <option value="<?php echo $line['Bname'];?>" data-address="<?php echo $line['Badress'] ?>" data-state="<?php echo $line['PCsuburb'] ?>" data-suburb="<?php echo $line['PCstate'] ?>"><?php echo $line['Bname'];?></option>  
   <?php endwhile; ?> 	
</select>

Open in new window

You'll notice that all the data for each company is dynamically being stored in the <option> tag using data elements. Using jQuery, you can then simply grab these values when the drop down changes:

$('#Bname').change(function() {
    selectedOption = $('option:selected', this);
    $('input[name=Badress]').val( selectedOption.data('address') );
    $('input[name=PCsuburb]').val( selectedOption.data('suburb') );
    $('input[name=PCstate]').val( selectedOption.data('state') );
});

Open in new window

You can see a working demo here - http://jsfiddle.net/ChrisStanyon/EhD99/
0
 

Author Comment

by:purple_tiger
Comment Utility
This is fantastic!   I searched for quite a while and couldn't work it out but the above worked a treat - Thanks Chris :)
0
 
LVL 42

Expert Comment

by:Chris Stanyon
Comment Utility
No worries :)
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
This article discusses four methods for overlaying images in a container on a web page
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

771 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

14 Experts available now in Live!

Get 1:1 Help Now