CODEWITHSUNDEEP
phpsql
user2432677
user2432677

Reputation: 75

How do I make my random MySQL query automatically refresh every 5 seconds

I have creating a little section on my webpage that changes randomly everytime the webpage opens. The code looks like this.

<div id ="quote-text">
    <?php
    mysql_connect("localhost", "xxxxxxx", "xxxxxxx") or die(mysql_error());
    mysql_select_db("xxxxxxx") or die(mysql_error());
    $result = mysql_query("SELECT * FROM quotes WHERE approved=1 ORDER BY RAND () LIMIT 1") 
    or die(mysql_error()); 
    while($row = mysql_fetch_array( $result )) {
        echo "<img src=http://www.xxxxxxxxxx.com/images/".$row['image'] ." width=280px ><br>";
        echo '<span class="style2">'.$row['quote'].'</span class>';
        echo "<tr><td><br>";
        echo "<tr><td>";
    } 
    echo "</table>";
    ?>
</div>

What do I need to do to make this change every 5 seconds randomly withoutrefreshing the whole page?

thank you

Upvotes: 1

Views: 1495

Answers (3)

Menelaos
Menelaos

Reputation: 26004

I'd say the most optimized solution would be to use a solution that makes use of both PHP, and javascript/Jquery.

First off it I would avoid to make an AJAX call to a PHP script every 5 seconds..

Instead you could make one call every X number of minutes and get a set of 12X images.

I would then use javascript, with setInterval to have the client change the image.

Halfway through, you can make another call to the PHP script to add new elements to your set of images, and remove the previous.

An approach like this would reduce overhead both clientside and serverside.

Update: Below a rough implementation of this method

Javascript:

<?php
if(isset($_GET['getBanners']))
{
    header('Content-Type: application/json');
    mysql_connect("localhost", "root", "") or die(mysql_error());
    mysql_select_db("stackoverflow2") or die(mysql_error());

    $json_rows = array();

    $result = mysql_query("SELECT * FROM quotes WHERE approved=1   ORDER BY RAND ()  LIMIT 12;") 

    or die(mysql_error()); 
    $element = 0;
    while($row = mysql_fetch_array( $result )) {
        $json_rows[$element] = $row['image'];
        $element++;
    } 

    print '{"dataVal":'.json_encode($json_rows).'}';
    return;
}
?>
<html>
<head>

<script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js" ></script>
<script>
//alert('test1');
var randomBanners = new Array ( );
var currentBannerIndex = 0;

function readNewBanners(startElement, numElements)
{
    $.getJSON('http://127.0.0.1/stackoverflow/Banner.php?getBanners=1', function(data) {
            for (var i = startElement; i < data.dataVal.length && i<startElement + numElements ; i++) {
            randomBanners[i] = data.dataVal[i];
            }           
    });
}

function refreshBannerImage()
{
    if(document.getElementById('banner') == undefined) return;
    document.getElementById('banner').innerHTML = ("<img src='"+randomBanners[currentBannerIndex]+"'/>");
    currentBannerIndex = (currentBannerIndex+1)%12;
}

$( document ).ready(function() {
    readNewBanners(0, 12);
    setInterval(function() {
          readNewBanners(0, 12);
    }, 60000);
     setInterval(function() {
          refreshBannerImage();
    }, 500);
});
</script>

</head>
<body>

<div id="banner">
Banner Here
</div>

</body>
</html>

SQL:

   create table quotes
    (
    image varchar(10),
    approved int
    );

    insert into quotes values ('http://dummyimage.com/600x400/000/fff&text=1',1);
    insert into quotes values ('http://dummyimage.com/600x400/000/fff&text=2',1);
    insert into quotes values ('http://dummyimage.com/600x400/000/fff&text=3',1);
    etc...

Upvotes: 2

SoftwareAndOutsourcing
SoftwareAndOutsourcing

Reputation: 140

You need to use AJAX for that. I suggest you to use the jQuery or a similar framework. Here is a nice example of what you want How to update a specific div with ajax and jquery. Add a setInterval() call like in this post http://forum.jquery.com/topic/jquery-setinterval-function and you are done.

Upvotes: 0

immulatin
immulatin

Reputation: 2118

You will need to make an AJAX call to change content on the page without refreshing.

Check out the W3Schools tutorial here: http://www.w3schools.com/ajax/ajax_intro.asp

Or even better use the mozilla tutorial:

https://developer.mozilla.org/en-US/docs/AJAX/Getting_Started

Upvotes: 2

Related Questions