bad reputation for script

[stuckwithcode]

I am trying to randomly order a mysql table and then echo the results.  Why is it that everything I read about order by rand() says that It is too slow and not to use it.

 

Can any help explain why or maybe offer up some other suggestions.  The table will have a lot of rows eventually

 

 

Thanks

[JonnoTheDev]

This quite clearly explains why using RAND() for large result sets is a bad idea.

http://www.titov.net/2005/09/21/do-not-use-order-by-rand-or-how-to-get-random-rows-from-table/

Using your script to randomise the data is much more efficient. For instance, if you have 100 records in your table and the primary keys are ordered correctly 1 - 100 then I can use php to give me x numbers between 1 and 100. So if I wanted 5 random records from my table:

<?php
$randoms = array();
$required = 5;
// get me 5 numbers between 1 - 100
for($x = 0; $x < $required; $x++) {
// generate number
$number = rand(1,100);
// have we already used this number?
if(!in_array($number,$randoms)) {
	$randoms[] = $number;
}
// yes, try again
else {
	$x--;	
}
}
// run query
$result = mysql_query("SELECT * FROM tablename WHERE primarykey IN(".implode(",",$randoms).")");
?>

[stuckwithcode]

what does the IN mean after primarykey, I half understand the code

 

thanks

[JonnoTheDev]

It means give me any record where the primary key value is one of the supplied values i.e

If this is your table, lets call it 'users':

 

id | name

=======

1 Joe

2 Neil

3 Adam

 

Then the following would give me the first and last record

SELECT name FROM users WHERE id IN(1,3)

[stuckwithcode]

okay thanks a lot for the help, just to clarify, what do you consider to be a large table?

 

how many rows?

 

THanks

[stuckwithcode]

also could you check your script I cant get it to randomise a table and print it, im also using the table you showed in your example

[JonnoTheDev]

okay thanks a lot for the help, just to clarify, what do you consider to be a large table?

how many rows?

Doesn't really matter about the number of rows. More of a concern is what the table is being used for and the number of queries that run on it at a given time.

also could you check your script I cant get it to randomise a table and print it, im also using the table you showed in your example

The script is not a complete working example. The code prior to the database query is valid and produces an array of random numbers between 1 and 100 however you will have to add the parts to connect to your database, run the query and loop over the results to print them.

You should be able to do this yourself.

[stuckwithcode]

Right this I can do, thanks for all the help.  One last thing I under stand the IN() thing but cannot find any guides on it on google etc. can you provide any links, thanks

[JonnoTheDev]

http://www.webdevelopersnotes.com/tutorials/sql/tutorial_mysql_in_and_between.php3

[stuckwithcode]

thanks, why can i not put a variable in IN(), the code below does not work so i just put the variable in after doing the implode e.g.

 

$result = mysql_query("SELECT * FROM tablename WHERE primarykey IN(".implode(",",$randoms).")");

 

$randomslist = implode(",", $randoms);

 

 

$result = mysql_query("SELECT * FROM tablename WHERE primarykey IN($randomslist)");

 

Thanks, Im nearly there

[stuckwithcode]

The last post I typed was untrue the variable does work but if i have IN(101,110,102)

 

it prints rows 101,102,110 when i want the order to be 101,110,102

 

any ideas

[JonnoTheDev]

Use ORDER BY in your query

SELECT * FROM tablename WHERE id IN(101,110,102) ORDER BY id ASC

 

[ajlisowski]

The problem is he wants it to NOT order by...which, off the top of my head I have no idea how to accomplish that...

[premiso]

The only way to do that, afaik, is to handle it on the php end:

 

$result = mysql_query("SELECT * FROM tablename WHERE primarykey IN(".implode(",",$randoms).")");
while ($row = mysql_fetch_assoc($result)) {
    $rows[$row['primarykey']] = $row;
}

foreach ($randoms as $key) {
    print_r($rows[$key]);
}

 

Something like that would work.