persistant updates from mysql w/ php

[prolemur]

I want to have a number on a webpage that is updated every time the value in the database changes without having to re-load the webpage. How would I do this with php and mysql. I have read a bit about persistent connections to databases but most people say only to use those when a database is not hosted on the same machine the site is. I would prefer to try to do this in php even though I know there are probably much better ways like node.js which is hot right now.

 

ps: Any tutorials you can link me would be greatly appreciated too

[Hazy125]

Does it have to be instantaneous like strawpoll, or would AJAX polling be fine?

[prolemur]

Does it have to be instantaneous like strawpoll, or would AJAX polling be fine?

How often does ajax poll? Strawpoll like updating would be amazing especially compared to just having the page refresh every 5 seconds like it does now

[Hazy125]

How often does ajax poll? Strawpoll like updating would be amazing especially compared to just having the page refresh every 5 seconds like it does now

How ever long you want it to

setInterval{($.ajax({	 url: "URL",	 type: "POST",	 data: dataString,	 dataType: "html",	 success: function(data){		 //updatePage		 }, 5000)}

Something like this for example will send the request every 5 seconds and update the page with the data returned from the URL. Of course, 5 seconds isn't real time though 

[prolemur]

How ever long you want it to

setInterval{($.ajax({	 url: "URL",	 type: "POST",	 data: dataString,	 dataType: "html",	 success: function(data){		 //updatePage		 }, 5000)}

Something like this for example will send the request every 5 seconds and update the page with the data returned from the URL. Of course, 5 seconds isn't real time though 

Thanks Hazy I'll try to implement this and update you later. Funny thing you mentioned strawpoll because that's what I'm trying to make right now as a little side project. 

[Hazy125]

Thanks Hazy I'll try to implement this and update you later. Funny thing you mentioned strawpoll because that's what I'm trying to make right now as a little side project. 

Ah, well you might want to look into web sockets and HTML5 <canvas> objects for that. socket.io is a popular choice to make sockets easier

 

EDIT: Oh, and by the way, my syntax was way off on the example before. Save you the headache of figuring out why it won't work now 

setInterval(function(){ 	 $.ajax({	 url: "URL",	 type: "POST",	 data: dataString,	 dataType: "html",	 success: function(data){		 //updatePage		 }	 }) }, 3000);

Also, not sure if you're familiar with Jquery, but that ajax request uses Jquery. An example without could be 

setInterval(function(){ var xmlHttp = createXmlHttpRequestObject();function createXmlHttpRequestObject(){	var xmlHttp;		if(window.ActiveXObject){		try{			xmlHttp = new ActiveXObject("Microsoft.XMLHTTP");			}catch(e){				xmlHttp = false;				}		}else{			try{			xmlHttp = new XMLHttpRequest();			}catch(e){				xmlHttp = false;				}			}				if(!xmlHttp){		alert("Can't create object");		}else{			return xmlHttp;			}	}		function process(){		if(xmlHttp.readyState==4 || xmlHttp.readyState==0){			xmlHttp.open("POST", URL, true);			xml.onreadystatechange = handleServerResponse;			xmlHttp.send(null);			}else{				setTimeout('process()',1000);				}				}		function handleServerResponse(){		if(xmlHttp.readyState==4){			if(xmlHttp.status==200){				xmlResponse = xmlHttp.responseXML;				xmlDocumentElement = xmlResponse.documentElement;				message = xmlDocumentElement.firstChild.data;				//Update page				setTimeout('process()',1000);				}else{					alert('Something went wrong');					}			}		}}, 3000);

So yeah, Jquery is easier and the non-Jquery code above will need a bit of optimisation, but saves you having to load in the whole Jquery library

[Neil]

You're looking for something using WebSockets, which /can/ be done in PHP... but it's sadly terrible compared to other languages.

[prolemur]

You're looking for something using WebSockets, which /can/ be done in PHP... but it's sadly terrible compared to other languages.

Exactly!

[madknight3]

Take a look at Building Realtime Web Apps with PHP. The page talks about some existing options available if you don't want to implement everything yourself. You can take a look and see if anything will suit your needs.

[Lumi]

How often does ajax poll? Strawpoll like updating would be amazing especially compared to just having the page refresh every 5 seconds like it does now

I recommend you look at using a websocket.

[prolemur]

Take a look at Building Realtime Web Apps with PHP. The page talks about some existing options available if you don't want to implement everything yourself. You can take a look and see if anything will suit your needs.

Ya I'd like to implement it myself without a library if possible to decrease the amount of code and extra stuffs I don't need.

Ah, well you might want to look into web sockets and HTML5 <canvas> objects for that. socket.io is a popular choice to make sockets easier

 

EDIT: Oh, and by the way, my syntax was way off on the example before. Save you the headache of figuring out why it won't work now 

setInterval(function(){ 	 $.ajax({	 url: "URL",	 type: "POST",	 data: dataString,	 dataType: "html",	 success: function(data){		 //updatePage		 }	 }) }, 3000);

Also, not sure if you're familiar with Jquery, but that ajax request uses Jquery. An example without could be 

setInterval(function(){ var xmlHttp = createXmlHttpRequestObject();function createXmlHttpRequestObject(){	var xmlHttp;		if(window.ActiveXObject){		try{			xmlHttp = new ActiveXObject("Microsoft.XMLHTTP");			}catch(e){				xmlHttp = false;				}		}else{			try{			xmlHttp = new XMLHttpRequest();			}catch(e){				xmlHttp = false;				}			}				if(!xmlHttp){		alert("Can't create object");		}else{			return xmlHttp;			}	}		function process(){		if(xmlHttp.readyState==4 || xmlHttp.readyState==0){			xmlHttp.open("POST", URL, true);			xml.onreadystatechange = handleServerResponse;			xmlHttp.send(null);			}else{				setTimeout('process()',1000);				}				}		function handleServerResponse(){		if(xmlHttp.readyState==4){			if(xmlHttp.status==200){				xmlResponse = xmlHttp.responseXML;				xmlDocumentElement = xmlResponse.documentElement;				message = xmlDocumentElement.firstChild.data;				//Update page				setTimeout('process()',1000);				}else{					alert('Something went wrong');					}			}		}}, 3000);

So yeah, Jquery is easier and the non-Jquery code above will need a bit of optimisation, but saves you having to load in the whole Jquery library

I am back from the web-dev grave. I've been putting this off and school gets in the way

So referring to the just javascript (not jquery code) what would I have to do to actually update the page. Like I've added that code block to my results page inside a script tag but how would I fetch the data from the database. Can javascript interact directly with MySQL or is there a hand-off between php and js somehow? Thanks

Edited October 10, 2015 by prolemur
given 1 warning point for thread necro :p

[Hazy125]

Ya I'd like to implement it myself without a library if possible to decrease the amount of code and extra stuffs I don't need.

I am back from the web-dev grave. I've been putting this off and school gets in the way

So referring to the just javascript (not jquery code) what would I have to do to actually update the page. Like I've added that code block to my results page inside a script tag but how would I fetch the data from the database. Can javascript interact directly with MySQL or is there a hand-off between php and js somehow? Thanks

Long time no see, love the necro warning haha.

 

Ok, your first question, that particular piece of code returns the server response to a variable called message. So it's just a simple document.getElementById('div where you want the page to update').innerHTML = message;

 

Javascript can't interact directly without using something like node.js which is basically like a javascript version of something like PHP... Sort of.. So, currently you need to have a PHP respond and process the request. On the line that says xmlHTTP.open(params) is what you will want to change for that. You're probably fine with just a GET request, so you can probably just go ahead and change that. The URL is the PHP file that processes and returns data. So you could set the URL to something like getUpdatedData.php or something.

 

Since JS doesn't directly interact with mySQL, you would need to do something similar to this

<?php //this is the file that gets the GET request sent to itif(empty($_GET)){    header("Location: " . $_SERVER['HTTPReferrer']);//simple code that redirects people back if the go to the page unwanted}$servername = "localhost";$username = "username";$password = "password";$dbname = "DB";$conn = new mysqli($servername, $username, $password, $dbname);if ($conn->connect_error) {    die("Connection failed: " . $conn->connect_error);}$stmt = $conn->prepare("SELECT number FROM pageCounter WHERE id = ? ORDER BY id DESC LIMIT 1");$stmt->bind_param("i", $databaseID);$databaseID = Whatever you use as a primary key, it's currently set to work as an integer;$stmt->execute();$stmt->bind_result($returnedNumber);$stmt->fetch();$stmt->close();$conn->close();// Just get the thing you want from the db... You don't really have to use prepared statementsecho '<response>$returnedNumber</response>';//The AJAX call expects an XML return, hence why the XML. You can change this as well, so you can just return the plain text. The JS code from before should now get the contents of this XML tag and print it to the page... ?>

Sorry if all this was a little long winded, I had a really big night last night and the hangover is real

[prolemur]

Long time no see, love the necro warning haha.

Sorry if all this was a little long winded, I had a really big night last night and the hangover is real

Thanks, no not at all

I guess I'll have to make a div out of anything I want to get updated then, right or maybe store it in a js or php variable. I'll try to figure this out!

I'ma get on this tonight and be back ether tomorrow/tonight or in another 2 months, happy thanksgiving(if you're canadian)

[prolemur]

@Hazy125 Hey I tried adding that code to my project but it isn't doing anything I can see.

 

Could I add you on skype or something and share the project files with you and you take a look please?

[Hazy125]

@Hazy125 Hey I tried adding that code to my project but it isn't doing anything I can see.

Could I add you on skype or something and share the project files with you and you take a look please?

Of course, I'm going to be working for another few hours, but happy to have a look. Just grab my Skype off of blade/ixi/luke/wind.... Whoever really