Right, I finally managed to get this problem sorted (even if in a roundabout way). I will share more detailed explanation if interested however here is what I did in a nutshell. Under Search and List form, where my content was linked to under the Item view, I edited the link properties to include a custom variable:
myvar=$cck->getValue('custom id value'). This ID will be used in the ordering of the items, and also is unique to identify the item.
Now when we call the link to the content page, an additional GET request value will be in the URL, &myvar=<content>. We can use this to build our query..
Using the custom php code in beforerender field I took this value using $_GET[''];, and from this value was able to construct my SQL query to locate the next and previous object in SQL query.
I then used JRoute::_() to build my prev/next button links, to retain the SEF URLs for SEO reasons.
One of the downsides to this workaround is a new GET variable must exist under the page in order for it to work. This is because we cannot seem to be able to get content value using beforerender in the content view i.e: $cck->getValue() (and before and after store do not support the content view as per documentation). Maybe this is by design, a bug or? I'm not too sure.
Another problem with this method is accessing the item directly without the GET data, maybe even the item from a search engine, you won't get the prev/next buttons unless you access the same or another item from the search and list object. but for now it will do the job for my purpose.
Thanks everyone for their help here,
Nick