Problem| Getting random values from db,how not to repeat them

Question

I am creating a website that gets random videos from the db. My problem is that my code to get the random video is this:

select id,content from videos order by rand() limit 1

And I don't want the user to see the same video until 3 other videos (at least) were played before.

Do you have any suggestion on how to do that? This is how my site works currently.

1. HTML-AJAX(CALL FOR VIDEO URL)
2. PHP(RETURN RANDOM VIDEO URL) One video.
3. AJAX(DISPLAY VIDEO)

[EDITED] Another problem I am facing is that I need to return only one video url, because this is how my ajax call look like:

success: function(data){

            $('#content').html('<div id="ytapiplayer">You need Flash player 8+ and JavaScript enabled to view this video.</div>');
            var params = { allowScriptAccess: "always" };
            var atts = { id: "ytapiplayer" };
            swfobject.embedSWF(data.vidData+"&enablejsapi=1&playerapiid=ytapiplayer?autoplay=1", "ytapiplayer", "500", "405", "8", null, null, params, atts);
}

Thanks in advance.

Answer 1

Could the video id be sent to the client? Then from there the client (Javascript) request the video. Here how that could be played:

1. Ajax the the list of video ids
2. insert them in an Array in javascript (var toWatch)
3. Random the array
4. Get the first video
5. Remove the id from the first array. You might want to keep a trace of that id in an other array
6. repeat 4-5

In javascript it might look like :

$.post("getVideoId.php",function(videoId){
   var aVideoToWatch = videoId.split(',').sort(randOrd);

   for(var x=0; x<aVideoToWatch.length;x++){
       $.post("getAVideo(aVideoToWatch[x])",function(){
           //play the video
       })
   }
})

// source : http://javascript.about.com/library/blsort2.htm
function randOrd(){
 return (Math.round(Math.random())-0.5); }

Answer 2

You could just use (We'll grab fifteen so we don't have to query the server so much):

SELECT id, content FROM videos ORDER BY RAND() LIMIT 15

Hand those fifteen to the browser and allow it to request the videos. Once it has seen the fifteen, it can ask the server for three more. It can skip ones it's already played by storing played video ids.

If you are delivering results via Ajax and JSON you can just return the concatenation of the results arrays:

<?php
$query = $pdo->query('SELECT id, content FROM videos ORDER BY RAND() LIMIT 10');
$videos = $query->fetchAll();
echo json_encode($videos);
?>

Then in JS:

(function playRandomVideos() {  //Closures are cool
    $.getJSON('getRandomVideos.php', {success: function(videos) {
        for(video in videos) {
            if(video.id in playRandomVideos.played) {
                continue;
            }
            play(video);
            playRandomVideos.played.push(video.id);
        }
        playRandomVideos();
    }});
})();
playedRandomVideos.played = [];