mysql: result starting on row 10

Steffel

how does a mysql-query look like if i only wanna have the rows starting at line 11?

i only know it from oracle like this:
SELECT * FROM table WHERE ROWNUM<=10

but this doesnt work with mysql...
thx for help
Steffel

hand

SELECT * FROM table LIMIT 10, 9999

Steffel

thats the only way?
i think its not really proper programming, even if its working...

hand

Limit is a mySQL Feature

SELECT [STRAIGHT_JOIN]
       [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
       [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS] [HIGH_PRIORITY]
       [DISTINCT | DISTINCTROW | ALL]
    select_expression,...
    [INTO {OUTFILE | DUMPFILE} 'file_name' export_options]
    [FROM table_references
      [WHERE where_definition]
      [GROUP BY {unsigned_integer | col_name | formula} [ASC | DESC], ...
      [HAVING where_definition]
      [ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC] ,...]
      [b][LIMIT [offset,] rows][/b]
      [PROCEDURE procedure_name]
      [FOR UPDATE | LOCK IN SHARE MODE]]

http://www.mysql.com/doc/en/SELECT.html

Oracles ROWNUM and mySQLs LIMIT, both aren't SQL

Steffel

yeah, i know LIMIT, but normally its used to LIMIT your result to a special row number
but i dont know how large my result will be because this query will be for many different tables
so i could take something like limit 10, 1000000
but thats what i said: its not really proper limiting the number of rows if i just wanna get all after the first ten

vitch2002

I think the thing he is saying doesn't seem proper is that you have to hardcode the 9999 in. It seems abit suspect to me too - who knows how many rows you are retrieving but just putting a high number in seems inelegant....
Another option would be to use

mysql_data_seek($resultID, 10);

before doing any output. The problem is I think that the data will still be passed from mysql to PHP which could slow your app down if it's getting loads of hits...

hand

Not the best solution, but an other possible solution ...

<?php
$offset = 10;
$query  = "SELECT count(*) as RECS FROM table";
$result = mysql_query($Query)
	or print "&gt;&gt;&gt; MySQL-Error: ".mysql_errno()." -&gt; ".mysql_error()."<br>\n";

while($row = mysql_fetch_array($result)){
	$recs = $row[RECS];
}

$recs = $recs - $offset;

$query  = "SELECT * FROM table LIMIT $offset , $recs";
$result = mysql_query($Query)
    or print "&gt;&gt;&gt; MySQL-Error: ".mysql_errno()." -&gt; ".mysql_error()."<br>\n";

while($row = mysql_fetch_array($result)){
	// ...
}
?>

Steffel

jap, thx vitch2002, thats the point
so in this case it would be ok to work with your mysql_data_seek function but what if i want all result from the 1000 row on?
then i'd get much more data than i need...

is there really no proper way to get only the rows i wanna have?