get 4 Results from table, in or around X PK

[onlyican]

OK, This seems more complex than it is. Trying to work out different methods

 

I have a table which lists pictures, description ect for an album, it also contains the Album ID

 

My Pictures page displays 4 images, based on Page number

 

But I want to have it to show by Image ID as well (So I can direct Link to that picture)

This means the pagination would not be known as it will change (Newest images first, so what was Page 1 can now be page 2)

 

Is there a way I can run a query which selects by the PK + 3 more (Either To left of or to right off)

For example, PK1, Select 1-4

PK 10 (out of 10 records) - Select 6-10

 

PK 5 SELECT 3-7

 

????

[fenway]

No, not really -- you'd have to know the IDs, in the desired order, to replicate that.

[kickstart]

Hi

 

This was bugging me, so I had a play.

 

This does what you seemed to want in your example (this is using a simple table of Id and PicName called Piccies).

 

SET @rank1=0;
SET @rank2=1;
SELECT *
FROM (SELECT *
FROM (SELECT * FROM
(SELECT @rank1:=@rank1+1 AS rank, Id, PicName
FROM Piccies
WHERE Id <= 10
ORDER BY Id DESC) a
UNION 
SELECT *
FROM (SELECT @rank2:=@rank2+1 AS rank, Id, PicNAme
FROM Piccies
WHERE Id > 10
ORDER BY Id ASC) b
) c
ORDER BY Rank
LIMIT 5) d
ORDER BY Id

 

All the best

 

Keith

[fenway]

Interesting approach -- but that assumes that there are that number of records before/after.

[kickstart]

Interesting approach -- but that assumes that there are that number of records before/after.

 

Hi

 

Not sure I understand you. I might have missed something.

 

It seemed that he wanted  5 records returned, either equally either side or if not enough then make up for it on one side. That SQL should do that.

 

What it won't do is work around (say) 10 records per page and if the 41st record is selected get records 41, 42, 43, 44 and 45. It could probably be modified to do that with a bit of hassle.

 

It will work if there is just 1 record before and loads after.

 

All the best

 

Keith

[fenway]

Well, now I'm really confused -- I only see one LIMIT, and it's a hard-coded constant.  Maybe the indentation is messing with my head.

[kickstart]

Hi

 

Yep, but I am getting 2 lists of items one of which might contain (say) 1 record if it is at the start or end. But I also get how far away from the chosen start point each returned record is, order by that and just grab the first 5 records.

 

So if you had records 1 to 10, and the starting record was 2 the ids and ranks would be:-

 

Id / Rank

1 1

2 0

3 1

4 2

5 3

6 4

7 5

8 6

9 7

10 8

 

So that would sort to:-

 

Id / Rank

2 0

3 1

1 1

4 2

5 3

6 4

7 5

8 6

9 7

10 8

 

Limit 5 would be

 

Id / Rank

2 0

3 1

1 1

4 2

5 3

 

Ordering by Id would be

 

Id / Rank

1 1

2 0

3 1

4 2

5 3

 

Rank can then be ignored.

 

At the moment it is using a hard coded start point (10) but that would be easy to change for real use.

 

All the best

 

Keith

[fenway]

Clearly, I'll have to re-read this when I'm not the middle of a work-day -- you're telling me this works even with arbitrary IDs?

[kickstart]

Hi

 

It currently assumes the IDs are the order records are to be presented in, but as no grouping it should be easy to use a different field to order the records in (along with retrieving other columns of data).

 

I may have missed something so I am interested in anything you find or if you modify it to be more efficient.

 

All the best

 

Keith

[fenway]

If you have some sample data, I'd love to play around with it.