21,817 views

Storing records on a database is a great help and convenience to a web developer or even to a web designer. It gives an option to pull out database records anytime the necessity occurs. And to help speed up the process, jSon or XML can be use to perform the required action. One of these common actions from a website user's perspective is to populate records from a database to a dropdownlist without leaving the current page. The actual exchange of information from the current user page to the server will happen on the back-end of that exact same page where the user is. That give's an advantage and in favor of both the developer and the user in terms of site speed and good functionality. I will show you how did I accomplished that on this website. To populate a dropdownlist from a database with the poweful combination of PHP-JSON-JQUERY and PHP-XML-JQUERY.

By Rnel, on October 07, 2010, under Php Tutorials
 

Storing records on a database is a great help and convenience to a web developer or even to a web designer. It gives an option to pull out database records anytime the necessity occurs. And to help speed up the process, jSon or XML can be use to perform the required action.

One of these common actions from a website user's perspective is to populate records from a database to a dropdownlist without leaving the current page. The actual exchange of information from the current user page to the server will happen on the back-end of that exact same page where the user is. That give's an advantage and in favor of both the developer and the user in terms of site speed and good functionality.

I will show you how did I accomplished that on this website. To populate a dropdownlist from a database with the poweful combination of PHP-JSON-JQUERY and PHP-XML-JQUERY.

 

After you download the related files, extract it.

  • You should have the following files:
  • index.php
  • RNELdotNET_populateStates_json.php - under files folder
  • RNELdotNET_populateStates_xml.php - under files folder
  • RNELdotNET_populating-dropdownlist-from-database-using-php-json-jquery-and-php-xml-jquery.js - under files folder
  • states.sql - under sql folder

Import the sql file to your database.

Note:The sql records were taken from this list http://xcs.me/hf30n

 
 

Database:

 

This is the TABLE structure that I've used to store states records. The sql file is included on the free download file above.

The table only have two fields namely; id and state. id has an auto increment character and would represent as the unique indentifier of the state field. state field is wehre the states are stored.

		DROP TABLE IF EXISTS `states`;
		CREATE TABLE `states` (
		  `id` smallint(11) NOT NULL auto_increment,
		  `state` varchar(50) NOT NULL,
		  PRIMARY KEY  (`id`)
		) ENGINE=MyISAM AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;
	
 
 

Backend: index.php

 

These are the HTML part from a users perspective of the dropdownlist codes in which I am displaying the database records using PHP-JSON-JQUERY and PHP-XML-JQUERY.

Take note of the following attribute ID's related to JSON codes, these are all important on the JQUERY part of the code.

JSON

id="json_options" - Attribute ID from JSON select option where a user will choose what to display on the drop down list
id="json_states" - Attribute ID of the select option where the results from a JSON query will be populated
id="json_alertResponse" - can be deleted - for debugging purposes only - Attribute ID of the option to check what is the JSON result
id="json_link" - can be deleted - for debugging purposes only - Attribute ID of the option to see the actual JSON file from the recent JQUERY request

JSON

		

alert JSON response

 

Take note of the following attribute ID's related to XML codes, these are all important on the JQUERY part of the code.

XML

id="xml_options" - Attribute ID from XML select option where a user will choose what to display on the drop down list
id="xml_states" - Attribute ID of the select option where the results from a XML query will be populated
id="xml_alertResponse" - can be deleted - for debugging purposes only - Attribute ID of the option to check what is the XML result
id="xml_link" - can be deleted - for debugging purposes only - Attribute ID of the option to see the actual XML file from the recent JQUERY request

XML

		

alert XML response

 
 

JQUERY:

 

I will only go over the important parts of the jQuery codes, If you need to know about a specific block of code I did not explain, please post it on the comment section below and I will help you understand what it does.

I've created a jquery function that will trigger the on change event of the select option with attribute id = json_options. The code will initially get and store in a variable the value of select option with attribute id = json_options and will then pass it on the jQuery function .getJSON() that will be using a GET HTTP request and it will load JSON-encoded data from the server. The JSON result will be stored in a variable data which will be looped and will append each record on the select option with attribute id = json_states

Note: Since the code is getting a single value from the current object, var json_option = $("#json_options").val(); can also be written as var json_option = $(this).val();

JSON

		$("#json_options").change(function(){
			var json_option = $("#json_options").val();
			
			if($('#json_alertResponse').is(':checked')){
				var json_alertResponse = 1;
			}
			else{
				var json_alertResponse = 0;
			}
			
			$("#json_states").empty().append(""); //show loading...
			
			var json_url = "files/RNELdotNET_populateStates_json.php?json_option=" + encodeURIComponent(json_option);
			$.getJSON(json_url,function(data){
				if(json_alertResponse){
					alert(data.id);
					alert(data.state);
				}
				$("#json_states").empty(); //clear states selections
				if(data==""){
					$("#json_states").append("");
				}
				else{
					for(i=0; i<data.id.length; i++){
						$("#json_states").append("<option value=\"" + data.id[i]  + "\">" + data.state[i]  + "</option>");
					}
				}
				
				$("#json_link").html("<a href=\"" + json_url + "\" class=\"siteTutorial\" target=\"_blank\">view JSON output file</a>");
			});
			
			return false;
		});
	
 

The jQuery code for XML is pretty the same as the jQuery code I've used for JSON except that .ajax() is used in replacement of .getJSON() and the result is a XMLDocument object.

XML

		
		$("#xml_options").change(function(){
			var xml_option = $("#xml_options").val();
			
			if($('#xml_alertResponse').is(':checked')){
				var xml_alertResponse = 1;
			}
			else{
				var xml_alertResponse = 0;
			}
			
			$("#xml_states").empty().append(""); //show loading...
			
			var xml_url = "files/RNELdotNET_populateStates_xml.php?xml_option=" + encodeURIComponent(xml_option);
			$.ajax({
				type: "GET",
				url: xml_url,
				dataType: "xml",
				success: function(xml){
					if(xml_alertResponse){
						alert(xml + " " + $(xml).find("item").length + " items found");
					}
					$("#xml_states").empty(); //clear states selections
					var countTotal = $(xml).find("item").length;
					if(countTotal>0){
						$(xml).find("item").each(function(){
							var id = $(this).find("id").text();
							var title = $(this).find("title").text();
							$("#xml_states").append("<option value=\"" + id  + "\">" + title  + "</option>");
						});
					}
					else{
						$("#xml_states").append("<option value=\"0\">No states found</option>");
					}
					
					$("#xml_link").html("<a href=\"" + xml_url + "\" class=\"siteTutorial\" target=\"_blank\">view XML output file</a>");
				}
			});
			
			return false;
		});
	
 
 

RNELdotNET_populateStates_json.php:

 

From the jQuery function .getJSON() made from the jQuery code above, this is the PHP file where the jSon request is sent to.

The PHP code will check the occurence and value of parameter json_option, set a default value for variable $json_option that will be used to create an extended mysql statement to query the TABLE states. The results will be loaded on variable $json which is an array().

The end of the code will echo the results stored in variable $json encoded using json_encode() with proper header header('Content-Type: text/javascript; charset=UTF-8');

JSON

		$json_option = isset($_GET['json_option']) ? (!empty($_GET['json_option']) ? htmlspecialchars($_GET['json_option']) : 0) : 0;
		if(!$json_option) $json_option=1; //set default to "view all" if var $json_option is not set or is empty
		
		/*
		 * alternative for the above statement is an option to kill the script if var $json_option is not set or is empty
		 * if(!$json_option) die(); //kill the script if var $json_option is not set or is empty
		 */
		
		if($json_option!=1){ //create sql statement if the user wants a specific option to be displayed - else do nothing 
			$sql_option = " WHERE state REGEXP '^" . mysql_real_escape_string($json_option) . "'";
		}
		
		$json = array(); //set a variable for the results
		
		$sql = "SELECT * FROM states";
		$sql .= $sql_option;
		$sql = mysql_query($sql);
		while($row = mysql_fetch_object($sql)){
			$json['id'][] = $row->id;
			$json['state'][] = $row->state;
		}
		mysql_free_result($sql);
		
		header('Content-Type: text/javascript; charset=UTF-8'); //set header
		echo json_encode($json); //display records in json format using json_encode
	
 
 

RNELdotNET_populateStates_xml.php:

 

This PHP file on the other hand, is where the jQuery function .ajax() sent the XML request.

Like on RNELdotNET_populateStates_json.php PHP file, this also checks the occurence and value of parameter xml_option, set a default value for variable $xml_option that will again be used to create an extended mysql statement to query the TABLE states. The results will be loaded on an array() variable $xml.

The end of the code will also echo the results this time stored in variable $xml. The header used is header("Content-type: text/xml; charset=ISO-8859-1");

XML

		$xml_option = isset($_GET['xml_option']) ? (!empty($_GET['xml_option']) ? htmlspecialchars($_GET['xml_option']) : 0) : 0;
		if(!$xml_option) $xml_option=1; //set default to "view all" if var $xml_option is not set or is empty
		
		/*
		 * alternative for the above statement is an option to kill the script if var $xml_option is not set or is empty
		 * if(!$xml_option) die(); //kill the script if var $xml_option is not set or is empty
		 */
		
		if($xml_option!=1){ //create sql statement if the user wants a specific option to be displayed - else do nothing 
			$sql_option = " WHERE state REGEXP '^" . mysql_real_escape_string($xml_option) . "'";
		}
		
		$xml = array(); //set a variable for the results
		
		$sql = "SELECT * FROM states";
		$sql .= $sql_option;
		$sql = mysql_query($sql);
		while($row = mysql_fetch_object($sql)){
			$xml['id'][] = $row->id;
			$xml['state'][] = $row->state;
		}
		mysql_free_result($sql);
		
		$xml_result = '<?xml version="1.0" encoding="iso-8859-1"?>';
		$xml_result .= '
		<rss version="2.0"<';
		$xml_result .= '
			<channel>
				<title>RNELdotNET Webmaster Tutorials</title> 
				<link>www.rnel.net</link>
				<description>A sample on how to populate dropdown list from a database using php-json-jquery and php-xml-jquery</description>
				<language>en-us<>
		';
			for($i=0; $i<count($xml['id']); $i++){
				$xml_result .= '
					
						' . $xml['id'][$i] . '
						' . $xml['state'][$i] . '
					
				';
			}
		$xml_result .= '
			
		
		';
		header("Content-type: text/xml; charset=ISO-8859-1"); //set header
		echo $xml_result; //display records in xml format
	
 
 

Conclusion:

 

These are all simple codes that can be used in any of your web development projects, it doesn't matter if you are an experienced or only a new web developer to try and implement this Webmaster Tutorial, it only takes a little time to read and simple comprehension about all the given codes. You can explore and think about what you could do with it and use it to meet your coding or programming requirements. I've used this, for example, to populate Php Tutorials here on RNELdotNET.