Note:
- You want to fetch data from your database, and it will dynamically fetch new data within 30 seconds, right? So, do you want the new data at the top or at the bottom?
- In order to achieve your goals, we will be using jQuery (a Javascript library) and Ajax
- I'll be using
mysqli_* prepared statement so you don't need to worry about SQL injections
- I'll be guiding you through the code by leaving explanations quoted in
/**/ and <!-- -->
Lets first establish a connection and store it in a separate file (say connection.php):
<?php
/* ESTABLISH YOUR CONNECTION */
$con = new mysqli("localhost", "username", "password", "dbname");
/* CHECK CONNECTION */
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
?>
We can now fetch all the existing data and store them in a table (your main file - index.php?):
<table id="table">
<tr>
<th>id</th>
<th>player</th>
<th>brand</th>
<th>year</th>
</tr>
<?php
include("connection.php"); /* INCLUDE YOUR CONNECTION TO YOUR DATABASE */
if($stmt = $con->prepare("SELECT id, player, brand, year FROM bbcardindo")){ /* CHECK IF THE PREPARED STATEMENT IS TRUE */
$stmt->execute(); /* EXECUTE THE QUERY */
$stmt->bind_result($id,$player,$brand,$year); /* BIND THE RESULT TO THESE VARIABLES */
while($stmt->fetch()){ /* START LOOP THAT WILL FETCH ALL ROWS */
?>
<tr>
<td><?php echo $id; ?></td>
<td><?php echo $player; ?></td>
<td><?php echo $brand; ?></td>
<td><?php echo $year; ?></td>
</tr>
<?php
} /* END OF WHILE LOOP */
$stmt->close(); /* CLOSE THE STATEMENT */
} /* END OF PREPARED STATEMENT */
?>
</table> <!-- END OF TABLE -->
<input type="hidden" id="lastid" value="<?php echo $id; ?>">
<!-- WE WILL BE STORING THE LAST/LATEST ID IN A HIDDEN INPUT FIELD THAT WE WILL BE USING FOR OUR SCRIPT LATER -->
Download the jQuery library here and put it inside your project folder. Then let us create the script that you will be needing to fetch new data every 30 seconds (you can put this at the top of our first code; this is still part of your main file - index.php):
<script src="jquery-1.11.3.js"></script> <!-- CHANGE THE NECESSARY JS FILE DEPENDING ON THE VERSION YOU HAVE DOWNLOADED -->
<script type="text/javascript">
$(document).ready(function(){ /* START THE JQUERY */
setInterval(function() { /* START THE setInterval FUNCTION */
var lastid = $("#lastid").val(); /* GET THE VALUE OF THE HIDDEN INPUT FIELD */
var dataString = 'lastid=' + lastid; /* STORE IT AS A DATA STRING */
$.ajax({ /* START THE AJAX */
type: "POST", /* METHOD WE WILL BE USING TO PASS THE FORM */
url: "getnewdata.php", /* FILE WHERE WE WILL PASS THE DATA */
dataType: 'json', /* USE JSON AS THE DATATYPE FOR READING DATA BEING RETURNED FROM getnewdata.php */
data: dataString, /* THE DATA THAT WE WILL BE PASSING IN getnewdata.php */
success: function(result){
if(result.newid != ""){ /* IF THIS PASSES A NEW DATA */
$('#table tr:last').after(result.newtr); /* APPEND TO YOUR TABLE THE NEW DATA */
$("#lastid").val(result.newid); /* UPDATE THE HIDDEN INPUT FIELD WITH THE LATEST INSERTED ID */
} /* END OF CHECKING RETURNED DATA */
} /* END OF SUCCESS */
}); /* END OF AJAX */
return false;
}, 30000); /* SET TO 30 SECONDS */
}); /* END OF JQUERY */
</script>
Then create the file where we will get new data (getnewdata.php):
<?php
include("connection.php");
if(!empty($_POST["lastid"])){ /* IF THERE IS A PASSED DATA */
$newtr = '';
$newestid = '';
if($stmt = $con->prepare("SELECT id,player,brand,year FROM bbcardindo WHERE id > ?")){ /* GET ROWS GREATER THAN THE PASSED ON ID; CHECK IF THE PREPARED STATEMENT IS TRUE */
$stmt->bind_param("i",$_POST["lastid"]); /* BIND THE PASSED ON DATA TO THE QUERY; i STANDS FOR INTEGER, MEANING THAT WE WILL BIND A NUMERIC VALUE TO THE QUERY */
$stmt->execute(); /* EXECUTE THE QUERY */
$stmt->bind_result($id,$player,$brand,$year); /* STORE THE DATA TO THESE VARIABLES */
while($stmt->fetch()){ /* START FETCHING NEW ROWS */
$newtr = '
<tr>
<td>'.$id.'</td>
<td>'.$player.'</td>
<td>'.$brand.'</td>
<td>'.$year.'</td>
</tr>
';
$newestid = $id; /* STORE THE LAST ID */
} /* END OF WHILE LOOP OF FETCHING NEW DATA */
} /* END OF PREPARED STATEMENT */
echo json_encode(array("newtr" => $newtr, "newid" => $newestid)); /* PRINT THE RESULT IN JSON */
} /* END OF IF NOT EMPTY lastid */
?>
Review:
When you load your main file (index.php), all the existing data from your bbcardindo table will be listed in a table. The latest id will be stored in a hidden input field. Every 30 seconds, the program will check for new data based from the hidden input field. New data will be added as new row(s) automatically at the bottom of your table.
