w3schools    w3Schools
Search W3Schools :
   
HOME HTML CSS XML JAVASCRIPT ASP PHP SQL MORE...   References Examples Forum About
ADVERTISEMENTS

XML Certification
Download XML editor
Custom Programming
 
Table of contents
PHP Basic
PHP HOME
PHP Intro
PHP Install
PHP Syntax
PHP Variables
PHP String
PHP Operators
PHP If...Else
PHP Switch
PHP Arrays
PHP Looping
PHP Functions
PHP Forms
PHP $_GET
PHP $_POST

PHP Advanced
PHP Date
PHP Include
PHP File
PHP File Upload
PHP Cookies
PHP Sessions
PHP E-mail
PHP Secure E-mail
PHP Error
PHP Exception
PHP Filter

PHP Database
MySQL Introduction
MySQL Connect
MySQL Create
MySQL Insert
MySQL Select
MySQL Where
MySQL Order By
MySQL Update
MySQL Delete
PHP ODBC

PHP XML
XML Expat Parser
XML DOM
XML SimpleXML

PHP and AJAX
AJAX Introduction
XMLHttpRequest
AJAX Suggest
AJAX XML
AJAX Database
AJAX responseXML
AJAX Live Search
AJAX RSS Reader
AJAX Poll

PHP Reference
PHP Array
PHP Calendar
PHP Date
PHP Directory
PHP Error
PHP Filesystem
PHP Filter
PHP FTP
PHP HTTP
PHP Libxml
PHP Mail
PHP Math
PHP Misc
PHP MySQL
PHP SimpleXML
PHP String
PHP XML
PHP Zip

PHP Quiz
PHP Quiz
PHP Exam

Selected Reading
Web Statistics
Web Glossary
Web Hosting
Web Quality

W3Schools Tutorials
W3Schools Forum

Helping W3Schools

 

PHP and AJAX MySQL Database Example

Previous Next

AJAX can be used for interactive communication with a database.


AJAX Database Example

In the AJAX example below we will demonstrate how a web page can fetch information from a MySQL database using AJAX technology.


Select a Name in the Box Below

Select a User:

User info will be listed here.

This example consists of four elements:

  • a MySQL database
  • a simple HTML form
  • a JavaScript
  • a PHP page

The Database

The database we will be using in this example looks like this:

id FirstName LastName Age Hometown Job
1 Peter Griffin 41 Quahog Brewery
2 Lois Griffin 40 Newport Piano Teacher
3 Joseph Swanson 39 Quahog Police Officer
4 Glenn Quagmire 41 Quahog Pilot


The HTML Form

The example above contains a simple HTML form and a link to a JavaScript:

<html>
<head>
<script src="selectuser.js"></script>
</head>
<body>
<form> 
Select a User:
<select name="users" onchange="showUser(this.value)">
<option value="1">Peter Griffin</option>
<option value="2">Lois Griffin</option>
<option value="3">Glenn Quagmire</option>
<option value="4">Joseph Swanson</option>
</select>
</form>
<p>
<div id="txtHint"><b>User info will be listed here.</b></div>
</p>
</body>
</html>

Example Explained - The HTML Form

As you can see it is just a simple HTML form with a drop down box called "users" with names and the "id" from the database as option values.

The paragraph below the form contains a div called "txtHint". The div is used as a placeholder for info retrieved from the web server.

When the user selects data, a function called "showUser()" is executed. The execution of the function is triggered by the "onchange" event.

In other words: Each time the user changes the value in the drop down box, the function showUser() is called.


The JavaScript

This is the JavaScript code stored in the file "selectuser.js":

var xmlHttp;
function showUser(str)
{ 
xmlHttp=GetXmlHttpObject();
if (xmlHttp==null)
 {
 alert ("Browser does not support HTTP Request");
 return;
 }
var url="getuser.php";
url=url+"?q="+str;
url=url+"&sid="+Math.random();
xmlHttp.onreadystatechange=stateChanged;
xmlHttp.open("GET",url,true);
xmlHttp.send(null);
}
function stateChanged() 
{ 
if (xmlHttp.readyState==4 || xmlHttp.readyState=="complete")
 { 
 document.getElementById("txtHint").innerHTML=xmlHttp.responseText;
 } 
}
function GetXmlHttpObject()
{
var xmlHttp=null;
try
 {
 // Firefox, Opera 8.0+, Safari
 xmlHttp=new XMLHttpRequest();
 }
catch (e)
 {
 //Internet Explorer
 try
  {
  xmlHttp=new ActiveXObject("Msxml2.XMLHTTP");
  }
 catch (e)
  {
  xmlHttp=new ActiveXObject("Microsoft.XMLHTTP");
  }
 }
return xmlHttp;
}

Example Explained

The stateChanged() and GetXmlHttpObject functions are the same as in the PHP AJAX Suggest chapter, you can go to there for an explanation of those.

The showUser() Function

If an item in the drop down box is selected the function executes the following:

  1. Calls on the GetXmlHttpObject function to create an XMLHTTP object
  2. Defines the url (filename) to send to the server
  3. Adds a parameter (q) to the url with the content of the dropdown box
  4. Adds a random number to prevent the server from using a cached file
  5. Call stateChanged when a change is triggered
  6. Opens the XMLHTTP object with the given url.
  7. Sends an HTTP request to the server

The PHP Page

The server page called by the JavaScript, is a simple PHP file called "getuser.php".

The page is written in PHP and uses a MySQL databse.

The code runs a SQL query against a database and returns the result as an HTML table:

<?php
$q=$_GET["q"];

$con = mysql_connect('localhost', 'peter', 'abc123');
if (!$con)
 {
 die('Could not connect: ' . mysql_error());
 }

mysql_select_db("ajax_demo", $con);

$sql="SELECT * FROM user WHERE id = '".$q."'";

$result = mysql_query($sql);

echo "<table border='1'>
<tr>
<th>Firstname</th>
<th>Lastname</th>
<th>Age</th>
<th>Hometown</th>
<th>Job</th>
</tr>";

while($row = mysql_fetch_array($result))
 {
 echo "<tr>";
 echo "<td>" . $row['FirstName'] . "</td>";
 echo "<td>" . $row['LastName'] . "</td>";
 echo "<td>" . $row['Age'] . "</td>";
 echo "<td>" . $row['Hometown'] . "</td>";
 echo "<td>" . $row['Job'] . "</td>";
 echo "</tr>";
 }
echo "</table>";

mysql_close($con);
?>

Example Explained

When the query is sent from the JavaScript to the PHP page the following happens:

  1. PHP opens a connection to a MySQL server
  2. The "user" with the specified name is found
  3. A table is created and the data is inserted and sent to the "txtHint" placeholder

Previous Next


Make your web applications look like a million bucks

FusionCharts   

Most web applications today use boring methods to present data to their viewers using grids or simple HTML tables. FusionCharts induces "life" into the web applications by converting monotonous data into lively charts, gauges & maps.

FusionCharts works with all technologies like ASP, ASP.NET, PHP, ColdFusion, Ruby on Rails, JSP, HTML pages etc. and connects to any database to render animated & interactive charts. It takes less than 15 minutes and no expertise whatsoever to build your first chart and just a glance of it to captivate your audience. This fact is endorsed by our 12,000 customers and 150,000 users which include a majority of the Fortune 500 companies. And yeah, your applications could look like a million bucks by spending just $69.

So go ahead, download your copy of FusionCharts and start "wow-ing" your customers now!


 
WEB HOSTING
Shopping Cart
ASP.NET
Shopping Cart
$15 Domain Name
Registration
Save $20 / year!
Buy UK Domain Names
Register Domain Names
Cheap Domain Names
Cheap Web Hosting
Best Web Hosting
PHP MySQL Hosting
Top 10 Web Hosting
UK Reseller Hosting
Web Hosting
FREE Web Hosting
WEB BUILDING
Website Templates
Flash Templates
Website Builder
Internet Business Opportunity
Custom Programming
FREE Trial or Demo
Web Content Manager
Forms,Web Alerts,RSS
Download XML editor
FREE Flash Website
FREE Web Templates
EDUCATION
US Web Design Schools
HTML Certification
JavaScript Certification
XML Certification
PHP Certification
ASP Certification
Home HOME or Top of Page Validate   Validate   W3C-WAI level A conformance icon Printer Friendly  Printer Friendly

W3Schools is for training only. We do not warrant the correctness of its content. The risk from using it lies entirely with the user.
While using this site, you agree to have read and accepted our terms of use and privacy policy.
Copyright 1999-2009 by Refsnes Data. All Rights Reserved.