Update HTML when change is noticed in MySQL Database

Question

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

@SumonSarker Yes, that's what I thought. Do you have some example code? :) — Coderax, Mar 16 '17 at 05:32
@lazy_coder I'll see that I can try to get, not really relevant for what I'm asking though.. — Coderax, Mar 16 '17 at 05:33
Ok, I will make an answer and post here for you about this solution. @Coderax — Sumon Sarker, Mar 16 '17 at 05:35
@Coderax for doing this kind of things Nodejs is the best thing via Nodejs using WebSockets it is possible so its better if you do some R&D on Nodejs and WebSockets. — Varun Malhotra, Mar 16 '17 at 05:37
Opps! Marked as Duplicates, So I can't post answer. Here is the solution. Create 2 files in your project. `index.php` and `ajax.php`, Here is the 2 files link `http://pastebin.com/MJPrJUFg` and `http://pastebin.com/4btnCA4h` @Coderax — Sumon Sarker, Mar 16 '17 at 06:43
@SumonSarker Cool, will try it out! But, could you edit the code, so it also fades in/pops out a little bit every time it refreshes? — Coderax, Mar 16 '17 at 06:49
@SumonSarker I'm also getting this error: Parse error: syntax error, unexpected 'foreach' (T_FOREACH) in C:\xampp\htdocs\assets\check\ajax.php on line 17 — Coderax, Mar 16 '17 at 07:01
Check variable `$dataFromDatabase` is a proper array. @Coderax — Sumon Sarker, Mar 16 '17 at 07:03
Maybe no need to use my code in `ajax.php` file. Just `Query Result` from database and `echo` the result to `ajax.php` file. @Coderax — Sumon Sarker, Mar 16 '17 at 07:08

score 9 · Answer 1 · answered Mar 16 '17 at 05:47

9

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

answered Mar 16 '17 at 05:47

Zeus

1,039
11
18

1

Would you care to make an example of this, with long polling, using the database, and the actual update of the data itself? :) – Coderax Mar 16 '17 at 05:54
1

@Coderax https://github.com/panique/php-long-polling This is a full example with client and server side code. – Zeus Mar 16 '17 at 06:05
1

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. – Coderax Mar 16 '17 at 06:16
1

@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. – Zeus Mar 16 '17 at 06:24
1

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 – Coderax Mar 16 '17 at 06:36

score 2 · Answer 2 · answered Mar 16 '17 at 05:43

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

score 1 · Answer 3 · answered Mar 16 '17 at 06:28

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.

Update HTML when change is noticed in MySQL Database

3 Answers3