471,108 Members | 1,298 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,108 software developers and data experts.

update increment

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
Jul 20 '05 #1
0 2568

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

19 posts views Thread by Westcoast Sheri | last post: by
6 posts views Thread by Paul Eden | last post: by
6 posts views Thread by Tom Allison | last post: by
1 post views Thread by Hexman | last post: by
5 posts views Thread by logina1 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.