Select only 5 random rows

PHP
1

Hi,

I have some working code, but cant work out without it being over complicated on the net, how to select say only 5 rows from the database.

I only want a certain number to show.


<?php 
$r=mysql_query("select * from tbl_hotels where IdRsrt_Hot=$Other_Hot and Act_Hot=1 order by rand()");
while($q=mysql_fetch_assoc($r)){
?>
<div style="position:relative; height:135px; width:97%; margin-left:auto; margin-right:auto; margin-bottom:10px; font-size:11px; text-align:left;">
<?=$q['Nom_Hot']?><br/> <img src="/admin1212/<?=$q['Foto1_Hot']?>" width="162px" style="border:#FFFFFF solid 1px; margin-top:5px;" /><br/>
</div>
<?php } ?>

I know it doesnt work, but what im trying to get and what i had in my head was:


$r=mysql_query("select 5 * from tbl_hotels where IdRsrt_Hot=$Other_Hot and Act_Hot=1 order by rand()");
while($q=mysql_fetch_assoc($r)){

Cheers

:slight_smile:

2

LIMIT 5

3

Thank you guido2004,

Some of the ways they have answered that on other sites is crazy, I just knew it was a lot easier.

LIMIT *, brill will remember that one easily.

Cheers

4

How big is your table?
(SELECT…ORDER BY rand() is very slow when the table gets large)

5

With a limit applied it isn’t too bad.

6

Sure it is. Even with a limit, the engine has to generate a random number for every row in the table, then sort it, and THEN you can limit your results.

7

A better way when using PHP is to drop the ORDER BY and the LIMIT and instead, get the result set of the query into an array in php in the usual way then do:


$random_hotels=array_rand($hotels_list,5);

In [this thread here ](http://www.sitepoint.com/forums/mysql-182/speedy-order-rand-operation-727493.html)the efficiency of different methods of getting x number of random records is discussed.
8

Yeah, I’m mistaken. I still get lazy and use order by rand. If speed is a concern…

$db->queryAll("SELECT * FROM myTable WHERE id IN(".implode(',',array_slice(shuffle($this->quickQuery("SELECT id FROM myTable")), 0, 5).") LIMIT");

queryAll return an array of all results, quickQuery changes the return based on the result context - with a SQL statement that contains only one column it will return an array of that column.

9

shouldnt need that LIMIT on the end (cause you’ll only have 5 numbers anyway), but yeah, that’ll do it.

10

Forgot to erase the limit while I was typing it in (I was originally going with limit 5, but then realized array_slice would be faster).