One of those issues where I have a feeling somebody's excellent suggestion will make me wonder how I could have not figured it out myself...
I'm creating a database full of records representing reported cases. A major means of sorting and accessing the records will be by print reporter citation, and I want to have "Next Case" and "Previous Case" links in the record display. The nature of the workflow probably makes it unreasonable to expect that these records will be entered in reporter citation sequence, so auto-incrementing record numbers won't do the job of performing that particular sort. So consider the following three-in-a-row reported cases:
301 Va. 28
301 Va. 28 (a second case starting on the same page as the one before)
301 Va. 41
It occurs to me to script the creation of numeric field entries representing these citations. In this example, maybe 301028000, 301028001, and 301041000, respectively (which should work fine since these reporter volumes are nowhere near 1000 pages). That would give me my sort. The short question is: If I'm displaying the second record above, how do I phrase MySQL queries to retrieve the "one before" and the "one after?"
I figure the work-around would be to require the user to pre-enter a whole volume's citations in correct order so that record numbers will work, but there are reasons that approach may not be doable.
University of Richmond School of Law