Just starting out with mySql / php ... hope this is a simple question
and my description is understandable:
I want to present table data from one row in one webpage with links to
the previous and next row/page. The way I propose to do this is to
have a field called SortOrder with values assigned incrementally so
that each webpage 'knows' that the next page should have
SortOrder_next == SortOrder_this + 1
and the previous page
SortOrder_prev == SortOrder_this - 1
When a new row is added to the table, it most likely has to fit in the
middle not the end, so the table has to be re-ordered and new values
assigned for SortOrder. I have tried this:
SET @n = 0 ;
UPDATE tableA
SET SortOrder =@n, @n = @n +1
WHERE ready =1 AND id =22
ORDER BY date ;
but mySql says
"Check the manual ... for the right syntax to use near '@n = @n +1 ...
' "
so I guess this is not the way to increment my variable.
I feel this must be quite a common situation but have searched in vain
for any examples.
.... or perhaps there is a simpler way which avoids the SortOrder field
altogether? Can one instruct mySql to return "the row after this one
when ordered by..." ?
thanks
Alan
