Jump to content
xisto Community
niran

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

By niran, in Websites and Web Designing

Recommended Posts

niran    0

niran
Posted (edited)

Hi all..

 

This the Section 2 for my previous tutorial about storing values in background using ajax!

 

Please have a look at here:

 

Xisto.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>
<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:

 

<?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:

 

<?
   $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

So, here is our final integrated page:

 

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

Edited by Niru (see edit history)

Share this post

Link to post
Share on other sites

niran    0

niran
Posted (edited)

Hi..

Thanks for approving my small tutorial!! :ph34r:

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://forums.xisto.com/no_longer_exists/

(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://forums.xisto.com/no_longer_exists/

 

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

Edited by Niru (see edit history)

Share this post

Link to post
Share on other sites

KillerSneak    0

KillerSneak
Posted

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.

Share this post

Link to post
Share on other sites

iGuest    3

iGuest
Posted
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 neelam

Share this post

Link to post
Share on other sites

iGuest    3

iGuest
Posted
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

Share this post

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Go To Topic Listing
×
×
  • Create New...

Important Information

Terms of Use | Privacy Policy | Guidelines | We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.

  I accept