order by rand() problem

teoexe
teoexe used Ask the Experts™
on


i have this command:
$sql4 = "SELECT * FROM tbl_member WHERE bot=1 order by rand() limit 1";

"order by rand() " will choose "bot=1 " randomly and sometimes he choose same "bot=1".This is wrong  because i don`t want to choose same "bot=1".

Someone please tell me how to do it in order to do not duplicate same " bot=1 "?
Regards.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
you mean, when you run the same command again, to avoid to get the same "bot=1" again?
how many rows are in the table for that condition?

if you HAVE to ensure the same row is not returned within X time, you HAVE to store the values in another table and add a condition to the query to exlude those rows

Commented:
Well, `ORDER BY rand()` creates a new list in a new order each time it is executed. So, for a row to not repeat, you would have to record the IDs of the previous rows and tell the query not to select them, like this ():
...
WHERE
  bot = 1 AND
  row_id NOT IN (4, 23, 21)
ORDER BY
  rand()

Open in new window

You might generate RAND with PHP, but you need 2 SQL queries. It might hit your performance in some really large tables, or with InoDB tables, but it is a good solution. Read here for more: http://www.phpbuilder.com/board/showthread.php?t=10338930
// get the highest 2 ids 
$res1 = mysql_query("SELECT id FROM table ORDER BY id DESC LIMIT 2"); 
// generate random no between 0 and second highest id 
$top = mysql_result($res1,1); 
$rand = rand(0,$top); 
// now query for id greater then random no 
$sql = "SELECT * FROM table WHERE id>$rand ORDER BY id DESC LIMIT 1"

Open in new window

Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
WHERE
  bot = 1 AND
  row_id NOT IN (4, 23, 21)
ORDER BY
  rand()

it`s a goo ideea , but the problem is that , if we have 2 bot=1 selected in our database ,
bot=1 -> dex
bot=1 -> pep

ID`s are:  
dex - 12
pep- 16

how can i allow "dex" to work now and stay next round? ( next round "pep" must work )

like this

dex
pep
dex
pep
dex
pep


Regards,

Commented:
Add another condition:
...
WHERE
  bot = 1 AND
  row_id NOT IN (12) AND
  some_field <> 'dex'
ORDER BY
  rand()
 
---
 
...
WHERE
  bot = 1 AND
  row_id NOT IN (12, 16) AND
  some_field <> 'pep'
ORDER BY
  rand()

Open in new window

Author

Commented:
my head will blow :)

i`m to newbie for this

there is the code
<?php
	include('db.php');
	include("functions.php");
 
	$id = $_GET['id'];
 
	$sql2 = "SELECT MAX(bid_price) as max, mid, bid_type, id FROM tbl_bid_history WHERE product_id='$id' GROUP BY mid, bid_type, id ORDER BY id DESC LIMIT 1";
	$result2 = mysql_query($sql2);
	$row2 = mysql_fetch_array($result2);
	$bid_price = $row2['max'] + 1.00;
 
	$sql4 = "SELECT * FROM tbl_member WHERE bot=2 order by rand() limit 1";
 
 
	$result4 = mysql_query($sql4);
 
	$row4 = mysql_fetch_array($result4);
 
	   
	mysql_query("INSERT INTO tbl_bid_history SET product_id='$id',mid='".$row4['id']."', bid_price='$bid_price', bid_type='".$row2['bid_type']."'");	
	
	//$t2 = getExpDate($id);
	//$ttt =$t2 - time() + 65;
	$ttt =time() + 65;
	$query123 = "UPDATE tbl_product SET expiry_date='$ttt' WHERE id='$id'";
	$query = mysql_query($query123);
	echo "";
?>

Open in new window

Author

Commented:
someone can please help me ?

Commented:
Not sure I understand... With `$sql4` you want to select a random member? But next (or any other) time you don't want the same member again? And not only that, but some other field should be different, too?

Please, give us some examples with mock data and the results you want to get...

Author

Commented:
in our database we have BOT field. ( bot=2 ).Real users have bot=0 ( by default ) and robots bot=2 ( changed from 0 to 2 manually )

So , robots name has bot=2 ,
$sql4 must select random robots usernames ( only from  that usernames that we have changed to bot=2 field ) BUT DO NOT REPEAT SAME USER AFTER

GOOD !

dex
pep
dex
pep
dex
pep


WRONG !

dex
pep
pep
pep
dex
dex
pep
you have to keep track of the user id which has already been selected by the system I am posting your code slightly modified by me.
<?php
        include('db.php');
        include("functions.php");
        session_start();
 
        $id = $_GET['id'];
 
        $sql2 = "SELECT MAX(bid_price) as max, mid, bid_type, id FROM tbl_bid_history WHERE product_id='$id' GROUP BY mid, bid_type, id ORDER BY id DESC LIMIT 1";
        $result2 = mysql_query($sql2);
        $row2 = mysql_fetch_array($result2);
        $bid_price = $row2['max'] + 1.00;
        if(isset($_SESSION['row_id']))
           $user_id=$_SESSION['user_id']);
        else
           $user_id="dummyText"; //This dummyText string should not be an actual username in the database
        
        $sql4 = "SELECT * FROM tbl_member WHERE bot=2 AND id<>'$user_id' order by rand() limit 1";
 
 
        $result4 = mysql_query($sql4);
 
        $row4 = mysql_fetch_array($result4);
        $_SESSION['user_id']=$row4['id'];
           
        mysql_query("INSERT INTO tbl_bid_history SET product_id='$id',mid='".$row4['id']."', bid_price='$bid_price', bid_type='".$row2['bid_type']."'");        
        
        //$t2 = getExpDate($id);
        //$ttt =$t2 - time() + 65;
        $ttt =time() + 65;
        $query123 = "UPDATE tbl_product SET expiry_date='$ttt' WHERE id='$id'";
        $query = mysql_query($query123);
        echo "";
?>

Open in new window

Author

Commented:
it dosen`t work :-((
sorry it was my mistake. Please change the line number 12 which contains:
if(isset($_SESSION['row_id']))
to
if(isset($_SESSION['user_id'])) .
Please let me know if you have any problem now.
The final modified code should lokk like this one :

<?php
        include('db.php');
        include("functions.php");
        session_start();
 
        $id = $_GET['id'];
 
        $sql2 = "SELECT MAX(bid_price) as max, mid, bid_type, id FROM tbl_bid_history WHERE product_id='$id' GROUP BY mid, bid_type, id ORDER BY id DESC LIMIT 1";
        $result2 = mysql_query($sql2);
        $row2 = mysql_fetch_array($result2);
        $bid_price = $row2['max'] + 1.00;
        if(isset($_SESSION['user_id']))
           $user_id=$_SESSION['user_id']);
        else
           $user_id="dummyText"; //This dummyText string should not be an actual username in the database
        
        $sql4 = "SELECT * FROM tbl_member WHERE bot=2 AND id<>'$user_id' order by rand() limit 1";
 
 
        $result4 = mysql_query($sql4);
 
        $row4 = mysql_fetch_array($result4);
        $_SESSION['user_id']=$row4['id'];
           
        mysql_query("INSERT INTO tbl_bid_history SET product_id='$id',mid='".$row4['id']."', bid_price='$bid_price', bid_type='".$row2['bid_type']."'");        
        
        //$t2 = getExpDate($id);
        //$ttt =$t2 - time() + 65;
        $ttt =time() + 65;
        $query123 = "UPDATE tbl_product SET expiry_date='$ttt' WHERE id='$id'";
        $query = mysql_query($query123);
        echo "";
?>

Open in new window

Author

Commented:
i partial resolved the problem

that problem is this: If i opened 2 browsers ( explorer and mozilla ) ... at second 00:00:01 , 2 bots will place bids , if i opened 3 browsers .... at 00:00:01 ... 3 bots will place the bid !!!! --- this must be resolved , it dosen`t matter how many browsers ( or users ) are on that auction page.... at 00:00:01 .. only 1 bot must place the bid

the bot script is : uppp.php

attached bid_calc.php

at 00:00:01 second , bid_calc.php execute the uppp.php file
<table width="215" border="0" align="center" cellpadding="0" cellspacing="0">
                                          <tr>
                                            <td align="left" valign="top" background="image/w_t_c.gif"><img src="image/w_t_l.gif" width="12" height="11" /></td>
                                            <td align="left" valign="top" background="image/w_t_c.gif"><img src="image/w_t_c.gif" width="5" height="11" /></td>
                                            <td align="right" valign="top" background="image/w_t_c.gif"><img src="image/w_t_r.gif" width="12" height="11" /></td>
                                          </tr>
                                          <tr>
                                            <td colspan="3" align="left" valign="top" class="TblBrd4"><table width="100%" border="0" cellspacing="0" cellpadding="0">
                                              <tr>
                                                <td align="left" valign="top">
												
											<!--if auction is about to end-->
												<?php /*if($currentTime > $rows['rem_date']){												
												
												include("auc_complete.php");?>
												
											
												
												
												
												<? } else {*/ ?>
												
													<table width="98%" border="0" align="center" cellpadding="0" cellspacing="4">
                                                  <tr>
                                                    <td width="100%" align="center" valign="middle" class="Light2 Blue1">
											
											
													<div id="timeerLive" <?php if($rows['running_status']=='Paused'){?> style="display:none" <? } else {?>style="width:120px; font-size:20px; font-weight:bold; font-family:Arial, Helvetica, sans-serif" <? } ?> >&nbsp;</div>													</td>
                                                  </tr>
                                                  <tr>
                                                    <td align="center" valign="middle" class="Light2 Blue1">&nbsp;</td>
                                                  </tr>
                                                  <tr>
                                                    <td align="center" valign="middle" class="Light2 Blue1">
<?php if($rows['running_status']=='Paused') {
$timeDiff = ($rows['expiry_date']-$rows['paused_date']); 
echo "<p style=font-size:25px; font-weight:bold; font-family:Arial, Helvetica, sans-serif>"."<b>".calc_counter_from_time($timeDiff)."</b>"."</p>";
}?>
 
<div id="txtLive" style="height:50px"></div>
<input name="diffval" id='diffval' type="hidden" value="<?=$diffs?>" />
<?
//$t1=  strtotime(date("Y-m-d h:i:s"));
$t1 = time();
$serverTime = $t1;
 
//$t1 = strtotime("2009-02-20 h:i:s"); // AA -- test purposes only
$t2 = getExpDate($rows['id']);
$expiryTime = $t2;
 
 
$diffs =  $t2 - $t1;
 
$currentBidPrice = getCurrentBid($rows[id]);
$currentBidder =   getCurrentBidder($rows[id]);
/*if (diffs<=20)
{
getUpdateExpDate($rows['id'] , 65);
}*/
?>
 
<input type='hidden' id='expiry_time' value='<?=$expiryTime;?>'>
<input type='hidden' id='server_time' value='<?=$serverTime;?>'>
<input type='hidden' id='step_time' value='0'>
 
<script type="text/javascript">
var current='<?=$diffs?>';
var page_request = false;
function ajaxpage_local(_url) {
		page_request = false;
		if (window.XMLHttpRequest) // if Mozilla, Safari etc
			page_request = new XMLHttpRequest()
		else if (window.ActiveXObject){ // if IE
			try {
				page_request = new ActiveXObject("Msxml2.XMLHTTP")
			} 
			catch (e){
				try{
					page_request = new ActiveXObject("Microsoft.XMLHTTP")
				}
				catch (e){}
			}
		} else {
			return false;
		}
		page_request.onreadystatechange=function(){
			loadpage_local(page_request)
		}
		page_request.open('GET', _url, true)
		page_request.send(null)
	}
	
	
	function functoadd(_url) {
		page_request12 = false;
		if (window.XMLHttpRequest) // if Mozilla, Safari etc
			page_request12 = new XMLHttpRequest()
		else if (window.ActiveXObject){ // if IE
			try {
				page_request12 = new ActiveXObject("Msxml2.XMLHTTP")
			} 
			catch (e){
				try{
					page_request12 = new ActiveXObject("Microsoft.XMLHTTP")
				}
				catch (e){}
			}
		} else {
			return false;
		}
		page_request12.onreadystatechange=function(){
 
		}
		page_request12.open('GET', _url, true)
		page_request12.send(null)
	}
	
	
function calc_counter_from_time() { //diff,temp) {
 //temp=parseInt(temp)+parseInt(1000);
 var tem="temps";
 //var current=document.getElementById('diffval').value;
 
 var expiryTime = parseInt(document.getElementById('expiry_time').value); 
 var cTime = parseInt(document.getElementById('server_time').value); 
 var stepTime = parseInt(document.getElementById('step_time').value);
 
 cTime = cTime + stepTime;
 document.getElementById('step_time').value = stepTime+1;
 
 diff = expiryTime - cTime;
 
 
 var divnamech="timeerLive";
//  dd=diff-currenttime;
 
 
 
if (diff > 0) {
    hours=Math.floor(diff / 3600)
 
    minutes=Math.floor((diff / 3600 - hours) * 60)
 
    seconds=Math.round((((diff / 3600 - hours) * 60) - minutes) * 60)
	
  } else {
  functoadd('uppp.php?id='+<? echo $rows['id']?>);
  document.getElementById('step_time').setAttribute('value' , 0);
  document.getElementById('expiry_time').setAttribute('value' , 65);
  document.getElementById('server_time').setAttribute('value' , 0);
  //alert(seconds);
    hours = 0;
    minutes = 0;
    seconds = 1;
  }
 
  if (seconds == 60) {
    seconds = 0;
  }
 
  if (minutes < 10) {
    if (minutes < 0) {
      minutes = 0;
    }
    minutes = '0' + minutes;
  }
  if (seconds < 10) {
    if (seconds < 0) {
      seconds = 0;
    }
    seconds = '0' + seconds;
  }
  if (hours < 10) {
    if (hours < 0) {
      hours = 0;
    }
    hours = '0' + hours;
  }
  /*if (hours==00&&minutes==00&&(seconds==00||seconds==01))
{
hours=0;
hours='0'+hours;
minutes=1;
minutes='0'+minutes;
seconds=5;
seconds='0'+seconds;
//functoadd('uppp.php?id='+<? echo $rows['id']?>);
}*/
/*
if (hours==00&&minutes==00&&seconds<=05)
{
functoadd('uppp.php?id='+<? echo $rows['id'];?>);
}*/
 
document.getElementById(divnamech).innerHTML=hours+":"+minutes+":"+seconds;
 
    //alert(hours);
//if(hours==00&&minutes==00&&seconds==00){
	//pageRefrersh('product_detail.php?id='+<? echo $rows['id']?>);  
//}
//else
	//{
	 // var next=parseInt(dday*24);
     // next=next+dhour;
	//current=current-1;
	//if(hours==00&&minutes==00&&seconds<=10){
	//if(seconds==<? echo intval(rand(1,10)) ?>) ajaxpage_local('bidcheck.php');
	//document.getElementById(divnamech).innerHTML='<span style="color:#FF0000">'+hours+":"+minutes+":"+seconds+'</span>';
	//}
	//else
	//{
	//document.getElementById(divnamech).innerHTML=hours+":"+minutes+":"+seconds;
	//}
	
	//document.getElementById(divnamech).innerHTML=hours+":"+minutes+":"+seconds;
	<?
	//$y=i+1;
	?>
	/*
	   ajaxpage('bid_history.php?tit='+<? echo $rows['id']?>+'&update=bid',  'bidHistory');
		 ajaxpage('bid_hits_prd_detail.php?tit='+<? echo $rows['id']?>, 'txtLive');
	*/
 
		setTimeout("calc_counter_from_time()", 1000); //"+current+","+temp+")",1000);
	//}
//alert(hours + ":" + minutes + ":" + seconds);
//return hours + ":" + minutes + ":" + seconds;
}
	function loadpage_local(page_request, containerid) {
		if (page_request.readyState == 4 && (page_request.status==200 || window.location.href.indexOf("http")==-1)) {
			//document.getElementById(containerid).innerHTML=page_request.responseText;
			//alert(eval('(' + page_request.responseText + ')'));
			var res = page_request.responseText;
			var h1 = res.substring(0, res.indexOf('@'));
			document.getElementById('txtLive').innerHTML = h1;
			var h2 = res.substring(res.indexOf('@') + 1, res.indexOf('|'));
			document.getElementById('bidHistory').innerHTML = h2;
			var h3 = res.substring(res.indexOf('|')+1, res.indexOf('~'));
			document.getElementById('savingBox').innerHTML = h3;
			var expiryTime = res.substring(res.indexOf('~')+1, res.indexOf('!'));
			document.getElementById('expiry_time').value = expiryTime;
			var serverTime = res.substring(res.indexOf('!')+1);
			document.getElementById('server_time').value = serverTime;
			document.getElementById('step_time').value = 0;
		}
	}
 
function updateAuctionDetails() {
	/*
	ajaxpage('bid_hits_prd_detail.php?tit='+<? echo $rows['id']?>, 'txtLive');
	ajaxpage('bid_history.php?tit='+<? echo $rows['id']?>, 'bidHistory');
	ajaxpage('saving.php?tit='+<? echo $rows['id']?>, 'savingBox');
	*/
 
	ajaxpage_local('aa_auction_details.php?tit='+<? echo $rows['id']?>);
 
	setTimeout("updateAuctionDetails()", 3000);
}
 
calc_counter_from_time(); /* '<?=$diffs?>'); */
updateAuctionDetails();
 
</script>
<!-- </div> txtlive--></td>
                                                    </tr>
                                                  
                                                  
                                                  
                                                  <tr>
                                                    <td height="28" align="center" valign="top" class="Light05">&nbsp;</td>
                                                  </tr>
												  
												  
												  
												  
                                                  <tr>
                                                    <td height="28" align="center" valign="top" class="Bdr0B"><br/>
 
<?php if($rows['running_status']!='Paused'){?>
<?php if($_SESSION['mid']!=""){
			if($bidAvailable>0){ 
 
/*
<a href="javascript:ajaxpage('bid_hits_prd_detail.php?tit='+<? echo $rows['id']?>+'&update=bid', 'txtLive'); javascript:ajaxpage('bid_history.php?tit='+<? echo $rows['id']?>+'&update=bid',  'bidHistory'); ajaxpage('saving.php?tit='+<? echo $rows['id']?>, 'savingBox');">
*/			
?>
<a href="javascript:ajaxpage_local('aa_auction_details.php?tit='+<? echo $rows['id']?>+'&update=bid');">
<img src="image/bid.jpg" width="106" height="31" border="0" /></a>
			<? } else { ?>
			<div id="content"><a href="javascript:showDialog('Recharge Bid','Please Recharge Your Bid Account.','error',2);"><img src="image/bid.jpg" width="95" height="27" border="0" /></a></div>
<? }?>
<? }
else{
?>
<a href="login_us.php" onMouseOut="MM_swapImgRestore()" onMouseOver="MM_swapImage('Image<?php echo $rows['id'];?>','','image/bid_login.jpg',1)"><img src="image/bid.jpg" name="Image<?php echo $rows['id'];?>" width="143" height="39" border="0" id="Image<?php echo $rows['id'];?>" /></a>
<? }?>
<? } else {?>
				<img src="image/paused.gif" name="Image<?php echo $i;?>" width="98" height="33" border="0" /><? }?>													</td>
                                                  </tr>
                                                </table>
											<? 	//}?>
												
												
												
												
												</td>
                                              </tr>
                                              <tr>
                                                <td height="15" align="left" valign="top"></td>
                                              </tr>
                                              <tr>
                                                <td align="left" valign="top"><table width="98%" border="0" align="center" cellpadding="0" cellspacing="4">
                                                  
                                                  
                                                  <tr>
                                                    <td height="17" align="left" valign="top" class="small_text"><div align="center">
                                                    <div align="center">Pentru a evita anumite neplaceri , va rugam licitati inainte de secunda 03 ( 00:00:03 ) <strong class="small_text"></strong></div></td>
                                                  </tr>
                                                  <tr>
                                                    <td height="17" align="left" valign="top" class="saving">Economii:<strong class="header_text"></strong></td>
                                                    </tr>
                                                  <tr>
                                                    <td align="center" valign="top" class="BgBlue3">
													<div id="savingBox">
													</div>
													</td>
                                                  </tr>
                                                  <tr>
                                                    <td align="center" valign="top" class="small_text">Pret in magazin * -  pretul actual in magazinele din Romania </td>
                                                  </tr>
                                                </table></td>
                                              </tr>
                                              
                                            </table></td>
                                            </tr>
                                          <tr>
                                            <td align="left" valign="top" background="image/w_b_c.gif"><img src="image/w_b_l.gif" width="12" height="11" /></td>
                                            <td align="left" valign="top" background="image/w_b_c.gif"><img src="image/w_b_c.gif" width="5" height="11" /></td>
                                            <td align="right" valign="top" background="image/w_b_c.gif"><img src="image/w_b_r.gif" width="12" height="11" /></td>
                                          </tr>
                                        </table>

Open in new window

Author

Commented:
attached uppp.php

thank you so much !!!

God Bless you and your work
<?php
	include('db.php');
	include("functions.php");
 
	$id = $_GET['id'];
 
	$sql2 = "SELECT MAX(bid_price) as max, mid, bid_type, id FROM tbl_bid_history WHERE product_id='$id' GROUP BY mid, bid_type, id ORDER BY id DESC LIMIT 1";
	$result2 = mysql_query($sql2);
	$row2 = mysql_fetch_array($result2);
	$bid_price = $row2['max'] + 1.00;
 
	$sql4 = "SELECT * FROM tbl_member WHERE bot=2 order by rand() limit 1";
 
 
	$result4 = mysql_query($sql4);
 
	$row4 = mysql_fetch_array($result4);
 
	   
	mysql_query("INSERT INTO tbl_bid_history SET product_id='$id',mid='".$row4['id']."', bid_price='$bid_price', bid_type='".$row2['bid_type']."'");	
	
	//$t2 = getExpDate($id);
	//$ttt =$t2 - time() + 65;
	$ttt =time() + 65;
	$query123 = "UPDATE tbl_product SET expiry_date='$ttt' WHERE id='$id'";
	$query = mysql_query($query123);
	echo "";
?>

Open in new window

Thank you angellll for your comment on this. I fully accept with you.
I have provided an working solution to this problem and hence I should be awarded with points. Thank you.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial