This the Section 2 for my previous tutorial about storing values in background using ajax!
Please have a look at here:
Astahost.com/ajax-php-sql-simply-superb-visitor-tracking-t15502.html
Introduction!
We are going to create 2 background script files. One to get values from the main page input field and store all values including visitor details ( IP Address, Input Value, Visitor Agent etc) to database and the 2nd one for retrieving all values from database and display it in the screen! We are going to 2 separate pages first and then going to integrate all those 2 pages to the main user interface page!
So, let us start guys!!
Stage 1: Getting values from the database and displaying the same
We already inserted some values to the database as described here
Now we have to retrieve all values by using query and to display them in screen!!
So, here is the query to retrieve values:
SELECT * FROM ajax ORDER BY slno DESC
This will retrieve all values from the database and will be stored in some buffer! We have to show each and every field values in some table cells!
So, here comes the main code for that!
$query="SELECT * FROM ajax ORDER BY slno DESC";
$result=mysql_query($query);
echo "<table>
<tr>
<th>Sl Num</th>
<th>Text Msg</th>
<th>IP Address</th>
<th>Hostname</th>
<th>Visitor Agent</th>
<th>Time</th>
</tr>\n";
while($row=mysql_fetch_array($result))
{
echo "<tr bgcolor=#2F2F2F>\n";
echo "\n<td align=center>". $row['slno'] . "</td>";
echo "\n<td>". $row['test'] . "</td>";
echo "\n<td>". $row['ipaddr'] . "</td>";
echo "\n<td>". gethostbyaddr($row['ipaddr']) . "</td>";
echo "\n<td>". $row['agent'] . "</td>";
echo "\n<td>". date("D, d M y g:i:s a",$row['timer']+45000) . "</td>";
echo "\n</tr>";
}
echo "\n</table>\n";
Here we already stored the visitor ipaddress. We can get hostname from the ip address by using the function:
gethostbyaddr();
The above while loop will execute for the n number of times (* n = number of records retrieved)
And we can get the timestamp and we can format it using php date() function!
More details about Date function available here
Ok, I think, now the idea is clear! This will display all values in some table format!
Now we have to place some checkbox in each and every row and clicking the delete button should delete the selected values!
( Got this checkbox deletion idea from here )
So we are midifying the script page like this:
if($_POST['perform']) {
foreach($_POST as $id) { // This will loop through the checked checkboxes
mysql_query("DELETE FROM ajax WHERE slno='$id' LIMIT 1"); // Change yourtable and id. This deletes the record from the database
}
}
Thats over in the display part! So finished page will look like this:
HTML
<html>
<head>
</head>
<body>
<form name="action" id="action" method="post" action="<?=$_SERVER['PHP_SELF']?>">
<?
$MyUsername="username";
$MyPassword="password";
$MyDatabase="database";
mysql_connect(localhost,$MyUsername,$MyPassword);
@mysql_select_db($MyDatabase) or die( "Unable to select database");
if($_POST['perform']) {
foreach($_POST as $id) { // This will loop through the checked checkboxes
mysql_query("DELETE FROM ajax WHERE slno='$id' LIMIT 1"); // Change yourtable and id. This deletes the record from the database
}
}
$query="SELECT * FROM ajax ORDER BY slno DESC";
$result=mysql_query($query);
$num=mysql_numrows($result);
//echo date("D M d, Y g:i a");
//echo time();
//echo date("D, d M y g:i:s a",time()+45000);
echo "<table width=100% border=0 align=center cellpadding=4 cellspacing=1 bordercolor=#333333 bgcolor=#FCFCFC class=sample id=sortTable>
<tr bgcolor=#990000>
<th height=30 align=center><span class=tab_head>Sl Num</span></th>
<th height=30><span class=tab_head>Text Msg</span></th>
<th height=30><span class=tab_head>IP Address</span></th>
<th height=30><span class=tab_head>Hostname</span></th>
<th height=30><span class=tab_head>Visitor Agent</span></th>
<th height=30><span class=tab_head>Time</span></th>
</tr>\n";
while($row=mysql_fetch_array($result))
{
echo "<tr bgcolor=#2F2F2F>\n";
echo "\n<td align=center>". $row['slno'] . "<input type=checkbox name=". $row['slno'] . " id=". $row['slno'] . " value=". $row['slno'] . " /></td>";
echo "\n<td>". $row['test'] . "</td>";
echo "\n<td>". $row['ipaddr'] . "</td>";
echo "\n<td>". gethostbyaddr($row['ipaddr']) . "</td>";
echo "\n<td>". $row['agent'] . "</td>";
echo "\n<td>". date("D, d M y g:i:s a",$row['timer']+45000) . "</td>";
echo "\n</tr>";
}
echo "\n</table>\n";
mysql_close();
?>
<input type="submit" name="perform" id="perform" value="Delete Selected" />
</form>
</body>
</html>
<head>
</head>
<body>
<form name="action" id="action" method="post" action="<?=$_SERVER['PHP_SELF']?>">
<?
$MyUsername="username";
$MyPassword="password";
$MyDatabase="database";
mysql_connect(localhost,$MyUsername,$MyPassword);
@mysql_select_db($MyDatabase) or die( "Unable to select database");
if($_POST['perform']) {
foreach($_POST as $id) { // This will loop through the checked checkboxes
mysql_query("DELETE FROM ajax WHERE slno='$id' LIMIT 1"); // Change yourtable and id. This deletes the record from the database
}
}
$query="SELECT * FROM ajax ORDER BY slno DESC";
$result=mysql_query($query);
$num=mysql_numrows($result);
//echo date("D M d, Y g:i a");
//echo time();
//echo date("D, d M y g:i:s a",time()+45000);
echo "<table width=100% border=0 align=center cellpadding=4 cellspacing=1 bordercolor=#333333 bgcolor=#FCFCFC class=sample id=sortTable>
<tr bgcolor=#990000>
<th height=30 align=center><span class=tab_head>Sl Num</span></th>
<th height=30><span class=tab_head>Text Msg</span></th>
<th height=30><span class=tab_head>IP Address</span></th>
<th height=30><span class=tab_head>Hostname</span></th>
<th height=30><span class=tab_head>Visitor Agent</span></th>
<th height=30><span class=tab_head>Time</span></th>
</tr>\n";
while($row=mysql_fetch_array($result))
{
echo "<tr bgcolor=#2F2F2F>\n";
echo "\n<td align=center>". $row['slno'] . "<input type=checkbox name=". $row['slno'] . " id=". $row['slno'] . " value=". $row['slno'] . " /></td>";
echo "\n<td>". $row['test'] . "</td>";
echo "\n<td>". $row['ipaddr'] . "</td>";
echo "\n<td>". gethostbyaddr($row['ipaddr']) . "</td>";
echo "\n<td>". $row['agent'] . "</td>";
echo "\n<td>". date("D, d M y g:i:s a",$row['timer']+45000) . "</td>";
echo "\n</tr>";
}
echo "\n</table>\n";
mysql_close();
?>
<input type="submit" name="perform" id="perform" value="Delete Selected" />
</form>
</body>
</html>
And the finished page is available here:
Fun.niranvv.com/test/php_ajax_output.php
Stage 2: Embed the page inside main background script file
We can view the results in separate as seen in the stage 1!
But we have to display the table inside the main page immediately after inserting the values!
So, we can copy and paste the script to the main background script page called by clicking the enter button!
But, That will complicate the pages! So, Im going to include the script file inside the main background script file!
We can include like this:
echo include("php_ajax_integrated_output.php")
here "php_ajax_integrated_output.php" is used to output the values ( created in stage 1 )
So the main Background script file will be like this:
HTML
<?php
$dbhost = "localhost";
$dbuser = "username";
$dbpass = "password";
$dbname = "database";
//Connect to MySQL Server
mysql_connect($dbhost, $dbuser, $dbpass);
//Select Database
mysql_select_db($dbname) or die(mysql_error());
if (isset($_SERVER['HTTP_X_FORWARDED_FOR'])) {
$ClientIP = $_SERVER['HTTP_X_FORWARDED_FOR'];
} else {
$ClientIP = $_SERVER['REMOTE_ADDR'];
}
//$ClientHost = gethostbyaddr($ClientIP);
$ClientAgent = $_SERVER['HTTP_USER_AGENT'];
$MyTimeStamp = time();
// Retrieve data from Query String
$word = $_GET['word'];
// Escape User Input to help prevent SQL Injection
$word = mysql_real_escape_string($word);
//build query
$query = "INSERT INTO ajax(ipaddr, agent, test, timer) VALUES('$ClientIP', '$ClientAgent', '$word','$MyTimeStamp')";
//Execute query
// Exit if calling directly the script file!
if ($word != "")
{
$qry_result = mysql_query($query) or die(mysql_error());
echo "Updated Successfully with values IP :$ClientIP<br>Visitor Agent: $ClientAgent<br>Input Word: $word<br>Current Time Stamp: $MyTimeStamp";
}
else
{
echo '<b>Hacking Attempt!! or No Input Values</b><br><br>';
}
// Add Output Table
echo include("php_ajax_integrated_output.php")
?>
$dbhost = "localhost";
$dbuser = "username";
$dbpass = "password";
$dbname = "database";
//Connect to MySQL Server
mysql_connect($dbhost, $dbuser, $dbpass);
//Select Database
mysql_select_db($dbname) or die(mysql_error());
if (isset($_SERVER['HTTP_X_FORWARDED_FOR'])) {
$ClientIP = $_SERVER['HTTP_X_FORWARDED_FOR'];
} else {
$ClientIP = $_SERVER['REMOTE_ADDR'];
}
//$ClientHost = gethostbyaddr($ClientIP);
$ClientAgent = $_SERVER['HTTP_USER_AGENT'];
$MyTimeStamp = time();
// Retrieve data from Query String
$word = $_GET['word'];
// Escape User Input to help prevent SQL Injection
$word = mysql_real_escape_string($word);
//build query
$query = "INSERT INTO ajax(ipaddr, agent, test, timer) VALUES('$ClientIP', '$ClientAgent', '$word','$MyTimeStamp')";
//Execute query
// Exit if calling directly the script file!
if ($word != "")
{
$qry_result = mysql_query($query) or die(mysql_error());
echo "Updated Successfully with values IP :$ClientIP<br>Visitor Agent: $ClientAgent<br>Input Word: $word<br>Current Time Stamp: $MyTimeStamp";
}
else
{
echo '<b>Hacking Attempt!! or No Input Values</b><br><br>';
}
// Add Output Table
echo include("php_ajax_integrated_output.php")
?>
And I'm going to modify the included page like this for easy understanding:
HTML
<?
$MyUsername="username";
$MyPassword="password";
$MyDatabase="database";
mysql_connect(localhost,$MyUsername,$MyPassword);
@mysql_select_db($MyDatabase) or die( "Unable to select database");
$query="SELECT * FROM ajax ORDER BY slno DESC";
$result=mysql_query($query);
$num=mysql_numrows($result);
echo "<table width=100% border=1>
<tr>
<th align=center>Sl Num</th>
<th>Text Msg</th>
<th>IP Address</th>
<th>Hostname</th>
<th>Visitor Agent</th>
<th>Time</th>
</tr>\n";
while($row=mysql_fetch_array($result))
{
echo "<tr>\n";
echo "\n<td align=center>". $row['slno'] . "</td>";
echo "\n<td>". $row['test'] . "</td>";
echo "\n<td>". $row['ipaddr'] . "</td>";
echo "\n<td>". gethostbyaddr($row['ipaddr']) . "</td>";
echo "\n<td>". $row['agent'] . "</td>";
echo "\n<td>". date("D, d M y g:i:s a",$row['timer']+45000) . "</td>";
echo "\n</tr>";
}
echo "\n</table>\n";
mysql_close();
?>
$MyUsername="username";
$MyPassword="password";
$MyDatabase="database";
mysql_connect(localhost,$MyUsername,$MyPassword);
@mysql_select_db($MyDatabase) or die( "Unable to select database");
$query="SELECT * FROM ajax ORDER BY slno DESC";
$result=mysql_query($query);
$num=mysql_numrows($result);
echo "<table width=100% border=1>
<tr>
<th align=center>Sl Num</th>
<th>Text Msg</th>
<th>IP Address</th>
<th>Hostname</th>
<th>Visitor Agent</th>
<th>Time</th>
</tr>\n";
while($row=mysql_fetch_array($result))
{
echo "<tr>\n";
echo "\n<td align=center>". $row['slno'] . "</td>";
echo "\n<td>". $row['test'] . "</td>";
echo "\n<td>". $row['ipaddr'] . "</td>";
echo "\n<td>". gethostbyaddr($row['ipaddr']) . "</td>";
echo "\n<td>". $row['agent'] . "</td>";
echo "\n<td>". date("D, d M y g:i:s a",$row['timer']+45000) . "</td>";
echo "\n</tr>";
}
echo "\n</table>\n";
mysql_close();
?>
We are dynamically updating the main page with updated database field values!
Now the this is the process flow:
- User enter information and clicking the enter button ( php_ajax_integrated.php)
- Entered values will be send to background script (php_ajax_integrated_script.php) using query string
- The values will be inserted to the database and the background script will call another php page (php_ajax_integrated_output.php) to display updated values using echo include("file path")
- In that php_ajax_integrated_output.php , retrive all values from the database and display the values
- Inrtegrate all files and display output in the main page ( Please have a look at here for more info
Fun.niranvv.com/test/php_ajax_integrated.php
Thats all guys!!
If you have any doubt, Please reply here!
I will try to help you! ( Im a beginner only .. Yes guys!! U have to believe my words!! Just went through some online tutorials about php and ajax, and got some ideas! And tried to integrate all.. This page is the result of that!! And it took only 1-2 days to study all these functions! I Started from a big '0' only!! So, if you dont have any ideas, dont worry!! If you know pragramming concepts, u can also come up with this! all the best )
regards,
Niru
