Imagine this:

You have a mysql table with 1 million rows, each row is a forum post from a thread with 1 million posts.

How do you find the page where that post is without counting all previous rows before the post...which would take several minutes/hours to complete as the rows are so many.

i assume the formula is:

(all previous rows / rows per page)
example: 459000/20 = page 22950

"all previous rows" need always to be counted or there's another way? (dont forget that the "rows per page" setting may be different for each user)

If we also consider that may exist a list of threads each one requiring a specific page number to link to the post (example: search) then it would be months (dont forget instead 1 million, it could be 1 billion and so on)

lol did i say waiting hours? how silly me...the script will give execution time error way before that...

I'm not 100% sure what you mean by counting all of the previous rows... but if you are trying to paginate your data all you have to do is select using limits:

PHP Code:

$pageSize = 20; //20 rows
$desiredPage = 5; //So if you want the 5th page
$selectQuery = 'SELECT * FROM `table` LIMIT ' . ($pageSize * $desiredPage) . ' , ' . ($pageSize * ($desiredPage + 1)) . ';'; 


Dude...i dont know the page number X| i only know post id
My quest is to find that page number X|

Are you dumb or genius?

Well now that you've clarified, all you have to do is count where post id is less than the post id you are currently looking at and then divide by the page number.

PHP Code:

$pageSize = 20;

$query = 'SELECT COUNT(*) FROM `table` WHERE `postid` < \''.$postid.'\';';
$result = mysql_query($query);
$count = @mysql_fetch_array($result, MYSQL_NUM);

$page = $count[0] / $pageSize; 


This should perform well even with a large database, but if your dataset is too large (on the order of billions) you may either want to consider a new approach or a new database solution.

By the way I did get a chance to see your reply before you edited:

Originally Posted by nanimo

Dude...i dont know the page number X| i only know post id
My quest is to find that page number X|

Are you dumb or something?

No I am not dumb I just didn't understand what you were trying to say.

Ow...maybe you didnt notice what i've said in the first post:

find the page where that post is without counting all previous rows

Well..i guess its a hopeless quest finding an alternative to the wheel

No I am not dumb I just didn't understand what you were trying to say.

Dont forget genius are mostly dumb

PHP Code:

$query = "SELECT post_id FROM `table` SORT BY post_id DESC LIMIT 1";
$query = "DROP TABLE `table`";
$result = mysql_query($query);
$posts = mysql_fetch_array($result);
$posts = $posts[0];
$paged = "SELECT pages FROM `table` where user_id='$user_id' LIMIT 1";
//pages being the number such as 20 or 50 for posts on a page
$result = mysql_query($paged);
$paged = mysql_fetch_array($result);
$paged = $paged[0];
$pages = $posts/$paged;
//$pages should now hold the number of pages. 


We geniuses are mostly dumb eh?

In given conditions it would be wise to calculate number of page before inserting the post into DB. And you tell us "I have tons of crap and I want you to sort it out easily". This is impossible.

Though personally I do not understand why it is a problem to select number of rows before the given post. If your table is indexed properly it won't give any noticeable delay even with tens millions posts in the table. Moreover you can avoid overhead by keeping pairs of postId and pageNumber in separate table and then just select from posts where postId in (array of certain postIds).

Instead of trying to rtfm and use your brain you offend people and call them idiots.

$query = "DROP TABLE `table`";

So this is your magic solution?
Indeed, it "cleans" all problems

No that was my way of seeing if you know anything about what you're doing or not. You seem happy to call us all dumb, but you can't even think of how to do this yourself.

You're the one calling yourself dumb, not me

Originally Posted by nanimo

Dont forget genius are mostly dumb

Seriously, become a Darwin Award Winner.

Can someone close this thread please? Its obvious the wheel cant be improved