Re: FN-FORUM: Choosing the next record in MySQL DB

date posted 14th May 2008 13:03

'It's flawed in that you need to work around what happens when you're at
one end of the list.'

The workaround code would be simpler that firing off another query and
quicker. The query does exactly what three queries would.

When going to select the 'next' row on the last record you would do
'select * from news where newsID > $id LIMIT 1' (replacing the nasty
nasty bad 'select *') and would come up with zero returned rows, which
you would already have with the UNION query.

On Wed, May 14, 2008 at 2:22 PM, Dom Latter [EMAIL REMOVED] wrote:
>
> On Wednesday 14 May 2008 14:25:12 Graham Stark wrote:
>
> > I haven't been following this one, but given what I understand the
> > problem to be, Tom's solution looks quite elegant. The problem is that
>
> It's flawed in that you need to work around what happens when you're at
> one end of the list.
>
>
> > there's no simple way to select the id for the next page since the ids
>
> There is, there's just no simple way of getting it in the *same* select.
>
>
> > are not contiguous (so you can't just say "select $id+1"): Tom solves
> > that nicely. Another approach might be:
> >
> > select * from news where id = $id or id = (select max(id) from id1 where
> > id < $id ) or id = (select min(id) from id1 where id > $id );
>
> Same problem as Tom's, AFAICS. You need to write extra code to work out
> what the first ID refers to if you only get two results back.
>
> You're also selecting roughly three times as much data as you actually
> need for the page.
>
>
> > The "select *" seems fair enough to me in this context since we don't
> > actually know what's in the table, or which fields are wanted.
>
> Well, we can guess at likely fields if we're writing pseudo / example code.
> "select *" is a nasty evil habit and should be discouraged at all times.
>
> I really don't see what's wrong with three separate SQL queries that
> return the three separate sets of information that are required.
>
> Once we've thrashed this out we can move on to whether you should
> be using IDs to perform date ordering in the first place.
>
>
>
> --
> Freelancers, contractors earn more with Prosperity4
> Call 0870 870 4414 or visit www.prosperity4.com
> and benefit from Inland Revenue approved expenses today.
>
> To advertise here: http://www.freelancers.net/advertising.html
>
>