Update HTML when change is noticed in MySQL Database

php mysql ajax html

15,806

Solution 1

This process is called polling. There are basically two ways of doing this which are long polling and short polling

In Short polling you basically make a timer and get info from a php file which outputs db data every few second or so and then you compare data to see if data has been updated or not.

Then there is long polling which is preferred as it puts less pressure on the server. FB uses long polling. In this process what you basically do is you make a request to a a php file and the php file doesn't response until there is update in database so instead of making a ajax request to php file every few seconds here it is done every minute or so putting less pressure on server.

You can find long polling example here https://techoctave.com/c7/posts/60-simple-long-polling-example-with-javascript-and-jquery

More examples here https://gist.github.com/jhbsk/4353139

Solution 2

If you are using php, than you may follow this code-

In your html,

<script type="text/javascript" src="http://ajax.googleapis.com/ajax/
libs/jquery/1.3.0/jquery.min.js"></script>
<script type="text/javascript">
var auto_refresh = setInterval(
function ()
{
$('#load_tweets').load('record_count.php').fadeIn("slow");
}, 1000); // refresh every 1000 milliseconds

<body>
<div id="load_tweets"> </div>
</body>

In you PHP (record_count.php) file-

<?php
include("db.php");
$search_word=$_GET['q'];
$sql = mysqli_query($db,"Select number form Users");
$record_count=mysqli_num_rows($sql);
//Display count.........
echo $record_count;
?>

Ref: http://www.9lessons.info/2009/07/auto-load-refresh-every-10-seconds-with.html

Solution 3

The short answer, is that once the browser, has the page, that’s the end of the connection. There is no way for the server to inform the browser that something has happened.

However, using JavaScript, you can make post hoc requests t the server, a process known as Ajax. For this, you need 2 parts:

a PHP script which responds to a request and sends back a result
a JavaScript which sends the request and process the result.

The PHP script can be relatively short and simple:

<?php
    $id=@intval($_GET['id']);
    //  get data from database into an array
    print json_encode($result);
?>

And the JavaScript just needs to send the request and handle the response. The trick is to do this periodically, such as with window.setInterval.

var xhr=new XMLHttpRequest();
xhr.onload=doit;
var url='…?id=…';
xhr.open('get',url,true);

window.setInterval(poll,1000);  // every second

function poll() {
    xhr.send(null); 
}

function doit() {
    var result=this.response;
    result=JSON.parse(result);
    //  etc
}

In the absence of specific data, the code above is untested, but this is roughly how it should work.

15,806

Author by

Coderax

Updated on June 28, 2022

Comments

Coderax almost 2 years
So sorry if this is a total newbie question, but I can't seem to find any answers to my question out here on the great web...

I'm building a site, where it depends that some of the data on the page needs to be updated automatically....

More specific, when a change is made in the MySQL Database. Let's say for instance that the value of a row in the database is 10, and that changes to 11, I need the page to automatically get this from the database, and update it on the site. Also, if possible, is there any way I could make the numbers "pop out" a little bit, when the actual data is changing?
```
<div class="container hero-unit">
    <h1>Test Data Change</h1>
    <p>231</p>
    <p>7</p>
    <p>14532</p>
</div>
```
Let' say that these numbers are fetched from the database, how would I use 'AJAX' to auto change this data when a change is made to the database?

If this is possible at all, I would appreciate every contribution.....
Coderax about 7 years

Would you care to make an example of this, with long polling, using the database, and the actual update of the data itself? :)
Zeus about 7 years

@Coderax github.com/panique/php-long-polling This is a full example with client and server side code.
Coderax about 7 years

I like what I see, and I'm guessing this is kind of what I'm looking for, but I can't get it to work. When I use the sample data.txt file, and save the changes, I'm not getting any "Response from server", the page is just blank.
Zeus about 7 years

@Coderax It works pretty well. Perhaps you need to change the url in client.js pointing at php file to match the directory path on your system. I had same problem changed it and worked like a charm.
Coderax about 7 years

That did the trick! :D Now I just need to figure out a way to make in pop out/fade in every time there is a change! :D