Sorry for my english. I try to modify this script which uses AJAX to look for changes in a database table, and if a change is detected, return information from that table : http://blog.codebusters.pl/en/ajax-auto-refresh-volume-ii/
Instead of using html form to add value to database (from add.php), I try to send value to the db when the page is loaded (goal is to be alerted in real time that a user is on that page).
I think the file which need to be modified is add.php and especially this :
"<?php require('common.php');
if($_POST && !empty($_POST['title'])){
$result = $db->add_news(strip_tags($_POST['title']));
}?>"
I remplaced by :
<?php
$link = mysqli_connect("localhost", "user", "mypass", "db");
if($link === false){
die("ERROR: Could not connect. " . mysqli_connect_error());
}
$sql = "INSERT INTO news (title) VALUES ('This page has been loaded !')";
if(mysqli_query($link, $sql)){
echo "Records inserted successfully.";
} else{
echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);
}
mysqli_close($link);
?>
Problem is auto refresh isn't working. I think this is because this line is missing from add.php :
"$result = $db->add_news(strip_tags($_POST['title']));"
How can I ask for auto refresh after adding value in sql table ? I think I have to use function register_changes from db.php but I don't know how and where.. I'm beginner and lost!
Thank you in advance for your help.
Here are the original files :
add.php :
<?php require('common.php');
if($_POST && !empty($_POST['title'])){
$result = $db->add_news(strip_tags($_POST['title']));
}?>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>Add news - Demo for Ajax Autorefresh</title>
</head>
<body>
<h1>This is a demo for post <a href="http://blog.codebusters.pl/en/entry/ajax-auto-refresh-volume-ii">Ajax Auto Refresh - Volume II</a></h1>
<p>Open <a href="index.php">list of messages</a> in new window, add new message with this form and look at that list. Don't refresh it manually. It should refresh automatically after 20 seconds.<p>
<?php if(isset($result)){
if($result==TRUE){
echo '<p>Success</p>';
}else{
echo '<p>Error</p>';
}
}else{?>
<form method="post" action="#">
<input type="text" name="title" size="50" />
<input type="submit" value="Add message" />
</form>
<?php }?>
</body>
</html>
common.php :
<?php
require_once ('db.php'); //get our database class
$db = new db();
/* end of file common.php */
db.php :
<?php
/**
* Class db for Ajax Auto Refresh - Volume II - demo
*
* @author Eliza Witkowska <kokers@codebusters.pl>
* @link http://blog.codebusters.pl/en/entry/ajax-auto-refresh-volume-ii
*/
class db{
/**
* db
*
* @var $ public $db;
*/
public $db;
/**
* __construct
*
* @return void
*/
function __construct(){
$this->db_connect('localhost','root','alamakota','test');
}
/**
* db_connect
*
* Connect with database
*
* @param mixed $host
* @param mixed $user
* @param mixed $pass
* @param mixed $database
* @return void
*/
function db_connect($host,$user,$pass,$database){
$this->db = new mysqli($host, $user, $pass, $database);
if($this->db->connect_errno > 0){
die('Unable to connect to database [' . $this->db->connect_error . ']');
}
}
/**
* check_changes
*
* Get counter value from database
*
* @return void
*/
function check_changes(){
$result = $this->db->query('SELECT counting FROM news WHERE id=1');
if($result = $result->fetch_object()){
return $result->counting;
}
return 0;
}
/**
* register_changes
*
* Increase value of counter in database. Should be called everytime when
* something change (add,edit or delete)
*
* @return void
*/
function register_changes(){
$this->db->query('UPDATE news SET counting = counting + 1 WHERE id=1');
}
/**
* get_news
*
* Get list of news
*
* @return void
*/
function get_news(){
if($result = $this->db->query('SELECT * FROM news WHERE id<>1 ORDER BY add_date DESC LIMIT 50')){
$return = '';
while($r = $result->fetch_object()){
$return .= '<p>id: '.$r->id.' | '.htmlspecialchars($r->title).'</p>';
$return .= '<hr/>';
}
return $return;
}
}
/**
* add_news
*
* Add new message
*
* @param mixed $title
* @return void
*/
function add_news($title){
$title = $this->db->real_escape_string($title);
if($this->db->query('INSERT into news (title) VALUES ("'.$title.'")')){
$this->register_changes();
return TRUE;
}
return FALSE;
}
}
/* End of file db.php */
index.php :
<?php require('common.php'); ?>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>Demo for Ajax Auto Refresh</title>
<script src="jquery-1.10.2.min.js"></script>
<script>
/* AJAX request to checker */
function check(){
$.ajax({
type: 'POST',
url: 'checker.php',
dataType: 'json',
data: {
counter:$('#message-list').data('counter')
}
}).done(function( response ) {
/* update counter */
$('#message-list').data('counter',response.current);
/* check if with response we got a new update */
if(response.update==true){
$('#message-list').html(response.news);
}
});
}
//Every 20 sec check if there is new update
setInterval(check,20000);
</script>
</head>
<body>
<h1>This is a demo for post <a href="http://blog.codebusters.pl/en/entry/ajax-auto-refresh-volume-ii">Ajax Auto Refresh - Volume II</a></h1>
<?php /* Our message container. data-counter should contain initial value of couner from database */ ?>
<div id="message-list" data-counter="<?php echo (int)$db->check_changes();?>">
<?php echo $db->get_news();?>
</div>
<p><a href="add.php">Add new message</a></p>
</body>
</html>
checker.php :
<?php require('common.php');
//get current counter
$data['current'] = (int)$db->check_changes();
//set initial value of update to false
$data['update'] = false;
//check if it's ajax call with POST containing current (for user) counter;
//and check if that counter is diffrent from the one in database
if(isset($_POST) && !empty($_POST['counter']) && (int)$_POST['counter']!=$data['current']){
//the counters are diffrent so get new message list
$data['news'] = '<h1>OMG! It\'s alive!!! NEW UPDATE !!!</h1>';
$data['news'] .= $db->get_news();
$data['update'] = true;
}
//just echo as JSON
echo json_encode($data);
/* End of file checker.php */
