0

I have a list of products with many columns, but for the sake of make this question more simple, we will taking in count just these ones: ID, published, productName, and publishedDate (2020-10-10 00:00:00). The IDs are not consecutive, and neither the dates, as I'm changing that date when I update the products.

I'm doing the pagination just obtaining the products ordered by date, and the with php I make an array, and then I look for the index of the previous and next product to one given to make the pagination on that product.

Is there a way to obtain those previous and next IDs of products to that given product with just mysql?

My subquery is like follow: SELECT * FROM table WHERE published = 'y' ORDER BY publishedDate DESC

I have been trying to use max(ID) and min(ID) in the main query, but as the publishedDates are not consecutive I do not always get the correct result.

I've been thinking if there is a way to create a column on the fly (a virtual id) for each result of the subquery that I can use then in my main query. I mean, if I have 5 products with ids: 1,2,3,4,5, that after the subquery and ordered by date like this 5,3,2,4,1, maybe I can create a virtual column called newID which gives a order number, and then I can look for the previous and next number to the product I'm seeing.

Another option is that I do not know if SQL actually gives to each result an internal index number, maybe I could use that internal number to make my query.

Another option would be to create a temporary table and then make a query against that table, but it will take more time to do that than make the process with php.

Maybe, another option would be to set on the a variable, but I can imaging how to do that for every result.

As I told you, I have working my pagination with php, so no rush. But just thinking in the performance of the app when the products are more than 2.000 products, maybe MySQL will be a best approach to solve this issue.

Any ideas about to do this if exists a solution to do this with MysQL?

Thanks in advance!

Shadow
  • 33,525
  • 10
  • 51
  • 64
Alberto
  • 151
  • 1
  • 8
  • That's a very long question for a simplification. Consider adding sample data and expected output. – P.Salmon Oct 11 '20 at 08:11
  • This post (https://stackoverflow.com/questions/3333665/rank-function-in-mysql) is not a valid answer for my problem, actually. But, I have solved with another approach which is creating a temporary table. It's the only solution it worked in my case. Thank you anyway! – Alberto Oct 11 '20 at 15:27

0 Answers0