How to select only 1 row from a query result? like the 4th row

Question

I have this code and am looking to make a sort of "quotes area" on my website. I want it to select 3 random quotes from my database everytime. I know about the function rand(min,max), but how do I use it to select that exact row from the query?

require 'DB.php';
$link = mysql_connect('localhost', 'root', '430123');
$db = mysql_select_db('bakery', $link);
$result = mysql_query('SELECT * FROM quotes');

I could use a for loop and use mysql_fetch_row() for how much the number from rand() was, but that could be a bit inefficient maybe?

Please, [don't use `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php), They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Learn about [prepared statements](http://en.wikipedia.org/wiki/Prepared_statement) instead, and use [PDO](http://us1.php.net/pdo) or [MySQLi](http://us1.php.net/mysqli). [This article](http://php.net/manual/en/mysqlinfo.api.choosing.php) will help you decide. — Jay Blanchard, Nov 04 '14 at 14:52

score 4 · Answer 1 · answered Nov 04 '14 at 14:52

4

To get only the 4th row use limit offset, number_of_records

SELECT * FROM quotes
order by rand()
limit 3, 1

answered Nov 04 '14 at 14:52

juergen d

201,996
37
293
362

With RAND()? I'm confused. – Strawberry Nov 04 '14 at 14:54
1

@Strawberry ordering by rand() assigns each row a random number, and MySQL will then order those in ascending order, randomising the order the results are returned. In this case, it would select everything in the quotes table, order it randomly and then return the 4th result in the set – gabe3886 Nov 04 '14 at 14:57
But it's RAND, so what's the difference? This is astrology. – Strawberry Nov 04 '14 at 15:30

score 0 · Answer 2 · answered Nov 04 '14 at 14:53

0

Well, you just could select all quotes and then use function array_rand() to select your random quotes

answered Nov 04 '14 at 14:53

EdvinasJ

61
7

score 0 · Answer 3 · answered Nov 04 '14 at 14:55

0

You can use ORDER BY rand().

eg.

$result = mysql_query('SELECT * FROM quotes ORDER BY rand() limit 3');

answered Nov 04 '14 at 14:55

kasim badami

196
1
4
14

score 0 · Answer 4 · answered Nov 04 '14 at 15:45

Here's a function that returns an array of $count elements given a source array. You have to add support for avoiding duplicate quotes, but this does what you asked.

<?php
    require 'DB.php';    
    $link = mysql_connect('localhost', 'root', '430123');
    $db = mysql_select_db('bakery', $link);
    $result = mysql_query('SELECT * FROM quotes');
    $row = mysql_fetch_array($result);
    get_quotes($row,3);

   function get_quotes($source,$count) {
        $quotes = array();
        for ($i = 0;$i<$count;$i++) {
            $seed = rand(0,sizeof($source)-1);
            $quotes[] = $source[$seed];
        }
        return $quotes;        
    }

Maybe doing it in a query would be better, but I don't think that's what you asked for.

How to select only 1 row from a query result? like the 4th row

4 Answers4