Solved

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

Posted on 2014-02-09
13
19,156 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
ID: 39845179
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
ID: 39845201
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 109

Expert Comment

by:Ray Paseur
ID: 39845225
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 43

Expert Comment

by:Chris Stanyon
ID: 39845227
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
ID: 39846380
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 109

Expert Comment

by:Ray Paseur
ID: 39846431
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
 

Author Comment

by:purple_tiger
ID: 39846533
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 109

Expert Comment

by:Ray Paseur
ID: 39846547
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
ID: 39846566
This is great -  thank you Ray
0
 
LVL 43

Expert Comment

by:Chris Stanyon
ID: 39846786
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 43

Accepted Solution

by:
Chris Stanyon earned 500 total points
ID: 39848200
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
ID: 39849712
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 43

Expert Comment

by:Chris Stanyon
ID: 39849731
No worries :)
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

I found this questions asking how to do this in many different forums, so I will describe here how to implement a solution using PHP and AJAX. The logical flow for the problem should be: Write an event handler for the first drop down box to get …
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
The viewer will learn how to count occurrences of each item in an array.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

832 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