|
|
Posted by Sandman on 11/15/05 11:15
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]
Navigation:
[Reply to this message]
|