Jump to content


Photo

database query question


  • Please log in to reply
9 replies to this topic

#1 tvdhoff

tvdhoff
  • Members
  • PipPip
  • Member
  • 16 posts

Posted 02 August 2006 - 03:11 PM

Hello all,

Sasa answered my previous question, which is basically still unanswered, because I forgot to mention one crucial detail.

I have a page that displays a publication from a database and want to have a short list of other entries next to it, as to use it for navigating those publications. The list should contain five entries, the entry that is displayed should be in the middle and should be surrounded by two newer and two older entries.

Sasa came up with this query:
SELECT id,title FROM content WHERE id >= $id-2 ORDER BY id ASC LIMIT 5

This works if the table that is queried contains all consecutively numbered IDs.

My content table has those too, but there's also a column called category that I need to include in my query.

So, suppose I retrieve a recordset that contains IDs 1, 3, 4, 11, 20. Sasa's trick $id-2 doesn't work here no more..

How would I make it work? Do I have to create a temp table (with new consecutive temp ids) with the result of this query first:
(SELECT id,title FROM content WHERE id >= $id and category=$category ORDER BY id ASC LIMIT 5)
UNION
(SELECT id,title FROM content WHERE id <= $id and category=$category ORDER BY id DESC LIMIT 5)

And then run Sasa's query:
SELECT id,title FROM temptable WHERE tempId >= (SELECT tempId FROM temptable WHERE id=$id)-2 ORDER BY id ASC LIMIT 5

???

Getting a headache here... anyone?

Help would be greatly appreciated.

UPDATE: I've tried the above solution with temporary tables, which could work, if not for this error:

SELECT id,title FROM temptable WHERE tempId >= (SELECT tempId FROM temptable WHERE id=$id)-2 ORDER BY id ASC LIMIT 5

Can't reopen table temptable


I'm really stuck now.


#2 bpops

bpops
  • Members
  • PipPipPip
  • Advanced Member
  • 232 posts

Posted 02 August 2006 - 05:14 PM

I don't want to leave you high and dry here, as I see you've had plenty of views, but no responses.
I'm assuming I'm having the same problems as other figuring out what you're trying to do. Can you please explain more clearly?

#3 ryanlwh

ryanlwh
  • Staff Alumni
  • Advanced Member
  • 511 posts

Posted 02 August 2006 - 05:29 PM

hmm.. why don't you run something like this?
(SELECT id,title FROM content WHERE id >= $id and category=$category ORDER BY id ASC LIMIT 3)
UNION 
(SELECT id,title FROM content WHERE id <= $id and category=$category ORDER BY id DESC LIMIT 3)
ORDER BY id DESC

tested with some of my own data... it will get 5 records and surround it by 2 older and 2 newer. you use limit 3 on both queries, but UNION is distinct, so the duplicated one, which is the middle record, will be replaced by itself. if there is only 1 available on one end, then it will only contain 4 records.
Please use EDIT * 100...
Please use
or [php] * 1000...

PLEASE READ THE POSTED SOLUTIONS CAREFULLY * 1000000...

#4 tvdhoff

tvdhoff
  • Members
  • PipPip
  • Member
  • 16 posts

Posted 02 August 2006 - 05:33 PM

Hi, thanks for the attention.

What I want is this:

I have a page that displays a publication from a database and want to have a short list of other titles next to it, as to use it for navigating those publications. The list should contain five entries, the publication that is displayed should be in the middle and should be surrounded by two newer and two older entries.


A normal table with an autoincrementing primary key column called 'id' can be queried for this purpose like this:
SELECT id,title FROM content WHERE id >= $id-2 ORDER BY id ASC LIMIT 5

But in my case, I have a table called 'content' which contains different types of data. A column called 'category_id' is used to keep track of those different kinds of data. Because of this mixed data, I can't rely on the primary key 'id' as in the example above to make that list of five consecutive titles (that are of the same category type).

My solution was to create a temp table to first get a set of rows that are of the same category and give them an extra temp_id and then run that first query to get that list of five titles.

However, the present code (as displayed in first post) gives an error 'can't reopen table' and needs fixing. Furthermore, I can't imagine there not being a less expensive way to get the desired result.

I hope I made myself clearer than before. Looking forward to your reply.

Thanks,
Tim


#5 bpops

bpops
  • Members
  • PipPipPip
  • Advanced Member
  • 232 posts

Posted 02 August 2006 - 05:37 PM

Well ryan might have the solution for you up there, but I was thinking you could always do something like

(SELECT id,title FROM content WHERE id >= ($id-3) and category=$category ORDER BY id DESC LIMIT 6)

which would just get them all.. although I don't know if that's being order the way you want.


#6 ryanlwh

ryanlwh
  • Staff Alumni
  • Advanced Member
  • 511 posts

Posted 02 August 2006 - 05:38 PM

bpops, with the category in there, you can't gaurantee the id's will be in CONSECUTIVE orders anymore, so id-3 might not get you anything either...

tvdhoff, see my post above yours.
Please use EDIT * 100...
Please use
or [php] * 1000...

PLEASE READ THE POSTED SOLUTIONS CAREFULLY * 1000000...

#7 tvdhoff

tvdhoff
  • Members
  • PipPip
  • Member
  • 16 posts

Posted 02 August 2006 - 05:42 PM

hmm.. why don't you run something like this?

(SELECT id,title FROM content WHERE id >= $id and category=$category ORDER BY id ASC LIMIT 3)
UNION 
(SELECT id,title FROM content WHERE id <= $id and category=$category ORDER BY id DESC LIMIT 3)
ORDER BY id DESC

tested with some of my own data... it will get 5 records and surround it by 2 older and 2 newer. you use limit 3 on both queries, but UNION is distinct, so the duplicated one, which is the middle record, will be replaced by itself.


Thanks, that actually does the trick. It's practically the same as my initial solution, but as you correctly put it, the duplicate row gets replaced and becomes the third row if there are at least five rows in the recordset to limit down to five.

This is good enough, though I would like to know if you could think of a way to make sure there are always five rows outputted. I can only imagine that you would first have to run the query and find the number of rows and then modify the query as needed to get five rows back (without $id being the middle row in that case). It's not really needed for functionality, but it's nicer aesthetically speaking - as long as it doesn't take to much resources....

Any thoughts?

Thanks.
Tim

#8 bpops

bpops
  • Members
  • PipPipPip
  • Advanced Member
  • 232 posts

Posted 02 August 2006 - 05:42 PM

bpops, with the category in there, you can't gaurantee the id's will be in CONSECUTIVE orders anymore, so id-3 might not get you anything either...

tvdhoff, see my post above yours.


ah, i see, I didn't understand that before.

#9 ryanlwh

ryanlwh
  • Staff Alumni
  • Advanced Member
  • 511 posts

Posted 02 August 2006 - 07:20 PM

try this
(SELECT id,title,@a:=@a-1 AS rownum FROM content, (SELECT @a:=0) AS aWHERE id<$id AND category=$category ORDER BY id DESC LIMIT 4)
UNION
(SELECT id,title,0 AS rownum FROM content, (SELECT 0) AS a WHERE id=$id AND category=$category )
UNION
(SELECT id,title, @b:=@b+1 AS rownum FROM content, (SELECT @b:=0) AS aWHERE id>$id AND category=$category ORDER BY id ASC LIMIT 4)
ORDER BY rownum, id DESC LIMIT 5;

There will be two extra columns, just ignore them. if this started to get too slow, create an index on id and category.
Please use EDIT * 100...
Please use
or [php] * 1000...

PLEASE READ THE POSTED SOLUTIONS CAREFULLY * 1000000...

#10 tvdhoff

tvdhoff
  • Members
  • PipPip
  • Member
  • 16 posts

Posted 04 August 2006 - 11:36 AM

thanks a lot!




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users