Index
 |
Ajax+php+sql=simply Superb!(with Visitor Tracking) :: Section 2 (retrive Values From Database And Dynamic Update!) - A small tutorial to explain integrating php, ajax and MySQL Database | ||
Discussion by Niru with 5 Replies.
Last Update: January 14, 2010, 6:43 am | |||
 |
|||
Hi all..
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:
<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:
$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:
$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:
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
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:
CODE
<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:
CODE
<?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:
CODE
<?$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
Sun Apr 15, 2007 Reply New Discussion
Hi..
Thanks for approving my small tutorial!!
Let me explain the process flow in this tutorial!
I had created one php page to retrieve database values and display them:
php_ajax_i...ated_output.php
Then One page for sending the values in the database and included/called the above php file inside this page:
http://www.fun.niranvv.com/test/php_ajax_i...ated_script.php
(You can see like this: Hacking Attempt!! or No Input Values )
The code will get values from the query string! If no query string is there, then it wont insert any data to the database!
But You can insert manually like this:
php_ajax_integrated_script.php?word=myWord
Now One new page that is the integration of all the above pages!
http://www.fun.niranvv.com/test/php_ajax_integrated.php
Here, clicking the button will calls the file: php_ajax_integrated_script.php?word=myWord
Thus it will insert the values in the database!
And will display the datas using php_ajax_integrated_output.php
If you have any douts regarding this, please post here! I will try to help you!!
Thanks,
Niru
Thanks for approving my small tutorial!!
Let me explain the process flow in this tutorial!
I had created one php page to retrieve database values and display them:
php_ajax_i...ated_output.php
Then One page for sending the values in the database and included/called the above php file inside this page:
http://www.fun.niranvv.com/test/php_ajax_i...ated_script.php
(You can see like this: Hacking Attempt!! or No Input Values )
The code will get values from the query string! If no query string is there, then it wont insert any data to the database!
But You can insert manually like this:
php_ajax_integrated_script.php?word=myWord
Now One new page that is the integration of all the above pages!
http://www.fun.niranvv.com/test/php_ajax_integrated.php
Here, clicking the button will calls the file: php_ajax_integrated_script.php?word=myWord
Thus it will insert the values in the database!
And will display the datas using php_ajax_integrated_output.php
If you have any douts regarding this, please post here! I will try to help you!!
Thanks,
Niru
Fri Apr 20, 2007 Reply New Discussion
I'm updating the code with Reference URL!!
Please check here
I will post the updated code details soon!!
(I'm adding some more functions also! Will post all together later!)
Please check here
I will post the updated code details soon!!
(I'm adding some more functions also! Will post all together later!)
Tue Apr 24, 2007 Reply New Discussion
Hi great work on this. I was able to make a new report/error reporting system with this tutorial for work. But i have a question. Is there a way to reload a already logged report into the input screen so it can be altered after? This function is needed for our logistics / couriers desk as they have to be able to re-edit already entered data.
Is there a way? Should be possible to reload everything into the input screen on ID ? As we are als able to delete records fromt the database.
I hope someone can answer as i see this topic is quite old.
Kind regards.
Is there a way? Should be possible to reload everything into the input screen on ID ? As we are als able to delete records fromt the database.
I hope someone can answer as i see this topic is quite old.
Kind regards.
Fri Mar 6, 2009 Reply New Discussion
i want tofetch data fromdatabase through for loop and want to show in a tableAjax+php+sql=simply Superb!(with Visitor Tracking) :: Section 2 (retrive Values From Database And Dynamic Update!)
<?phpInclude ('connection.Php');$obj=new dbConnect;$dbh=$obj->dataBase();
?>
<table bgcolor="#FF3333" cellpadding="0" cellspacing="0" border="0"> <tr> <td><a href="form.Php">Add New User</a></td> </tr></table><table bgcolor="#CC9900" cellpadding="0" cellspacing="0" border="1"> <tr> <td bgcolor="#CC99FF">ID</td> <td bgcolor="#FF66FF">First name</td> <td bgcolor="#FF9999">Last name</td> <td bgcolor="#CC66CC">Country</td> <td bgcolor="#FF9999">City</td> <td bgcolor="#FF33CC">Cell_num</td> </tr>
<?$q="select main.Id, main.F_name,main.L_name,country.Country,city.City,main.Cell_num from main, country,city where main.Country_id=country.Country_id and main.City_id=city.City_id";$exel=mysql_query($q);While($get=mysql_fetch_array($q)){?>
<tr> <td bgcolor="#993333"><? echo $get[1] ?></td> <td bgcolor="#FF6600"><? echo $get[2] ?></td> <td bgcolor="#663399"><? echo $get[3] ?></td> <td bgcolor="#CC3366"><? echo $get[4] ?></td> <td bgcolor="#990099"><? echo $get[5] ?></td> <td bgcolor="#CC3366"><? echo $get[6] ?></td> <!----<td bgcolor="#999933"><a href="edit2.Php?id=<? echo $get[0];?>">Edit</a></td> <td bgcolor="#999933"><a href="delete.Php?id=<? echo $get[0];?>">Delete</a></td>---></tr><? }?>
</table><? $obj->close($dbh);?>
-reply by neelamThu Aug 27, 2009 Reply New Discussion
questionAjax+php+sql=simply Superb!(with Visitor Tracking) :: Section 2 (retrive Values From Database And Dynamic Update!)
Hi
I want to fetch data from database when it's updated and show updated row on my page without no action from user.(e.G no submit, no typing, etc)
thanks
Maryam
Thu Jan 14, 2010 Reply New Discussion
 |
 |
|
 |
 |
| Reply / Comment | Ask a Question? | Share / Bookmark | E-Mail a Friend |
 Effective Website Promotion Without Spamming! Get traffic to your website without bein... (21)
|
(15) Coding Html Properly In The New Age 
|