End Of The Table Issue

[dweb]

Hi everyone

 

I have a QUERY to get a product based on a ID, and I am trying to make a next and previous button.

 

I'm using the following query

 

SELECT

p.id, p.name,

prev.id AS prevID, prev.name AS prevNAME,

next.id AS nextID, next.name AS nextNAME

FROM products p

LEFT JOIN products prev ON prev.id < p.id

LEFT JOIN products next ON next.id > p.id

WHERE p.type = 1 AND p.del = 1 AND p.id = '".$currentID."'

AND prev.type = 1 AND prev.del = 1

AND next.type = 1 AND next.del = 1

ORDER BY prev.id DESC,next.id ASC LIMIT 1

 

 

which gives me the current product, the next product name and ID and the previous product name and ID

 

It works ok, the problem is when you reach the last row, or first row, it won't return anything, I think because of the part

 

AND prev.type = 1 AND prev.del = 1

AND next.type = 1 AND next.del = 1

 

 

Is there a better way to do this and a way which will work

 

Thanks

[Barand]

SELECT
p.id, p.name,
prev.id AS prevID, prev.name AS prevNAME,
next.id AS nextID, next.name AS nextNAME
FROM products p
LEFT JOIN products prev ON prev.id < p.id
   AND prev.type = 1 AND prev.del = 1
LEFT JOIN products next ON next.id > p.id
   AND next.type = 1 AND next.del = 1
WHERE p.type = 1 AND p.del = 1 AND p.id = 1
ORDER BY prev.id DESC,next.id ASC LIMIT 1

[dweb]

thanks very much