Use Single Row Query with MySQL and PHP

Question

Hey there

say I have this

$result =  mysql_query('SELECT views FROM post ORDER BY views ASC');

and I want to use the value at index 30 I assumed I would use

mysql_data_seek($result, 30);
$useableResult = mysql_fetch_row($result);
echo $useableResult . '<br/>';

But that is returning my whole table

What have I got wrong?

Edit: Woops, I actually have

mysql_data_seek($result, 30);
while($row = mysql_fetch_row($result)){
    echo $row['views'] . '<br/>';
}

Answer 1

Simply use an SQL WHERE clause.

$result = mysql_query('SELECT views FROM post WHERE ID=30')

If you don't want to go by ID but instead want the 30th item that would be returned you can use a LIMIT min, max:

$result = mysql_query('SELECT views FROM post LIMIT 30, 1')

In both cases your ORDER BY commands become unnecessary.

Here is a good usage example of the LIMIT command for doing paging on large record sets.

Answer 2

Are you sure there are 30 elements in $result? You might want to check to see if 30 > mysql_num_rows().

Answer 3

Your first example would actually do what you want, but be very expensive. The second is selecting the entire table, moving to the 30th row of the result, and then looping through all the results from then onwards.

You should instead do the following, which will only return one row and be a lot faster:

$result =  mysql_query('SELECT views FROM post ORDER BY views ASC LIMIT 30,1');

Note that Soviut's explanation of LIMIT is not quite correct - it's (offset, number of rows) rather than (min, max).