[RESOLVED] 5 at a time randomly

[Ethan22]

if(!isset($COOKIE["a"])){//Check if cookie set, if not set to 1.
setcookie("a", 1, time()+606024*730);}
if(isset($COOKIE["a"])){
$a=$_COOKIE["a"];}

$lastrow =  mysql_query("SELECT SELECT MAX(id) FROM `business`");      

$rand = mt_rand(1,$lastrow);
mysql_query("SELECT id FROM business WHERE id>=1 ORDER BY $rand LIMIT 5")OR die(': ' . mysql_error()); 

This returns 5 id rows at a time 3,4,5,6,7,8, or 13,14,15,16,17 etc. on each refresh.
I need to know how to mix up 'the' 5 rows at a time randomly?
So instead the 5 rows at a time could be any id rows taken that are all random instead. Like 19, 2, 9, 14, 7 etc. instead. If it's the order by, I don't know how to remove that to write it right, or something else. Please let me know thank you.

[dagon]

order by RAND(), but this has big down side on a large data set

[Ethan22]

Thank you very much for replying sir. Yes many others told me the same thing, ORDER BY rand() is wrong way that's why I had the other. Causes all kinds of problems. That also return same results 7,8,9,10,11 also, than what I just said I need instead.

[dagon]

its not 'wrong' it has its place, how big is the data set?

[Ethan22]

I'd rather use the recommended, also I can't use it because it does the same thing this does, thanks a lot. So there is something else in this

mysql_query("SELECT id FROM business WHERE id>=1 ORDER BY $rand LIMIT 5")OR die(': ' . mysql_error()); 

that is missing.

[dagon]

you're doing it wrong if it produces the same results. As for recommended, even the mysql manual recommends order by RAND(), so that's rather meaningless.

As for alternative methods, i have found a dozen+ via google; since you don't like my recommendation you can pick one your self :-)

[dagon]

Ethan22;10983109 wrote:

I'd rather use the recommended, also I can't use it because it does the same thing this does, thanks a lot. So there is something else in this

mysql_query("SELECT id FROM business WHERE id>=1 ORDER BY $rand LIMIT 5")OR die(': ' . mysql_error()); 

that is missing.

i said
order by RAND()
not
ORDER BY $rand

[Ethan22]

You like to use rand() but many others do not and told me not to in 'select' so I should be able to follow several other good coders also recommendations if I choose. I know you said that, but my posts aren't being read. And rand() does all the same thing that did. Thank you very much.

I'd rather use the recommended, also I can't use it because it does the same thing this does, thanks a lot. So there is something else in this

mysql_query("SELECT id FROM business WHERE id>=1 ORDER BY $rand LIMIT 5")OR die(': ' . mysql_error());

that is missing.

[dagon]

Ethan22;10983112 wrote:

You like to use rand()

i like to use what's a appropriate for the situation, in this case it depends on the data set size, which i asked you for, but you haven't answered

Ethan22;10983112 wrote:

but many others do not and told me not to in 'select' so I should be able to follow several other good coders also recommendations if I choose.

you can do what ever you like.

Ethan22;10983112 wrote:

I know you said that, but my posts aren't being read. And rand() does all the same thing that did.

that is simply not possible, unless there is more information your not telling us

Ethan22;10983112 wrote:

Thank you very much.

your welcome

Ethan22;10983112 wrote:

I'd rather use the recommended, .

recommended by who? what criteria are you using to judge the recommendations, there are many web sites recommending things that are clearly wrong

Ethan22;10983112 wrote:

also I can't use it because it does the same thing this does, thanks a lot. So there is something else in this

that is missing

as above, that is simply not possible.

[Ethan22]

Thank you, but the word around is good programmers are getting abusive to others coming to programming forums for help in the last few years, when they treated others right before. Things are way over the top many posts ago should have been enough to state my point. You even said yourself problems with bigger tables, and many others recommended using other things instead and most know mt_rand does things randomly a different way and is faster and is approved of fine by the php manual as well.

http://www.google.com/#sclient=psy&hl=en&rlz=1R2ADRA_enUS401&source=hp&q=problem+large+tables+ORDER+BY+rand()&aq=&aqi=&aql=&oq=&pbx=1&bav=on.2,or.r_gc.r_pw.&fp=18775b43b0670ef8&biw=1253&bih=776

So I'm not going to say the same thing again after many times. Please some others tell me what the fix is for getting 5 id rows out of a table randomly instead. Some nice programmers that want to be helpful and not abusive to people. Thanks a lot you guys.

[dagon]

why are you asking again when the link you posted has the exact code you want? First one oh and 2nd one ignore 3 its MS, but if you add mysql to the query you would eliminate the irrelevant.

And in #6 i suggest you go use google as you didn't like my answer.

[Ethan22]

I did post a google link to your last question, and what I was saying. Other good programmers should reply to show they don't support other programmers, treating others wrong who come to programming forums for some help. Dagon don't reply to any more of these posts about this like I asked before, I wasn't supposed to say that again.

Ramjett said elsewhere this is probably fastest way and knows rand() doesn't always work right in select also under certain circumstances:

$findlastid = mysql_query("SELECT id FROM table ORDER BY id DESC LIMIT 1");
$lastid = mysql_result($findlastid,1);
$rand = mt_rand(1,$lastid);

Same question as original though, I still need to know why this isn't working, my very first question I asked. I'll ask it all again:
if(!isset($COOKIE["a"])){//Check if cookie set, if not set to 1.
setcookie("a", 1, time()+606024*730);}
if(isset($COOKIE["a"])){
$a=$_COOKIE["a"];}

$findlastid = mysql_query("SELECT id FROM business ORDER BY id DESC LIMIT 1"); 
$lastid = mysql_result($findlastid,1); 
$rand = mt_rand(1,$lastid);
mysql_query("SELECT id FROM business WHERE id>=1 ORDER BY $rand LIMIT 5")OR die(': ' . mysql_error()); 
 

This returns 5 id rows at a time 3,4,5,6,7,8, or 13,14,15,16,17 etc. on each refresh. I need to know how to mix up 'the' 5 rows at a time randomly?
So instead the 5 rows at a time could be any id rows taken that are all random instead. Like 19, 2, 9, 14, 7 etc. instead. If it's the order by, I don't know how to remove that to write it right, or something else. Please some other programmers please let me know thank you very much for your help.

[bradgrafelman]

Ethan22;10983116 wrote:

Thank you, but the word around is good programmers are getting abusive to others coming to programming forums for help in the last few years, when they treated others right before.

I (regrettably) agree with everything except for the fact that you called them "good" programmers. 😉

@: You've made your case (several times over). It's clear that Ethan22 is looking for alternatives to your point of view. If you have none, then move along.

[sneakyimp]

Take a moment and consider this query:

SELECT id FROM business WHERE id>=1 ORDER BY 42 LIMIT 5

"order by 42" doesn't accomplish anything in terms of ordering because it is a constant and in no way distinguishes between the different rows in your database. I find it hard to believe that it's even valid SQL but I suppose it is. I believe the result will be that your rows are returned in the order in which they are found originally entered in your db.

• ORDER BY RAND() would work for small data sets just fine. What exactly a 'small' data set it would depend on your server's capabilities and other factors. Anything under 10,000 records and you can probably get away with it under most circumstances.
• If your solution MUST work for large data sets, then you'll need a different approach. The best one I've heard is to
  a) make sure your id values are a contiguous set of integers from MIN to MAX.
  
  b) using PHP, generate 5 random integers A,B,C,D,E between MIN and MAX
  c) SELECT * FROM table WHERE id IN (A,B,C,D,E)

If your ids are not contiguous (meaning there are gaps) then you have to set up some kind of looping structure to generate random numbers between MIN and MAX repeatedly and try to fetch them until you have five results. It's pretty ugly, but complex code is necessary sometimes for performance.

[Ethan22]

Thank you very much for looking at it.

$findlastid = mysql_query("SELECT id FROM business ORDER BY id DESC LIMIT 1"); 

It is too big of a table. This first line was recommended by many for a big table because it takes a much shorter time for the code to find the last id row, then to search through a whole lot of them to get the total number of rows in the table instead. Why finding that last id was done that way.

mysql_query("SELECT id FROM business WHERE id>=1 ORDER BY $rand LIMIT 5")OR die(': ' . mysql_error()); 

I know something is wrong with the last query why I was trying to get help to figure out where the hitch is. Are you sure is it is in calling $rand like that though because many recommeded using it that way. MANY said use something similar to that instead of rand() there are all sorts of saying just that in searches as well, under one of the links I gave in this, and others 'don't use rand()' etc.

When you mentioned order by $rand, doesn't seems like it is saying, order by 42. To others it was reading order things by whatever $rand says to do so order by things 'randomly'. Do the number randomly order by the rows being pulled random and randomly. Order things by whatever $rand wants don't think is anything uncommon see it done all the time a $rand variable of some kind used instead of rand() because of these type of situations and others. And also using other random variables instead of rand() is a very common thing done.

The $rand is working it's just picking the random row to show 5 after it.

So the question is how to also make those 5 picked to display randomly as well.

So I don't know if the words 'order by' is what the hitch is I thought someone else would know I couldn't find it. Maybe 'order by' shouldn't be used right before $rand in this query. Or something else is wrong with this last query no one has spotted yet

mysql_query("SELECT id FROM business WHERE id>=1 ORDER BY $rand LIMIT 5")OR die(': ' . mysql_error()); 

I've looked at it a 100 times and can't spot it. Here's following how it still all is now:

---

if(!isset($COOKIE["a"])){//Check if cookie set, if not set to 1.
setcookie("a", 1, time()+606024*730);}
if(isset($COOKIE["a"])){
$a=$_COOKIE["a"];}

$findlastid = mysql_query("SELECT id FROM business ORDER BY id DESC LIMIT 1"); 
$lastid = mysql_result($findlastid,1); 
$rand = mt_rand(1,$lastid); 
mysql_query("SELECT id FROM business WHERE id>=1 ORDER BY $rand LIMIT 5")OR die(': ' . mysql_error()); 

This returns 5 id rows at a time 3,4,5,6,7,8, or 13,14,15,16,17 etc. on each refresh.
I need to know how to mix up 'the' 5 rows at a time randomly?
So instead the 5 rows at a time could be any id rows taken that are all random instead. Like 19, 2, 9, 14, 7 etc. instead. If it's the order by, I don't know how to remove that to write it right, or something else. Please let me know thank you.

The $rand is working it's just picking the random row to show 5 after it.

So the question is how to also make those 5 picked to display randomly as well.

The first rand worked, how to make those 5 numbers random as well. Maybe there is a shorter way without adding another line on to make those 5 random too, or not.

[sneakyimp]

Try echoing your query. You will see that $rand gets replaced by a constant and this is going to accomplish nothing. Obviously, you've tried it and it doesn't work. I'm not sure where you've been told that such an approach would work, but I think something is getting lost in translation between you and them -- or they are incorrect.

You don't have to fetch all the records in your database to find out the MIN and MAX id values. You could instead use queries like this:

SELECT MIN(id) FROM table;
SELECT MAX(id) FROM table;

[Ethan22]

Thanks a lot I just said the other was recommended because finding the last id was supposed to be faster in a real large table than finding the 'max' id, or the max id might do the same exact thing finding the last id too so thanks. But that's not the problem I'm having, problem I'm having is the last I just said.

[sneakyimp]

The "last" id in a database table could have several meanings: the highest id value, the record that happens to be the last one in the data file (which may not have the highest id at all).

I'd be shocked if this query:

SELECT id FROM business ORDER BY id DESC LIMIT 1

were faster than this one:

SELECT MAX(id) FROM business;

One thing to keep in mind when doing all this is that MySQL has a query cache. The basic idea of the query cache is that MySQL will cache the results of a particular query in memory until your database table gets updated. Obviously, it cannot cache very large queries if the query cache is not big enough.

The problem that you are having is that your approach does not work. I believe that it will never work like you want it to. Your other problem is that you keep insisting that it should work which, as far as I know, is simply incorrect. I'll say it again. This approach will not work:

$rand = mt_rand(1,$lastid);
mysql_query("SELECT id FROM business WHERE id>=1 ORDER BY $rand LIMIT 5")OR die(': ' . mysql_error()); 

Reason 1: It does not result in valid SQL -- at least on my Ubuntu machine running php 5.3.5.

Reason 2: All this PHP code will do is to create an SQL query with a constant in place of $rand. It could be 42 or it could be 999,942 but it is a constant value. I believe for that reason that you will not get any truly random ordering of your records at all

Reason 3: Suppose this line accidentally happened to generate the same random number twice:

$rand = mt_rand(1,$lastid);

In that case, MySQL would likely cache the result the first time and cough up the exact same results the second time the query runs even though there are about 120 different possibilities for ordering the same 5 random numbers.

You may find this code educational:

// get lowest id value
$min_sql = "SELECT MIN(id) AS minval FROM business";
$min_result = mysql_query($min_sql)
  or die('min query failed:' . mysql_error());
$min_row = mysql_fetch_assoc($min_result);
$min = $min_row['minval'];
echo "min is $min<br>";

// get highest id value
$max_sql = "SELECT MAX(id) AS maxval FROM business";
$max_result = mysql_query($max_sql)
  or die('max query failed:' . mysql_error());
$max_row = mysql_fetch_assoc($max_result);
$max = $max_row['maxval'];
echo "max is $max<br>";

// the number of items we'd like to choose
define("NUMBER_OF_RANDOMS_TO_PICK", 5);

// sanity check A
if (($max - $min) < (NUMBER_OF_RANDOMS_TO_PICK - 1)) die("$min and $max are too close to pick " . NUMBER_OF_RANDOMS_TO_PICK . " numbers between them");
// sanity check B
if ($min < 1) die("$min is less than 1");


// pick 5 ids at random between $min and $max
// note that this is not very efficient if NUMBER_OF_RANDOMS_TO_PICK is large relative to ($max - $min)
$picks = array();
while(sizeof($picks) < NUMBER_OF_RANDOMS_TO_PICK) {
  $rand = mt_rand($min, $max);
  if (!in_array($rand, $picks)) {
    $picks[] = $rand;
  }
}

$sql = "SELECT id FROM business WHERE id IN (" . implode(",", $picks) . ")";
echo "SQL Used: $sql<br>";
$result = mysql_query($sql)
  or die("Query failed:" . mysql_error());
$values = array();
while($row = mysql_fetch_assoc($result)) {
  $values[] = $row['id'];
}
echo "Values Returned: " . implode(',', $values) . "<br>";

[Ethan22]

That isn't true in any way, I said the whole time and in most of my own posts that I knew the code wasn't working 'that's what got me to come here and why I said I was here.'

Without the update cache situation but that was good info,
Does

SELECT MAX(id) FROM business

get the last autoincremented id row??? Because that's the id row I need. That's not what the big problem is and problem trying to find some answers to though.

So hopefully someone will look at my last query line and see if they can spot my problem. Someone will look at my last line and see if they can spot my problem. It's alright if the rest is just alright, I'm here for help with the last query and said what the last query is doing and not doing. I said many time I know the last query line isn't doing what I need and hoping someone can spot my problem.

If I have to have another random line that picks the random from each 5 or if there is a simpler way? Thanks a lot. Here it all is again:

---

if(!isset($COOKIE["a"])){//Check if cookie set, if not set to 1.
setcookie("a", 1, time()+606024*730);}
if(isset($COOKIE["a"])){
$a=$_COOKIE["a"];}

$findlastid = mysql_query("SELECT MAX(id) FROM business") OR die(': ' . mysql_error());
$lastid = mysql_result($findlastid,1);
$rand = mt_rand(1,$lastid);

mysql_query("SELECT id FROM business WHERE id>=1 ORDER BY $rand LIMIT 5")OR die(': ' . mysql_error()); 

This returns 5 id rows at a time 3,4,5,6,7,8, or 13,14,15,16,17 etc. on each refresh. I need to know how to mix up 'the' 5 rows at a time randomly?
So instead the 5 rows at a time could be any id rows taken that are all random instead. Like 19, 2, 9, 14, 7 etc. instead. If it's the order by, I don't know how to remove that to write it right, or something else. Please some other programmers please let me know thank you very much for your help.

The $rand is working it's just picking the random row to show 5 then in order after the random row it grabbed.

How to also make those 5 rows that are picked after it, how to get those 5 rows that show on each refresh to display randomly too.

The first rand worked, how to make those 5 numbers random as well. Maybe there is a shorter way without adding another line on to make those 5 random too, or not. If I have to have another random line that picks the random from each 5 or if there is a simpler way?

I'm still asking the same thing I needed to know at the very start. Thanks a lot.

[sneakyimp]

Good luck finding your answer. I've told you all I can.