asked on

order by rand() problem

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.

Guy Hengel [angelIII / a3]

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

Aleksandar Bradarić

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

Andrej Pirman

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

teoexe

ASKER

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,

Aleksandar Bradarić

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

teoexe

ASKER

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

teoexe

ASKER

someone can please help me ?

Aleksandar Bradarić

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...

teoexe

ASKER

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

souvik2008

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

teoexe

ASKER

it dosen`t work :-((

ASKER CERTIFIED SOLUTION

souvik2008

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

teoexe

ASKER

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

teoexe

ASKER

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

souvik2008

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.