This tutorial shows you how to create a Web page that extracts information from a MySQL database without refreshing the page (dynamic update). Javascript and PHP knowledge is required
In this tutorial we will create a web page that extracts data from a MySQL database without having to refresh the page. That is right… the web page will not be refreshed and the information will show up in the existing web page. — Impressed?
Before you start reading the tutorial, I recommend that you take a look at this script in action. This will give you an idea of what the end product will look like.
For this tutorial we will be creating two files:
mysql_insert.html (YES, HTML. This is the page that users will see)
mysql_insert.php (This file will do all the hard work and will return results to mysql_insert.html)
The Flow:
This approach makes use of JavaScript and updates elements in the parent page from a child page. You can pass variables to the child file and process information using PHP. Core concept for this tutorial was drawn from a tutorial posted on devshed.com. This tutorial takes the same approach but it also shows how you can add/query data from mysql on the fly.
This tutorial does not discuss the basics. If this is your first time creating a page that updates without having to reload, read this tutorial.
Lets Get Down and Dirty:
In order to create this functionality on your web-server, you will have to create a table in your mysql database. I created a table and called it php_js. This table will store the user information. Following is the code to create a table:
CREATE TABLE `php_js` (
`srno` INT NOT NULL AUTO_INCREMENT ,
`u_name` VARCHAR( 50 ) NOT NULL ,
`u_email` VARCHAR( 55 ) NOT NULL ,
PRIMARY KEY ( `srno` )
);The three columns that in the table are “srno”, “u_name” and “u_email”. Names will be stored in the u_name column and email addresses will be stored in the u_email column.
Now lets look at the code in the HTML and PHP files. Here is the code to the html file:
mysql_insert.html<html><br/>
<head><br/>
<script language="JavaScript"><!--<br/>
//these function from:<br/>
//http://www.devshed.com/c/a/PHP/PHP-and-JavaScript-Pooling-Your-Resources/<br/>
//this function calls the child file<br/>
function attach_file( p_script_url ) {<br/>
// create new script element, set its relative URL, and load it<br/>
script = document.createElement( 'script' );<br/>
script.src = p_script_url;<br/>
document.getElementsByTagName( 'head' )[0].appendChild( script );<br/>
}<br/>
<br/>
//this function updates the status box<br/>
function show_status( status_text ) {<br/>
document.getElementById('status').innerHTML = status_text;<br/>
}<br/>
//--><br/>
</script><br/>
<title>PHP MySQL and Javascripts Working Together </title><br/>
<!-- CSS Details --><br/>
<style type="text/css"><br/>
<!--<br/>
.table1 {<br/>
border: 1px solid #CC6600;<br/>
font-family: Verdana, Arial, Helvetica, sans-serif;<br/>
font-size: 10px;<br/>
font-style: normal;<br/>
text-transform: none;<br/>
}<br/>
body {<br/>
font-family: Verdana, Arial, Helvetica, sans-serif;<br/>
font-size: 8px;<br/>
background-position: center top;<br/>
margin: 0px;<br/>
padding: 15px;<br/>
}<br/>
input {<br/>
font-family: Verdana, Arial, Helvetica, sans-serif;<br/>
font-size: 10px;<br/>
font-style: normal;<br/>
font-weight: bold;<br/>
font-variant: normal;<br/>
color: #0099CC;<br/>
background-color: #FFFFCC;<br/>
border: 1px solid #FF9900;<br/>
}<br/>
.row1 {<br/>
background-color: #CCFFCC;<br/>
}<br/>
.style1 {<br/>
color: #FFFFFF;<br/>
font-weight: bold;<br/>
}<br/>
.row2 {<br/>
background-color: #FFFFFF;<br/>
}<br/>
--><br/>
</style><br/>
</head><br/>
<br/>
<body onLoad="javascript:attach_file( 'mysql_insert.php' ) ; show_status('Ready...'); "><br/>
<table width="600" border="0" cellpadding="1" cellspacing="3" class="table1"><br/>
<tr><br/>
<td width="88">Status Box: </td><br/>
<td width="493" bgcolor="#CCCCCC"><span id="status" /></span> </td><br/>
</tr><br/>
<tr><br/>
<td> </td><br/>
<td> </td><br/>
</tr><br/>
<tr><br/>
<td>Name</td><br/>
<td><input name="u_name" type="text" id="u_name" size="40" maxlength="40"></td><br/>
</tr><br/>
<tr><br/>
<td>Email</td><br/>
<td><input name="u_email" type="text" id="u_email" size="40" maxlength="45"><br/>
(email MUST contain the '@' and '.') </td><br/>
</tr><br/>
<tr><br/>
<td> </td><br/>
<td><input type="button" name="Button" value="Click Here to Insert into Mysql..." onClick="javascript:attach_file( 'mysql_insert.php?u_name=' + u_name.value + '&u;_email=' + u_email.value) ; show_status('Busy'); "></td><br/>
</tr><br/>
</table><br/>
<br/>
<br><br/>
Data from MySQL: <br><br/>
<br/>
<!-- Do not remove this or the table will not show up --><br/>
<span id="from_mysql"><br/>
</span><br/>
<br/>
<br><br/>
</body><br/>
</html>In the HTML file we have defined two functions (attach_file and show_hint). The attach_file() function calls up another file and appends the information to the parent (this) page. The show_status() function updates the text in the status box. When the page loads, the default text is set to “Ready…” but once the user presses the button to submit the information, the function is called again and the text is set to “Busy…”. The text in the status box will read “Busy…” until all the data has been processed and displayed in the parent window. This is a pretty nice tool and lets you know when the system is ready. You can further enhance the application and maybe display an image while the information is being loaded.
<span id="from_mysql">
</span> is the most important field in the html file. The table that you see (with user names and email addresses) is constructed within this span field. If you change the name (id) of this span, be sure to change the name in the mysql_insert.php file.
When the child window is called, parameters from input fields are passed along. You might have noticed that in the line that reads
onClick="javascript:attach_file( 'mysql_insert.php?u_name=' + u_name.value + '&u;_email=' + u_email.value) ; show_status('Busy');
Our other file is called mysql_insert.php and this is the file that adds and queries the database. All the information is stored in a variable and then javascript is used to update the information on parent window.
mysql_insert.php<?php<br/>
header( 'Content-Type: text/javascript' );<br/>
//database info: Change password and DB Name <br/>
$db = mysql_connect("localhost", "USER", "PASSWORD") or die (mysql_error());<br/>
mysql_select_db ("DATABASE_NAME", $db) or die (mysql_error());<br/>
<br/>
//get values if input fields<br/>
$u_name = addslashes($_GET['u_name']); //Name<br/>
$u_email = addslashes($_GET['u_email']); //Email<br/>
<br/>
//If name and email are NOT empty, insert into mysql<br/>
if (strlen($u_name)>1 and strlen($u_email)>1 and strstr($u_email,"@") and strstr($u_email,".") ) {<br/>
$insert = mysql_query("INSERT INTO php_js (u_name, u_email) VALUES ('$u_name', '$u_email')",$db) or die(mysql_error());<br/>
}<br/>
<br/>
//Now Refresh the table at the bottom id=from_mysql<br/>
$row_count = 0;<br/>
$output = "<table width="600" border="0" cellpadding="3" cellspacing="0" class="table1"><tr><td width="41" align="center" bgcolor="#3366CC"><span class="style1">Srno</span></td><td width="149" align="center" bgcolor="#3366CC"><span class="style1">Name</span></td><td width="384" align="center" bgcolor="#3366CC"><span class="style1">Email</span></td></tr>";<br/>
$result = mysql_query("SELECT srno, u_name, u_email FROM php_js",$db) or die (mysql_error());<br/>
While( $rows = mysql_fetch_array($result)) {<br/>
$srno = $rows['srno'];<br/>
$u_name = $rows['u_name'];<br/>
$u_email = $rows['u_email'];<br/>
$row_style = ($row_count % 2) ? "row1" : "row2";<br/>
$output .= "<tr class="$row_style"><td>$srno</td><td>$u_name</td><td>$u_email</td></tr>";<br/>
$row_count = $row_count + 1;<br/>
}<br/>
//Free Results<br/>
mysql_free_result($result);<br/>
$output .= "</table>";<br/>
?><br/>
from_mysql_obj = document.getElementById( 'from_mysql' );<br/>
from_mysql_obj.innerHTML = '<?php echo $output; ?>';<br/>
<br/>
<br/>
//update status box<br/>
my_status = document.getElementById( 'status' );<br/>
my_status.innerHTML = 'Ready...';<br/>
<br/>
//clear values from text fields<br/>
document.getElementById('u_name').value = '';<br/>
document.getElementById('u_email').value = '';We start off with PHP tags and define a header as
header( 'Content-Type: text/javascript' );This tells the browser to treat this file as a text file with javascripts. However within the file we have PHP functions that process in the information.
Next I have made a connection to the database. Be sure to change the Database Name and define your username and password.
The IF block is executed if the user has typed in a Name and a valid Email in the input fields. If these values were entered, the information is stored in the database.
Next, all the information from the table is queried and stored in a variable called $output. Once the information is stored, javascript’s getElementById method is used to update the <SPAN> tag that we created in the Parent window.
This is in no way a secure script. You will have to modify the script to make it secure.
If you have any questions about this tutorial, please contact our team at http://www.nabeelakhtar.net/contact/ or
Update: Please note that the website NabeelAkhtar.NET, previously referenced in this article, is no longer active as of 28/11/2023. We apologize for any inconvenience and are working to update the content with alternative resources. Thank you for your understanding.
for discussion go to http://forum.nabeelakhtar.net
More tutorials available at http://www.nabeelakhtar.net/tutorials/
Live Online Support available at http://www.nabeelakhtar.net
