0

I've been looking for some script that would refresh my page after it notices a new entry in a specific table of a database. So basically... The script would check in the table every five or so seconds and if it notices anything with the time stamp greater than time() (time of when the page was loaded, obviously, not current time) with an id column value of eg. 14, it would refresh the page.

I looked on line a lot trying to solve this, but I don't seem to find the right answers. You see, the problem is JavaScript and AJAX aren't really that familiar to me.

Sorry if it seems like a stupid question, it really means the world to me right now. Other answers are welcome as well... Like how could I do it using Comet or long Sockets? Because I'm 100% clueless.

Here is my current (reduced) code of that page:

if (isset($_GET['view'])) { $duel_id = mysqli_real_escape_string($query, $_GET['view']); $result = mysqli_query($query, "SELECT * FROM duels WHERE `id` = '$duel_id' LIMIT 0, 1") or die(mysqli_error($query)); $row = mysqli_fetch_array($result); if ($row['member_1'] !== $member_id && $row['member_2'] !== $member_id) { echo '<meta http-equiv="refresh" content="0; url=redirect.php" />'; exit; } else { //IRRELEVANT FUNCTIONS if ($my_turn == false && $row['winner'] == 0) { //IRRELEVANT FUNCTIONS } else { echo '<center><form action="spell.php" method="post"><table style="margin: 20px;">'; //300 LINES OF DYNAMIC FORMS AND INPUTS echo '</form></center>'; } } echo '<center><table style="margin: 20px; margin-top: 40px;">'; $result = mysqli_query($query, "SELECT * FROM duelling WHERE `duel_id` = '$duel_id' ORDER BY `timestamp` DESC") or die(mysqli_error($query)); //THIS IS THE WHILE LOOP WHICH DISPLAYS ALL LOGS ON REFRESH while ($row = mysqli_fetch_array($result)) { //$spell, $name, $defence ARE DEFINED HERE, ANOTHER 20 LINES echo '<tr><td style="padding: 5px; color: #a20d0d; font-family: \'Courier New\', Courier, monospace; text-align: right;">' . date('d.m.y H:i', $row['timestamp']) . '</td><td style="padding: 5px;">' . $name . ' used ' . $spell . '. <span style="font-family: \'Courier New\', Courier, monospace; color: #a20d0d; font-size: 10px;">' . $defence . '</span></td></tr>'; } echo '</table></center>'; }
Improve this question
1
  • You need to learn Javascript and AJAX for this. Commented Feb 11, 2014 at 14:43

3 Answers 3

Reset to default
2

Use setInterval and jQuery's $.get

setInterval(function(){ $.get( "page.php", function( data ) { //Write data to div $( "#result" ).html( data ); }); },5000);

Of course, this will involve you to edit your current script(s) to allow page.php to fetch results, and your page that is displayed to the client to have <div id="result"></div>

Your page.php will need to only display the data in a table (as that is your preferred output suggested by your original post) and nothing else, as it will be called every 5000 millisecond; 5 seconds.

Almost forgot. Make sure you include the jQuery lib

<script type="text/javascript" src="//ajax.googleapis.com/ajax/libs/jquery/1.10.2/jquery.min.js"></script>

In page.php, add a "slave" to deliver you the content in your desired format.

<?php //We've connected to the database using mysqli echo '<center><table style="margin: 20px; margin-top: 40px;">'; $result = mysqli_query($query, "SELECT * FROM duelling WHERE `duel_id` = '$duel_id' ORDER BY `timestamp` DESC"); while ($row = mysqli_fetch_array($result)) { echo '<tr><td style="padding: 5px; color: #a20d0d; font-family: \'Courier New\', Courier, monospace; text-align: right;">' . date('d.m.y H:i', $row['timestamp']) . '</td><td style="padding: 5px;">' . $name . ' used ' . $spell . '. <span style="font-family: \'Courier New\', Courier, monospace; color: #a20d0d; font-size: 10px;">' . $defence . '</span></td></tr>'; } echo '</table></center>'; ?>

Also, just a tip: It may be useful to you to not use inline styling, as it will be a nuisance for you to update styling later on, if it's not all in one (or two) css files.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

How would I allow page.php to fetch results, and what would page.php actually have in it?
Basically the same as what you've got above. Connect to the database, fetch results, render results in a table.
Okay, I think I got this... Would the data have to be in a return function instead of echo?
Use echo, as you want the data to render just as if someone went through it on their browser. I've updated my answer.
2

so you guessed right. you need ajax, if you don't mind the server load caused by a request every 5 seconds. to achieve that you need some js code like this:

var startTime = Math.round(new Date().getTime() / 1000); function checkTables(startTime) { $.get( "checkdb.php?startTime=" + startTime, function(res) { if(res == "1") location.reload(); }); } setInterval("checkTables(" + startTime + ")",5000);

you checkdb.php would probably look like: 

$startTime = $_GET['startTime']; //do appropriate sanitizing/escaping $result = mysqli_query($query, "SELECT * FROM duelling WHERE `duel_id` = '$duel_id' AND `timestamp` > " . $startTime) or die(mysqli_error($query)); if(mysqli_num_rows($result) > 0) echo "1";
Improve this answer

4 Comments

Would I have to put anything in checkdb.php? This seems to be the code I'm looking for, but I really don't get what to do with checkdb.php...
you would need to add you database connection parameters here. also i didn't know if $duel_id is needed in the query. just change the query to get the results that are newer than the page load, which we sent as $startTime
checkdb.php will just check if there is any new rows in the database which is inserted after $startTime and echo "1" if yes.
This is really useful, I will remember this for future projects, thank you.
0

  1. You could write an ajax request ( with set interval, or websockets ) to check, if the content of your page has changed using file_get_contents.

    file_get_contents("http://your_page.php", false );

  2. You could store the index of the last item and check for new ones also.

I also recommend not refreshing the whole page, just change the updated area using ajax and e.g. jquery's html function, or pure JavaScript

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.