Selecting rows

25lez25

Newbie question

If your mysql table has rows with id 1 to 300, is there a way to do a Select of any group of rows such as e.g. rows with ids 34-49 ? I have seen the Asc and Desc which seems to allow selecting the rows either beginning at 1 or at 300.

Thanks

laserlight

You could select the 34th row, and 15 rows after by using something like:

SELECT * FROM tablename ORDER BY id LIMIT 33, 15;

a basic example would be selecting the first 10 rows,
SELECT * FROM tablename LIMIT 0, 10;

shareaweb

Although LIMIT is great for the total number of tuples (records) in your database (LIMIT starting point, number of rows) you may need to adjust your SQL in some situations also, especially if your database is not defined well and has update or delete anomolies..

SELECT * FROM mytable WHERE id BETWEEN '$lowid' AND '$highid'

Have phun 😉

25lez25

Thanks

In the line.....

SELECT * FROM mytable WHERE id BETWEEN '$lowid' AND '$highid'

if you want say rows 34-49, do you just insert those numbers (34,49) directly into the SELECT line ('34', '49', in place of $lowid, $highid or how is this done?

shareaweb

Yup.. you're a natural.. hehe..

SELECT * FROM mytable WHERE id BETWEEN '34' AND '49';

or better -

$lowid = 34;
$highid = 49;
SELECT * FROM mytable WHERE id BETWEEN '$lowid' AND '$highid';

This way you can change the lowid or highid values later on without affecting the query.. say from a querystring...

$lowid = $GET['l'];
$highid = $GET['h'];
SELECT * FROM mytable WHERE id BETWEEN '$lowid' AND '$highid';

now you can call your page with the low and high in the querystring...

http://mysite/mypage.php?l=34&h=49
-or-
http://mysite/mypage.php?h=1000&l=0

get it? dynamiticity will lead to reuse 😉