problem with LIMIT

toxic_brain

i have a table with userids, and trying to display them page by page by using LIMIT while yje admin clicks on the 'Next' and 'Previous' links above the page.
let me explain...
i want to show userids in alphabetical order.
for eg: userids starting with z should be in last pages.
i have 100 users and 10 userid/page
but when i try with LIMIT 40,10
the LIMIT clause filter the 40 to 50 records in order they are inserted into the table.and sort that 10 records in alphabetical order..

pls help...

(i know this is too lengthy, sorry for that)

Sxooter

You are making some grave mistakes here. In SQL, the order of inserts means NOTHING if you aren't assigning some kind of time stamp or an autoincrementing field, then there is no guarantee of order. It is entirely possible that a future update to the MySQL's table handlers could break anything you manage to get to work now.

So, you need to use some kind of date / time field or an auto increment field, then use that to order by along with limit and offset to get the portion of the table you want.

Doing it the way you're trying to do it is a recipe for disaster for the poor guy who comes after you and runs a newer version of MySQL and everything breaks.

toxic_brain

i 'll xplain again
first of all,
i am NOT trying to sort on inserted order.

it should be shown sorted on userid .

for easy ,we can assume user id as numbers

-page 1-

1
2
3
4
5

-page 2-
6
7
8
9
10

-page 3-
11
12
13
14
15

but currently it is as,

-page 1-
8
9
10
11
12

-page 2-
3
4
5
6
7
page 3-
1
2
13
14
15

Sxooter

What are you doing for an order by clause? Got a whole query to show? This sounds like a sub-query to me.

toxic_brain

$from=$_GET['from'];  	" SELECT * FROM tbl_spice_user ORDER BY varUserId LIMIT $from, 5";

'Next' button will be a link to the same page but with an incremented from as,

<a href='user_sort.php?from=<?php echo $from +5?>'>Next</a>

nd 'Previous'

<a href='user_sort.php?from=<?php echo $from - 5?>'>Next</a>