tags:

views:

91

answers:

3

I am working on some PHP / mysql 5 web project involving a database table that uses not indexed GUID strings as its identifier for rows. So the rows of this product table look like this:

GUID                                   Name
936DA01F-9ABD-4d9d-80C7-02AF85C822A8   Product 1
07c5bcdc-b3a6-482f-8556-bb04fae06538   Product 2
b7b39700-d0e2-11de-8a39-0800200c9a66   Product 3

The new requirement is, when I am on the details page of Product 2, this page should contain a previous link (pointing to Product 1) and a back link pointing to Product 3.

Finding Product 2 is easy:

SELECT * FROM products WHERE GUID = "07c5bcdc-b3a6-482f-8556-bb04fae06538"

But how would I find the GUIDs of the previous and next products?

Remember: The product table has no auto-increment column and I cant add one easily as I am not in control over the database ...

EDIT:

The records are ordered by a timestamp column.

A: 

A row ID would help...

I found this link: http://forums.mysql.com/read.php?61,25654,173160#msg-173160

set @a := 0;
select *, @a := @a+1 as rowid
from table
limit 10 ;
MPelletier
And that would only be valid if your sorting criteria is the insertion order (kudos to empraptor for being thorough)
MPelletier
A: 

Wow, that's horrible.

Would something like this work? This is assuming you sort the products by name.

If you're on the Product 2 page...

To get Product 1:

select guid from product where name < 'Product 2' order by name desc limit 1;

To get Product 3:

select guid from product where name > 'Product 2' order by name asc limit 1;

Note that Bad Things will happen if you have products with the same name.

Jeff
Unfortunately the products have no naming schema, so this wont be an option. Yes, its really horrible.
Max
A: 

I am confused ....

Regarding the answer from Jeff; If the product has no naming schema (random?) how are the products sorted in the output to the pages?

If the output to the pages are sorted by timestamp only, then the answer from Jeff can be used with a small alteration of the where clause:

select guid from product
where timestamp < (select timestamp from product where name = 'Product 2')
order by timestamp desc limit 1;

and the other one:

select guid from product
where timestamp > (select timestamp from product where name = 'Product 2')
order by timestamp asc limit 1;


Or use the GUID from 'Product 2':

select guid from product
where timestamp < (select timestamp from product where GUID = guid_from_product2)
order by timestamp desc limit 1;

and the other one:

select guid from product
where timestamp > (select timestamp from product where guid = guid_from_product2)
order by timestamp asc limit 1;

Regards
     Sigersted

Sigersted