MySQL list, relative to one post.

[Sandman]

Ok, we'll see if anyone here has an idea for this problem.

I have a database of texts in a blog system. The database is MySQL. The
database contains blogs for hundreds of different "bloggers" and different
categories.

So, to list a specific posters blogs in a specific category, it looks something
like this:

select id, headline from blogs where member = 1234 and category
= 'Technology' order by date desc limit 20

So far so good. That displays the 20 latest blogs that fit that, which could
return something like this:

1256 My Mac!
1034 I ordered a Mac
945 I like my Palm

And so on.

Now, my problem arises when someone searches for blog entries for this person
and finds a blog entry that is older than the 20 ones listed as most recent
blogs. Let's say I am reading "12 Maybe I'll buy a Palm", and there are fifty
entries between 12 and 945 that won't be shown.

Now, I dont' want to extend my "limit" clause to go all the way down to 12,
since that could potentially result in thousands of lines returned.

What I *DO* want to do is to have the list be relevant to the current text. So
if the limit is 5, and the asterix is the current one, different lists might
look like this:

1. 1256 My Mac!
* 2. 1034 I ordered a Mac
3. 945 I like my Palm
4. 924 Quad Mac!
5. 900 Cool stuff for christmas


And if I click the fifth item (christmas), the list shifts to:

3. 945 I like my Palm
4. 924 Quad Mac!
* 5. 900 Cool stuff for christmas
6. 845 How about that new Palm?
7. 840 Gadgets galore

And if I somehow end up reading the very first blog entry for this person:

23. 101 Don't you just hate MS?
24. 98 If I only had the money...
25. 78 Hardcore gaming... on a palm!?
26. 24 Mystified IT
* 27. 12 Maybe I'll buy a Palm

Do you see where I'm getting at?

As far as I know, I have to do this in two SQL queries - one that fetches all
posts before the current one and one that fetches all the ones after it. But do
any of you guys have a smart SQL qquery that will fetch it correctly for me in
one pass?


--
Sandman[.net]

[Joshie Surber]

Re: MySQL list, relative to one post.

> have a smart SQL qquery that will fetch it correctly for me in one pass?

If you are fairly sure that the IDs are sequential, you may try "where
id<CURRENTID+2 OR id>CURRENTID+2 (my brain isn't working logicly now...
you may need to flip the + and - signs)

If they aren't sequential, make an educated guess about how
insequential they are and grab 10 or so on each side and filter out all
but the five you want in your script. It is a lot faster to grab a lot
of stuff in one query and filter it in your script than to grab a
little stuff in two or more queries.

Hope this gives you some ideas.

[Sandman]

Re: MySQL list, relative to one post.

In article <1132113150.201 641.49730@g44g2 000cwa.googlegr oups.com>,
"Joshie Surber" <joshiesurber@g mail.com> wrote:
[color=blue][color=green]
> > have a smart SQL qquery that will fetch it correctly for me in one pass?[/color]
>
> If you are fairly sure that the IDs are sequential[/color]

Well, had you looked at my example data, you would have seen they are not. :P
[color=blue]
> If they aren't sequential, make an educated guess about how
> insequential they are and grab 10 or so on each side and filter out all
> but the five you want in your script. It is a lot faster to grab a lot
> of stuff in one query and filter it in your script than to grab a
> little stuff in two or more queries.[/color]

"lots of stuff" could mean tens of thousands of posts, so that's not a good
idea.

what I would like is something like this:

select * from blog where member = 12 and category = 'Sports' and id = 12345
limit -5, 5;







--
Sandman[.net]

[Oli Filth]

Re: MySQL list, relative to one post.

Sandman wrote:[color=blue]
>
> what I would like is something like this:
>
> select * from blog where member = 12 and category = 'Sports' and id = 12345
> limit -5, 5;[/color]

SELECT * FROM blog
WHERE ... AND id < $currentID
ORDER BY id DESC
LIMIT 5
UNION
SELECT * FROM blog
WHERE ... AND id >= $currentID
ORDER BY id
LIMIT 6

Second limit needs to be 6 to make sure the current ID is included.

--
Oli

[Sandman]

Re: MySQL list, relative to one post.

In article <1132157223.443 017.320790@z14g 2000cwz.googleg roups.com>,
"Oli Filth" <catch@olifilth .co.uk> wrote:
[color=blue]
> Sandman wrote:[color=green]
> >
> > what I would like is something like this:
> >
> > select * from blog where member = 12 and category = 'Sports' and id =
> > 12345
> > limit -5, 5;[/color]
>
> SELECT * FROM blog
> WHERE ... AND id < $currentID
> ORDER BY id DESC
> LIMIT 5
> UNION
> SELECT * FROM blog
> WHERE ... AND id >= $currentID
> ORDER BY id
> LIMIT 6
>
> Second limit needs to be 6 to make sure the current ID is included.[/color]

Whoa - that looks like it's something!

But that will actually run two queries in the SQL-server, right? But still, I
get both in the same pass. I'll try it out. Thanks!


--
Sandman[.net]