Select nth record from a SQL result set in php

Question

I run a query and get a result set. I need to get the nth record from this result set. Any ideas how I can do this?

I cannot select the nth record from the database directly coz I need to filter it and don't know how many records there are in the result set. So I cannot be certain which number to pass to the mysql_result method i.e.

Based on certain conditions, get a few rows from a table
From these rows, select the nth row (the number n is not fixed. It depends on the number of records returned)

The basic idea is to get all results based on a set condition and get a random result from these.

Any ideas? Thanks.

So you can't use an offset using `LIMIT` ? Why not just get the records, filter, loop until you reach N then that's it. — Gary Green, May 13 '11 at 07:32
no. as i said, n is not fixed. it is calculated based on the number of results returned by the query. — lostInTransit, May 13 '11 at 07:33
How as a human are you going to determine the value of "n".. if you can explain that, perhaps we can help you turn it into code better — BugFinder, May 13 '11 at 07:33
maybe something like this: http://stackoverflow.com/questions/685871/mysql-data-seek-pdo-equivalent or http://php.net/manual/en/function.mysql-data-seek.php ? — Yoshi, May 13 '11 at 07:34
In which case just `SELECT COUNT(*) as record_count` then you can use record_count to decide what N should be, then when you reach that number in the loop, that's your baby. This question sounds too simple, it's probably missing something. — Gary Green, May 13 '11 at 07:35

score 0 · Accepted Answer · answered May 13 '11 at 07:34

0

Your question seems unclear. However here's a guess:

You want to select the record in the middle:

$count = mysql_num_rows($result);
$middle_name = mysql_result($result, intval($count/2), 'name');

Besides that, you can also do that if you have really less records:

$rs = array();
while ($row = mysql_fetch_assoc($result)){
     $rs[] = $row;
}

and then you can use $rs[N-1] to reach Nth record.

Read mysql_data_seek from PHP Manual if you will fetch just one record.

answered May 13 '11 at 07:34

ahmet alp balkan

42,679
38
138
214

Can't use mysql_result as I want to get the entire row. Thanks for the mysql_data_seek hint. – lostInTransit May 13 '11 at 07:39
You can do `mysql_data_seek` and then my `mysql_fetch_assoc` solution, but in `while` loop, put `break;` just after `$rs[]...` statement so that your `$rs[0]` will be your item. You can delete `$rs=array()` and `$rs[]...` statement and use just `$row`. It will be the key-value associative array of your record. – ahmet alp balkan May 13 '11 at 07:42

Yoshi · Answer 2 · 2011-05-13T07:46:24.323

0

The basic idea is to get all results based on a set condition and get a random result from these.

SELECT ... ORDER BY RAND() LIMIT 1

I know this is not best practice, but as the given information is rather sparse, this could be starting point for further reading. And to be honest, in an small enough application, this is often the easiest solution.

edited May 13 '11 at 07:46

answered May 13 '11 at 07:37

Yoshi

54,081
14
89
103

This is terrible. It doesn't seem answer the (granted, overly vague) question, and is one of the SQL-don'ts performancewise. – Konerak May 13 '11 at 07:41
Correct, but as there is not much information in the question, this could very well be a starting point for further reading. – Yoshi May 13 '11 at 07:43

Select nth record from a SQL result set in php

2 Answers2