Arnold.M Arnold.M - 5 months ago 28
HTML Question

Update a query value using javascript/php

I know that this might be answered already by someone, but all the answers I could find, are intended for people who already know what they are doing and they paste thousands of lines of code, so I want something more on a begginer's side.

I have a value that I want to update ever 15 seconds to show the most updated information from a mysql db. I managed to show the latest result every once you reload the webpage.

The code is the following (everything is on the same file index.php):

PHP part:

<?php
$link = mysql_connect('localhost', 'root', '');
$LastUpdate = mysql_query('SELECT dateTime FROM LastUpdate where id=1');
if (!$LastUpdate) {
die('Could not query:' . mysql_error());
}


HTML part:

<small id="result">Last Update: <?php echo mysql_result($LastUpdate, 0); ?></small>


Jscript part:

<script>
setInterval(function() {
var date='Last update: '+'<?php echo mysql_result($LastUpdate, 0); ?>';
document.getElementById("result").innerHTML = date;
}, 1000);




The script part is supposed to refresh the "result" div every 1000 milliseconds. The problem is that once the script is performed one time the value gotten from the query doesn't change, having to reload the page in order for it to refresh.

Answer

First off all. Client asks Server for Webpage -> The Server build your Page -> The Browser get the response and render your HTML page. So far so good. Your <?php echo mysql_result($LastUpdate, 0); ?> always has the same value. You have to make an AJAX call and please don't use mysql functions anymore. There are deprecated (https://secure.php.net/manual/de/function.mysql-connect.php). Use PDO instead and always escape your statements to prevent sql injections or use an ORM. Here is a quick and dirty solution:

index.html

<!DOCTYPE html>
<html lang="en">

<head>
    <meta charset="UTF-8">
    <title>test</title>
    <script src="https://code.jquery.com/jquery-3.0.0.min.js" integrity="sha256-JmvOoLtYsmqlsWxa7mDSLMwa6dZ9rrIdtrrVYRnDRH0=" crossorigin="anonymous"></script>
    <script>
        function ajaxCall() {
            $.ajax({
                url: "ajax.php",
            }).done(function (data) {
                $(this).addClass("done");
                var date = 'Last update: ' + data;
                document.getElementById("result").innerHTML = date;
            });
        }

        $(function () {
            ajaxCall();
            setInterval(ajaxCall, 1000);
        });
    </script>
</head>

<body>
    <small id="result"></small>
</body>

</html>

ajax.php

<?php
$link = mysql_connect('localhost', 'root', '');
mysql_select_db('database', $link); // select a database

$LastUpdate = mysql_query('SELECT dateTime FROM LastUpdate where id=1');
$row = mysql_fetch_assoc($LastUpdate); // gives you the data in an array

if (!$LastUpdate) {
    die('Could not query:' . mysql_error());
}

echo $row['dateTime'];