How can I refresh the results of a MySQL query without refreshing the page?

Question

I am wanting to refresh the results of a query I have periodically, say every 10-30 seconds. I am unsure how to implement code that I have read online into my own code as a lot of the time the scenario is different.

The MySQL query is on the same page as my jQuery and everything else.

Currently, I retrieve values of a MySQL table using this bit of PHP code:

$result = mysqli_query($con,"SELECT * From auction WHERE category = 'Bathroom' ORDER BY ID DESC");


while($row = mysqli_fetch_array($result))
  {
    echo "<form name='auction' id='auction" . $row['ID'] . "'>
            <input type='hidden' name='id' value='" . $row['ID'] . "' />
            <div class='auction-thumb'>
                <div class='auction-name'>" . $row['Item'] . "</div>";
            echo "<img class='auction' src='" . $row['ImagePath'] . "' />";
            echo "<div class='auction-bid'>Current Bid: £<div class='nospace' id='" . $row['ID'] . "'>" . $row['CurrentBid'] . "</div></div>";
            echo "<div class='auction-bid'>Your Name: <input type='text' class='bidder' name='bidname' autocomplete='off'/></div>";
            echo "<div class='auction-bid'>Your Bid: <input type='text' class='auction-text' name='bid' autocomplete='off'/></div>";
            echo "<div class='auction-bid'><input type='submit' name='submit' value='Place Bid!' /></div>";
    echo "</div></form>";
  }
echo "</table>";

mysqli_close($con);

?>

This retrieves multiple rows and they populate the page.

I would like to refresh or 'rerun' this query every so often, so that the Current Bid is updated.

I have this jQuery code already which posts the new bid to a PHP page, currently, anyone else looking at this page will not see the new bid until they refresh the page manually which is not great.

    <script>
    $(document).ready(function(){
        $('form[name="auction"]').submit(function(){
            var id = $(this).find('input[name="id"]').val();
            var bidname = $(this).find('input[name="bidname"]').val();
            var bid = $(this).find('input[name="bid"]').val();
            var currentbid = $('#'+id).text();
            var itemdesc = $(this).find('.auction-name').text();

            if (bidname == '')
            {
                alert("No name!")
                return false;   
            }

            if (bid > currentbid)
            {
                alert("Bid is greater than current bid");   
            }
            else
            {
                alert("Bid is too low!");
                return false;   
            }

            $.ajax({
            type: "POST",
            url: "auction-handler.php",
            data: {bidname: bidname, bid: bid, id: id, itemdesc: itemdesc},
            success: function(data){
                window.location.reload();
            }
        });
        return false;

        }); 
    });
</script>

My auction-handler.php code:

<?php
ini_set('display_errors', 'On');
error_reporting(E_ALL | E_STRICT);

$con=mysqli_connect("xxxx","xxxx","xxxx","xxxx");

if (mysqli_connect_errno())
    {
            echo "Failed to connect to MySQL: " . mysqli_connect_error();
    }

$bidname = $_POST['bidname'];
$bid = $_POST['bid'];
$id = $_POST['id'];
$itemdesc = $_POST['itemdesc'];

$query = "UPDATE auction SET CurrentBid = '$bid', Bidder = '$bidname' WHERE ID = '$id'";

$query2 = "INSERT INTO auction_log (Item, Bid, Bidder) VALUES ('$itemdesc','$bid','$bidname')";



mysqli_query($con, $query) or die(mysqli_error());

mysqli_query($con, $query2) or die(mysqli_error());

mysqli_close($con);
?>

A lot of things I have read online do not really help me and I am too much of a begginer to be able to write it myself properly. Somethings I am really struggling to understand how to do, and how I can implement it in my scenario.

Any advice is greatly appreciated.

Answer 1

<div id="results">
	...
</div>

<script>
var time = 30000;
function autoRefresh()
{
	$.ajax({
		type: "POST",
		dataType: "HMTL",
		url: "auction-handler.php",
		data: {bidname: bidname, bid: bid, id: id, itemdesc: itemdesc},
		success: function(data){
			$("#results").append(data);
		}
	});
}

setTimeout("autoRefresh()", time);
</script>

Answer 2

you're not far off.

What you need to do is replace the html of the relevant table with the response of the ajax request rather than reloading the entire page.

So instead of:

success: function(data){
            window.location.reload();
        }

You can do:

success: function(data){
            $('#auction'+id).before(data).remove();
        }

Edit

Periodic ajax request:

php:

//update-handler.php
$response = array();

$result = mysqli_query($con,"SELECT * From auction WHERE category = 'Bathroom' ORDER BY ID DESC");


 while($row = mysqli_fetch_array($result)){
  $response[] = (object)array('id'=>$row['ID'],'val'=>$row['CurrentBid']);
 // get all the updated rows and put them into response array;
 }

header('content-type=application/json');
exit(json_encode($response)); // send the json serialized response to jquery ajax

javascript:

var timeout = 30000 //30 seconds
setInterval(function () {

    $.ajax({
    type: "POST",
    url: "update-handler.php",
    success: function (data) {
        $(data).each(function (i, d) {  //loop though each bid amount from ajax response
            $('#auction' + d.id).find('.nospace').html(d.val); //nospace is not a good target name, but its the only unique selector for that div

        });
    }
    });
}, timeout);