Get the data from mysql database

[Anon]

Dear all,

I want to get the first 4 row of data in php in the following,

$sql="select id,num
from data where num >0 order by id ASC limit 4";

For example, the table "data" contains 10 rows of data, the data (id,num) in the table are shown below.
(1,2), (1,3),(1,1),
(1,4), (1,5),(1,4),
(1,4), (1,6),(1,1),
(1,8)

if the data is arranged in ascending order,
(1,1),(1,1),(1,2),(1,3),(1,4),(1,4),(1,4),(1,5),(1,6),(1,8)

This means the fourth data is (1,4) which contain three elements. Then I want to select all the data with (1,4)s. All the data we select is

(1,1),(1,1),(1,2),(1,3),(1,4),(1,4),(1,4)

Please give me some help on the sql command, thanks

Simon

[edwardsbc]

I think it will take two queries. First, change your first query to end in '...ORDER BY id,num ASC limit 4'. Going by ID isn't enough.

Then fetch the result, rembering the last record (I'm assuming odbc, use the database functions appropriate to your database):

while (odbc_fetch_row($x)) {
$tmpid=odbc_result("id");
$tmpnum=odbc_result("num");
}

tmpid and tmpnum now contain the last results. Then execute a new query to get all that match or less(more):

$sql="SELECT id,num FROM data WHERE id <= $tmpid AND num <= $tmpnum ORDER BY id,num ASC";

This will select the first seven records of your example dataset:

(1,1),(1,1),(1,2),(1,3),(1,4),(1,4),(1,4)

If you just want the (1,4)'s, change the <= to =.