CodingForums.com - php/mysql and ajax connecting with javascript

CodingForums.com (http://www.codingforums.com/index.php)

- Ajax and Design (http://www.codingforums.com/forumdisplay.php?f=55)

- - php/mysql and ajax connecting with javascript (http://www.codingforums.com/showthread.php?t=215849)

php/mysql and ajax connecting with javascript

hey guys, i am in a bit of a pickle (i also dont know if my title is correct) i was just wondering how i could connect mysql/php to javascript, so that a count down will be a live timer from a specific time, for instance say i do a form submit in php, it creates a date/time stamp in the mysql database, you cannot do another form of the same type for 3 minutes for example, but it will display a specific users time left in a box above the form showing how long they have left until they can submit again, but in a live timer where even if they refresh the page the counter continues from the database value and doesnt start all over again?

any help would be appreciated :)
even a website link to a tutorial would help :) as i was told it runs through ajax?

Well, two ways I can think of. One, which you already mentioned, is using AJAX. The second is not with AJAX. Allow me to explain:

In method two we would simply have a JavaScript count down timer on the page that starts at a specific number. That number is then inserted in there using PHP every time the page is loaded.

For example:

PHP Code:


		
			
<?php
$time = mysql_query( "SELECT........" );
?>
<html>
     <body>
          <script type="text/javascript">
               startCountdownTimer( <?php echo( $time ); ?> );
          </script>
     </body>
</html>

The first method is using AJAX. With this you would want to create a generic PHP page with no styling or HTML that simply outputs nothing but the time you want to start the countdown at. Then, using an AJAX GET request, you can fetch the time from the script and start it in your page.

Method 2 is simpler, Method 1 may be required based on how much AJAX is already used in your implementation.

Please let me know if you need further clarification on the methods. If you decide to go with Method 1 I can expand on how exactly you can implement the AJAX, but Method 2 will probably suit your purposes.

i have a great example here.

Code:

--

-- Table structure for table `backup`

--



CREATE TABLE IF NOT EXISTS `backup` (

  `id` int(11) NOT NULL auto_increment,

  `backup` enum('0','1') NOT NULL default '0',

  `date` datetime NOT NULL default '0000-00-00 00:00:00',

  PRIMARY KEY  (`id`)

) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;

thats my mysql database table forr backup, this will be set to 15 minutes a time whenever i set it, so i would need a way for it to display on the screen of 15 minutes, then obviously to count down from then onwards, but if anyone went to the page after it had already started it would be where it should be within the 15 minutes.

what do you think the easiest way, and most convienient way would be to do this?

I would say you have two fields. One that signals a Database Backup Countdown is active, and one that stores when it was started for countdown.

Then, every time a page is loaded, your PHP page runs a query to see if a Database Backup Countdown is active. If it is, then it will take the current time, subtract out the time the countdown started, and then send that to the browser. The browser can then display a count down using Javascript.

You would implement this almost the same as I had above. First you would obviously need to change your database architecture to include Database Backup Countdown.

The following example assumes that there exists a coulmn called countdownActive which is 0 for no countdown and 1 for an active countdown.

PHP Code:


		
			
<?php

$sql = mysql_query( "SELECT `countdownActive`, `countdownTimer` FROM `myTable` WHERE `id` = 'myID' LIMIT 0,1" );

$row = mysql_fetch_array( $sql );

if( $row['countdownActive'] == "1" )
     // Countdown is active, get the current time in the countdown
     $countdownStartPoint = time() - $row['countdownTimer'];
else
     // No countdown is active
     $countdownStartPoint = 0;
?>

<html>
     <body>
          <script type="text/javascript">
               startJavascriptCountdown( <?php echo( $countdownStartPoint ); ?> );
          </script>
     </body>
</html>

thanks for the help, as a test i renamed my feilds with those you provided and put it online, filled in the database, added my dbconnect and i put it on a page called backupnew.php there is however no count down of time. any ideas why this is?

also this comes up in the source code of hte page after i filled in the database.

Code:

<html>

     <body>

          <script type="text/javascript">

               startJavascriptCountdown( 1295649614 );

          </script>

     </body>

</html>

Now I don't know what your original values are but it's definitely outputting a Unix timestamp. The problem is it's way too large.

Now when I said "startJavascriptCountdown" that was just a psuedocode function. You'll actually need to implement a Javascript countdown script. I was under the impression you already had this, but it looks like you don't. So let's take a slightly different approach and convert that script you made to be an AJAX script.

First thing's first. We need to change the way you're storing the time (which appears to be already incorrect).

When you start a database back up. You need to store two things in the database. 1) You need to flip the "countdownActive" column from 0 to 1. 2) You need to store the time, plus 15 minutes in the future. This is when the backup will take place. You can get that time by doing this:

PHP Code:


		
			
$backupStartTime = time() + ( 60 * 15 ); // 60 seconds times 15 minutes

I'd like you to create a file that contains just the PHP from the above example. Make it read like so:

ajaxTimer.php

PHP Code:


		
			
<?php

$sql = mysql_query( "SELECT `countdownActive`, `countdownTimer` FROM `myTable` WHERE `id` = 'myID' LIMIT 0,1" );

$row = mysql_fetch_array( $sql );

if( $row['countdownActive'] != "1" ){
     // No countdown is active
     die( "No backup scheduled." );
}

// If the script has gotten this far, a countdown is active

$backupStartTime = $row['countdownTimer'];

// Calculate the amount of minutes:
$countdownMinutes = ( ( $backupStartTime - time() ) / 60 ) % 60;
$countdownSeconds = ( $backupStartTime - time() ) % 60;

echo( "Database Backup in T-Minus " . $countdownMinutes . " minutes " . $countdownSeconds . " seconds!" );
exit();

?>

Now that we've got our PHP script in place we can work on AJAX, but before I start that I want you to put this into place and make sure it's working. So I want you to do this:

1) Implement the above script so that your countdown is displaying correctly. Don't forget to do both changes, to the DB and adding in the script.

2) Once it's working correctly, come back and post what is displaying when the ajaxTimer.php is loaded.

After that we can cover AJAX. On that topic I have one question: Are you willing to/familar with the jQuery framework? I find its AJAX interface is much more tame.

Code:

Database Backup in T-Minus -51 minutes -49 seconds!

this is what now comes up.

Code:

id         countdownActive         countdownTimer                    backupStartTime

1         1                        2011-01-22 01:39:42            2011-01-22 01:24:42

this is whats in the database and the time stamp is a date/time type, is that correct?

i can create a mysql dump of the table if that would be more help, and in regards to your JQuery question, no i have never worked with it, i would be willing to learn though :) thanks for your help so far.

sorry i miss read your post, i have now removed the structure code part of backupstarttime.

now i have

Code:

Database Backup in T-Minus -5 minutes -39 seconds!

with this in the time

Code:

2011-01-22 01:54:42

however it is counting upwards. by this i mean its going -39, -40, -41. ect

No no no, replace what used to be in countdownTimer by what is in the $backupStartTime variable and eliminate the `backupStartTime` column in the Database.

So your `countdownTimer` column should now read whatever is output by time() + ( 15 * 60 ).

Code:

Database Backup in T-Minus 14 minutes 28 seconds!

ok i got it working, sorry i miss understood your code again, i will read them deep and ask questions if i dont understand in the future, sorry about that.

so we have that working now, now i am wide awake whereas i wasnt last night as i was tired, so today i should understnad everything so much easier :)

Cool beans, now we'll start the client side. Basically what we'll be doing is sending a request to the server every second in order to pull down the countdown timer.

This will be *very basic* but it should help you understand what the basic principle is. After we've gotten your page to automatically update with the new time you should then be able to format it on your own to how you would like it to look.

First thing is first, let's get jQuery going. There is, to be certain, a way to do AJAX without jQuery; however, for first time users dealing with the DOM element can be intimidating and jQuery makes AJAX a nice little process.

So you know, jQuery is a Javascript Framework that was built to make coding Javascript easier, more efficient, and to have more functionality. The tradeoff is that it will slightly increase your page load time -- because of the additional resources required to download. This, however, hasn't stopped big companies (including Google) from using it.

To start, you'll need to link to the file. Google has kindly provided a point where you can link to it. You will need to add this line before all other Javascript includes in your website, and it will need to be in the <head> tags.

Code:

<script src='https://ajax.googleapis.com/ajax/libs/jquery/1.4.4/jquery.min.js' type='text/javascript'></script>

Now that we've got this, we'll need to add the Javascript. This will go AFTER the line above.

Code:

<script type="text/javascript">

        $(document).ready( function(){

                var refreshCountdown =         setInterval( function() {

                                                                        $.get( "/ajaxTimer.php", {}, function( data ){

                                                                                $("#countdownBlock").html( data );

                                                                        });

                                                                }, 1000 );

        });

</script>

So you understand, let's dissect this.

$(document).ready( function(){ ..... });

This line is a jQuery must have. All jQuery operations can only be done once the document has been loaded (ready) with the proper resources. This is something you will always use, and you will always put any jQuery code you have inside this code block.

var refreshCountdown = setInterval( function() { ... }, MILISECONDS );

setInterval is a Javascript function that tells the browser to do the function() every X seconds, where X is the second parameter. In our code it is set to 1000, which is 1 second.

$.get( "/ajaxTimer.php", {}, function( data ){ ... } );

This is the AJAX statement. What we're saying is GET data from a certain page. In this case, ajaxTimer.php. IMPORTANT: Make sure the path is correct. I assumed that ajaxTimer.php is in your web root (/). If it isn't you'll need to change the path to match where your script is.

The set of curly braces after the file path is used for sending variables to the script. If, for example, I wanted to send the variable apples to the script with a value of oranges I would then use { apples : 'oranges' } in those braces. However, since we don't need to send any data to the PHP script we don't need to put anything in them.

The function( data ){} block is what we want to happen AFTER the AJAX request has been completed. The data parameter is what Javascript receives BACK from the PHP page. So, in our example, data will contain either the text "Database backup in..." or "No backup scheduled.".

$("#countdownBlock").html( data );

This statement is telling jQuery to change the inner HTML of the element with an ID of "countdownBlock" to whatever is contained in data. You can read more about jQuery's .html method here.

So, now that we understand what the Javascript is doing we need to add the final piece of the puzzle. We need to add the element that is actually being updated. In this case I'll use a DIV, but it can be ANY element so long as it has an ID attribute which matches what we used in our JQuery statement (in my example, countdownBlock.

So, in the body of the HTML page we have all our Javascript on, I'll add this element:

Code:

<html>

        <head>ALL OF OUR JAVASCRIPT SHOULD BE HERE</head>

        <body>

                <b>Countdown Timer:</b><br />

                <div id="countdownTimer">Loading...</div>

        </body>

</html>

That's it! If everything worked then your countdown timer should be updated every second. If it didn't work, or even if it did and you just have questions, please just let me know what errors you're getting and/or what you need clarification on.

Hope this helps!

Code:

<html>

<head>

<script src='https://ajax.googleapis.com/ajax/libs/jquery/1.4.4/jquery.min.js' type='text/javascript'></script>

<script type="text/javascript">

        $(document).ready( function(){

                var refreshCountdown =         setInterval( function() {

                                                                        $.get( "/ajaxTimer.php", {}, function( data ){

                                                                                $("#countdownBlock").html( data );

                                                                        });

                                                                }, 1000 );

        });

</script>

</head>

        <body>

                <b>Countdown Timer:</b><br />

                <div id="countdownTimer">Loading...</div>

        </body>

</html>

im sorry im a tad bit confused, where do i put what parts of data, if possible could you fill this in with what it should be from the php coding so i know exactly what you mean?

Where you've put the code in your HTML page is correct. The ajaxTimer.php page is a separate file. If you visit that page it should work, assuming your path to the ajaxTimer.php page is correct. If not you'll probably end up with a 404 error in your DIV.

all that comes up is this

Code:

Countdown Timer:

Loading...

My fault, I messed up the IDs. Replace this line:

<div id="countdownTimer">Loading...</div>

With this one:

<div id="countdownBlock">Loading...</div>