How to get where is the old row in new query?

[eaglehopes]

My question is about how to get where is the old row in new query. Let me explain in detail. I have a categorized blog pages such under "computer programming". Computer programming has its own sub categories too. One is "Linux" for instance. And when I did "pagination" to show all blogs under "computer programming"\"Linux" such as, list of id and link were  in table by sql query

SELECT id,path, subCategoryId FROM `files` WHERE categoryId=5 AND subCategoryId=15

 result table was :

id	 path	                    subCategoryId
297	/pages/blog/PH/GT/10.html	15
298	/pages/blog/PH/GT/11.html	15
299	/pages/blog/PH/GT/12.html	15
300	/pages/blog/PH/GT/1.html	15
301	/pages/blog/PH/GT/2.html	15
302	/pages/blog/PH/GT/3.html	15
303	/pages/blog/PH/GT/4.html	15
304	/pages/blog/PH/GT/5.html	15
305	/pages/blog/PH/GT/6.html	15
306	/pages/blog/PH/GT/7.html	15
307	/pages/blog/PH/GT/8.html	15
308	/pages/blog/PH/GT/9.html	15
333	/pages/blog/PH/GT/13.html	15

There are some missing files(htmls), so their id numbers did not match with the neither id number nor their filename.  PH -philosophy is the category name, GT - general topics is subcategory name.

In my home page, I listed the most current ones according to their edit dates and when user clicked the link of one of the latest blogs, link directed page to the according category page. There is a pagination at each category and I am showing three records per page.

So when I clicked the for instance the link of the file "8.html", I knew that I will be in number 4 in the result of the first query and know that it will be shown in the page #4(since three records was shown in each page).

The point I stucked is that :

1. I did the first query and get the results (table above) 

2. How can I find the position of the page 8.html in the query I did in above using sql ? 

So my question is simply : how can I get an queried item's order/position?

 

What I want to achieve is that, in my webpage's home, I show  latest blogs. When user clicked one of them, I want to go to the related category and subcategory page and correct pagination link that include the blog will be back-colored with green. (one can get/test the question idea in my web page by clicking the latest blog in my home page and see the result : http://enginery.freecluster.eu/index.php?page=blog&subblog=CP&fin=WEB&blogPage=./pages/blog/CP/WEB/14.html. It opens the correct category and pagination is working, but I could not find the position of blog, where it was under the correct pagination. I want to color the correct link of the page number when I got there.) 

I hope, I could explain my question clearly.

Thanks for any help.

[kicken]

Before anything else, you need to add an ORDER BY clause to your query so that you can ensure a consistent order of your results.  Without an ORDER BY clause, the database server is allowed to return the rows in whatever order it wants, usually that's just whatever order it finds them in as it scans the indexes/tables.  As you add/remove data that order will change.

Once you have your results ordered in a specific way, you can count the number of records that come before a specific row by running a query with a where condition of theOrderByColumn <= yourCurrentRowsOrderByColumn.  For example.

SELECT id,path, subCategoryId 
FROM `files` 
WHERE categoryId=5 AND subCategoryId=15
ORDER BY id;

SELECT COUNT(*) 
FROM files
WHERE categoryId=5 AND subCategoryId=15 AND id <= 307

 

[eaglehopes]

Thanks kicken! So, I learned that "ordering" its importance in such situations. Without it, I am working on this solution:

1. a loop on the first query results and start a counter 

2. match the path of the filename with the filename(or path) in the row

3. get the counter and find the page number by dividing counter to the result per page variable

But, your solution is much more simple and elegant. Mehcanics always said that "less number or parts cause less errors and provide higher reliability". Less code does the same. That will solve my problem and I can find that my file is on which page by COUNT(*).