Solved

Using AJAX to Rerun Single Database Query on PHP Page

Posted on 2010-09-24
20
189 Views
Last Modified: 2014-01-21
We have a PHP page with lots of queries to a mySQL database.

The web page delivers a webcast stream which can be any one of 2 stream types ...

- test mode stream
- live stream

From the back end admin area, we can toggle from  test (status value=1) to live (status value=2).

When a user logs in to view the webcast, the page queries the database to check the status of the webcast at the time the user logs in and ...

- if in TEST mode it will return 'value=1' which then displays the test webcast stream
- if in LIVE mode it will return 'value=2' which then displays the live webcast stream

When it is time to go live, the administrator toggles to live mode, changing the webcast status from 'test' (value=1) to 'live' (value=2).

What we need is for the page to regularly query the database to identify when that change is made.

But we don't want the whole page to refresh because that will then restart the test webcast.

Basically we want to know when the result changes from '1' to '2' so that we can change the webcast to show the live feed.

Of course, once it shows the live stream (value=2) then we can stop running the queries.

The code we are using is ...

// get webcast details
$sql_webcast = "SELECT * FROM webcasts WHERE id='$wid' AND code='$code'";
$result_webcast = mysql_query($sql_webcast);
$row_webcast = mysql_fetch_array($result_webcast);

if($row_webcast[feed_type]=='1') {
$stream = TEST STREAM;
}
if($row_webcast[feed_type]=='2') {
$stream = LIVE STREAM;
} 

Open in new window

0
Comment
Question by:poldings
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 9
20 Comments
 
LVL 16

Expert Comment

by:carsRST
ID: 33755480
I'm assume you're asking how to do AJAX and PHP.

See link below as basic example.
http://www.w3schools.com/php/php_ajax_database.asp

Nothing really to it.  Just have an onclick/onchange event call javascript which calls your php page and returns the results.

0
 

Author Comment

by:poldings
ID: 33755517
Thanks but I've spent hours doing the research already which is why I've posted here.

The above code is part of the page and is not a separate file.

I would assume it would be easy but I just can't see it.

Can you provide the code so I can drop it into the page?
0
 
LVL 16

Expert Comment

by:carsRST
ID: 33755632
Not able to test, but something like this.  Edit as necessary.

Two files...

1.  one HTML file
2.  Separate PHP file.

Not sure where the variables $wid' AND code='$code'" come from?
<html>
<head>

<script type="text/javascript">
function checkDB()
{

if (window.XMLHttpRequest)
  {// code for IE7+, Firefox, Chrome, Opera, Safari
  xmlhttp=new XMLHttpRequest();
  }
else
  {// code for IE6, IE5
  xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
  }
xmlhttp.onreadystatechange=function()
  {
  if (xmlhttp.readyState==4 && xmlhttp.status==200)
    {
    document.getElementById("returnValue").innerHTML=xmlhttp.responseText;
    }
  }
xmlhttp.open("GET","poldingsCode.php",true);
xmlhttp.send();
}
</script>

</head>
<body>

<input type=button onclick="checkDB();" value="Check Database"><BR>

<input type="text" ID="returnValue"><BR>
<br />


</body>
</html>




--------------
PHP FILE NOW
--------------


<?


//poldingsCode.php

// get webcast details
$sql_webcast = "SELECT * FROM webcasts WHERE id='$wid' AND code='$code'";
$result_webcast = mysql_query($sql_webcast);
$row_webcast = mysql_fetch_array($result_webcast);

if($row_webcast[feed_type]=='1') {
$stream = TEST STREAM;
}
if($row_webcast[feed_type]=='2') {
$stream = LIVE STREAM;
} 

echo($stream);


?>

Open in new window

0
Secure Your WordPress Site: 5 Essential Approaches

WordPress is the web's most popular CMS, but its dominance also makes it a target for attackers. Our eBook will show you how to:

Prevent costly exploits of core and plugin vulnerabilities
Repel automated attacks
Lock down your dashboard, secure your code, and protect your users

 

Author Comment

by:poldings
ID: 33755656
Okay. That's nearly there however, we don't want it triggered by a button, we want it automated to check every 30 seconds.
0
 
LVL 16

Expert Comment

by:carsRST
ID: 33755728
I had to make a change to the HTML but see below.  Should kick off every 30 seconds.
<html>
<head>

<script type="text/javascript">
function checkDB()
{

if (window.XMLHttpRequest)
  {// code for IE7+, Firefox, Chrome, Opera, Safari
  xmlhttp=new XMLHttpRequest();
  }
else
  {// code for IE6, IE5
  xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
  }
xmlhttp.onreadystatechange=function()
  {
  if (xmlhttp.readyState==4 && xmlhttp.status==200)
    {
    document.getElementById("returnValue").value=xmlhttp.responseText;
    }
  }
var url="updatestatus.php";
url=url+"?q="+str;
url=url+"&pid="+pid;
url=url+"&tid="+tid;
xmlhttp.open("GET","poldingsCode.php?ID=1"+str,true);
xmlhttp.send();
}
</script>

</head>
<body onLoad="window.setInterval('checkDB();',30000);">

<input type=button onclick="checkDB();" value="Check Database"><BR>

<input type="text" ID="returnValue"><BR>
<br />


</body>
</html>

Open in new window

0
 
LVL 16

Expert Comment

by:carsRST
ID: 33755731
Sorry - take out the button.
0
 
LVL 16

Accepted Solution

by:
carsRST earned 250 total points
ID: 33755740
Man, I'm not on top of my game today.    :)

See adjusted code.


<html>
<head>

<script type="text/javascript">
function checkDB()
{

if (window.XMLHttpRequest)
  {// code for IE7+, Firefox, Chrome, Opera, Safari
  xmlhttp=new XMLHttpRequest();
  }
else
  {// code for IE6, IE5
  xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
  }
xmlhttp.onreadystatechange=function()
  {
  if (xmlhttp.readyState==4 && xmlhttp.status==200)
    {
    document.getElementById("returnValue").value=xmlhttp.responseText;
    }
  }

xmlhttp.open("GET","poldingsCode.php"+str,true);
xmlhttp.send();
}
</script>

</head>
<body onLoad="window.setInterval('checkDB();',30000);">


<input type="text" ID="returnValue"><BR>
<br />


</body>
</html>

Open in new window

0
 
LVL 16

Expert Comment

by:carsRST
ID: 33755773
This is the code that will call the ajax javascript function multiple times:

<body onLoad="window.setInterval('checkDB();',30000);">
0
 

Author Comment

by:poldings
ID: 33755833
Thanks. That looks pretty good. I will run some final tests tomorrow and update this post when I can confirm the results. Thanks again.
0
 
LVL 16

Expert Comment

by:carsRST
ID: 33755885
Good luck!
0
 

Assisted Solution

by:poldings
poldings earned 0 total points
ID: 33760783
No joy with that I'm afraid. Even if I use the button to force the update it doesn't work.

I have however, had some success with this ...

<!--HEAD-->
<script language="javascript">
var XMLHttpRequestObject = false;

if(window.XMLHttpRequest) {
XMLHttpRequestObject = new XMLHttpRequest();
} else if (window.ActiveXObject) {
XMLHttpRequestObject = new ActiveXObject("Microsoft.XMLHTTP");
}

function getData(dataSource, divID)
{
if(XMLHttpRequestObject) {
var obj = document.getElementById(divID);
XMLHttpRequestObject.open("GET", dataSource);

XMLHttpRequestObject.onreadystatechange = function()
{
if(XMLHttpRequestObject.readyState == 4 && XMLHttpRequestObject.status == 200) {
obj.innerHTML = XMLHttpRequestObject.responseText;
}
}
XMLHttpRequestObject.send(null);
}
}
</script>


<!--BODY-->
<form>
<input type="button" value="get result" onclick="getData('script-checkstatus.php', 'targetDiv')" />
</form>




Using this code I can push the button and pull in the result from a dbase query in a separate file.

So, the next thing is ... how can I make this an automated update?

I have tried the following but it doesn't work ...


0
 

Author Comment

by:poldings
ID: 33760795
Sorry, that last comment should be ...

I have tried the following but it doesn't work ...
<body onLoad="window.setInterval('getData();',3000);" >
0
 

Assisted Solution

by:poldings
poldings earned 0 total points
ID: 33760807
I think we may be getting somewhere. The following seems to work ...

<body onLoad="window.setInterval('getData(\'script-checkstatus.php\', \'targetDiv\');',3000);" >

I am running more tests and will update this soon.
0
 
LVL 16

Expert Comment

by:carsRST
ID: 33760827
Based on your changes, that should work.
0
 

Author Comment

by:poldings
ID: 33771836
I'm still struggling on the final bit ...

I can get the result to show using a DIV tag as follows ...

<div id="targetDiv"></div>

But I need to convert this to a PHP variable so that I can use it inside the JavaScript code that calls the video.

Do you know how I can convert that to PHP ?
0
 

Author Comment

by:poldings
ID: 33777637
Just to clarify, I need the result to be something that I can insert into the middle of some Javascript code.

Maybe AJAX is not the best option?
0
 
LVL 16

Expert Comment

by:carsRST
ID: 33778484
Sorry.  Been out of town.  Will try to get back to u today.
0
 

Author Comment

by:poldings
ID: 33778547
Not a problem.

I think we have an alternative solution using an iFrame and a PHP "while loop". I need to continue tests but initial results are good.

If you do come up with an AJAX solution all well and good but I think we will run with the PHP solution for this project.
0

Featured Post

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

jQuery is a JavaScript library that greatly simplifies JavaScript programming. AJAX is an acronym formed from "Asynchronous JavaScript and XML."  AJAX refers to any communication between client and server, when the human client does not observe a…
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question