[Icematikx]

Hello,

I currently have a view exchange script on my site. The site allows users to exchange and view YouTube videos. However, the script seems to pull videos in the order they were added to the system by the user.

I would like to change it to a random ID that it pulls. I have read the forums and searched for over 5topics, trying to implement the advice given into my script but it never works. Here's the script so far:

<?
include('header.php');
$id = $db->EscapeString($_GET['id']);

if($_GET['a'] == "skip"){
$sql = $db->Query("SELECT id FROM `youtube` WHERE `id`='".$id."'");
$sit = $db->GetNumRows($sql);
if($sit > 0){
$db->Query("INSERT INTO `viewed` (user_id, site_id) VALUES('".$data['id']."','".$id."')");
}}
?>
<div class="block medium right">
			<div class="top">	
                  <h1>Earn Coins - Youtube</h1>
            </div>
			<div class="content">
<?
  $sql = $db->Query("SELECT id,youtube,title,cpc FROM `youtube` WHERE (`active` = '0' AND `points` >= `cpc`) AND (`id` NOT IN (SELECT `site_id` FROM `viewed` WHERE `user_id`='".$data['id']."') AND `user`!='".$data['id']."') ORDER BY `cpc` DESC LIMIT 0, 1");
  $site = $db->FetchArray($sql);
if($site['id'] > 0){
?>			
<script src="js/swfobject.js"></script>
<script type="text/javascript">
var playing = false;
var fullyPlayed = false;
var interval = '';
var played = 0;
var length = 15;

function YouTubePlaying(){
	played += 0.1;
	roundedPlayed = Math.ceil(played);
	document.getElementById("played").innerHTML = Math.min(roundedPlayed,length);
	if (roundedPlayed == length){
		if (fullyPlayed == false){
			YouTubePlayed();
			fullyPlayed = true;
		}
	}
}
function YouTubePlayed(){
    var response = '<?=$site['youtube']?>';
    $.post("pages/ytreceive.php",
		{  data: response},
		function(result){
			if(!isNaN(result)) { 
				var curr_val = $('#points').text();
				var new_val = parseInt(curr_val)+parseInt(result);
				$('#points').text(new_val); 
			}
		}
	 );
	document.getElementById(response).style.visibility = "visible";
}

function onYouTubePlayerReady(playerId){
	ytplayer = document.getElementById("myytplayer");
	ytplayer.addEventListener("onStateChange", "onYouTubePlayerStateChange");
}
function onYouTubePlayerStateChange(newState){
	if (newState == 1){
		playing = true;
		interval = window.setInterval('YouTubePlaying()',100);
	}else{
		if (playing) window.clearInterval(interval);
		playing = false;
	}
}
  function refreshpage()
   {
      window.location.reload();
   }
</script>
<center>
<div style="width: 520px; padding: 10px;">
<h3>"<?=$site['title']?>"</h3>
View this video for 15 seconds and after that you will receive <b><?=$site['cpc']?> coins</b><br/><br/>
<div id="ytPlayer">You need Flash player 8+ and Javascript enabled to view this video.</div>
<script type="text/javascript">
var params = { allowScriptAccess: "always" };
var atts = { id: "myytplayer" };
swfobject.embedSWF("http://www.youtube.com/v/<?=$site['youtube']?>?enablejsapi=1&playerapiid=ytplayer&autoplay=1", "ytPlayer", "425", "356", "8", null, null, params, atts);
</script>
<br/>

<br />Must play for <span id="played">0</span>/15 seconds (<a href="youtube.php?a=skip&id=<?=$site['id']?>" style="color:blue">Skip</a>)
<div id="<?=$site['youtube']?>" style="visibility:hidden"><a href="javascript:refreshpage()">View Next Video</a></div>
</div></center>
<?}else{?>
<div class="msg">
<div class="error">Sorry, there are no more coins to be earned at the moment. Please try again later.</div>
<div class="info"><a href="buy.php"><b>Feel like you need more coins? You can purchase them now!</b></a></div></div>
<?}?>
				</div>
			</div>		
<?include('footer.php');?>

As said, I would instead prefer it to pick a random ID from the 'youtube' table and display that - instead of it being in a specific order.

Any help would be thankyou!

[Icematikx]

Managed to fix it partially by adding:

$range_result = mysql_query( " SELECT MAX(`id`) AS max_id , MIN(`id`) AS min_id FROM `youtube` ");
$range_row = mysql_fetch_object( $range_result ); 
$random = mt_rand( $range_row->min_id , $range_row->max_id );
$sql = mysql_query( " SELECT id,youtube,title,cpc FROM `youtube` WHERE `id` >= $random LIMIT 0,1 ");
  $site = $db->FetchArray($sql);

BUT - it will show the users OWN videos and show the video more than once. Anyway to fix this?

[e_i_pi]

Novel way of doing it, but you can do it all SQL-side by running a query like this:

SELECT id,youtube,title,cpc
FROM `youtube`
ORDER BY RAND()
LIMIT 0,1

I'm not sure what would be more efficient. If you had a very very large table (somewhere in the region of >250k rows), the way you're doing it may well be quicker, but you run into a problem when you have IDs like this:

1
100
101
102
103

...which can happen when you're deleting rows for example. About 96-7% of the time, you'll get the record with ID = 100, which is less than ideal. I'd go with leaving it up to the SQL engine using RAND().