BlameThePixel - PHP Pagination

I'm making a forum for my site, and for the pagination at the page that shows the threads (forumview.php), I have followed this tutorial. But it doesn't work! Maybe it's because I use joins for selecting all the stuff I need, but I have no clue how I would solve the problem.
The table with the threads in is called f_threads, and the table with the posts in (including the first) is called f_posts.
This is the code for the pagination (tabs don't seem to work well):
\n

if(isset($_GET["page"])) //forumview.php?id=1&page=1, for ex. { $page = $_GET["page"]; $limit = 3; //A weird limit, just for testing $lstart = $page * $limit - ($limit); } else { $page = 1; $limit = 3; $lstart = $page * $limit - ($limit); } $sql = "SELECT f_threads.*, f_posts.date, f_posts.thread FROM f_threads INNER JOIN f_posts ON f_posts.thread = f_threads.id AND f_threads.forum = ".$_GET['id']." ORDER BY date DESC LIMIT ".$lstart.", ".$limit; //Selecting everything I need to output later, isn't shown in this part of the page $result = mysql_query("$sql"); $sqlc = "SELECT COUNT(*) FROM f_threads"; //FROM HERE $resultc = mysql_query("$sqlc"); $totalrows = mysql_num_rows($resultc); //TO HERE is what I think messes things up echo $totalrows.""; //I echoed $numofpages just to see what it is ?>\n
What is wrong with this? I have followed the tutorial perfectly well.
Can you help?

I'm making a forum for my site, and for the pagination at the page that shows the threads (forumview.php), I have followed <a href="http://www.phpfreaks.com/tutorials/43/0.php">this</a> tutorial. But it doesn't work! Maybe it's because I use joins for selecting all the stuff I need, but I have no clue how I would solve the problem.
The table with the threads in is called f_threads, and the table with the posts in (including the first) is called f_posts.
This is the code for the pagination (tabs don't seem to work well):
[code]<?php
if(isset($_GET["page"])) //forumview.php?id=1&<b>page=1</b>, for ex.
{
$page = $_GET["page"];
$limit = 3; //A weird limit, just for testing
$lstart = $page * $limit - ($limit);
}
else
{
$page = 1;
$limit = 3;
$lstart = $page * $limit - ($limit);
}
	
$sql = "SELECT f_threads.*, f_posts.date, f_posts.thread FROM f_threads INNER JOIN f_posts ON f_posts.thread = f_threads.id AND f_threads.forum = ".$_GET['id']." ORDER BY date DESC LIMIT ".$lstart.", ".$limit; //Selecting everything I need to output later, isn't shown in this part of the page
$result = mysql_query("$sql");
$sqlc = "SELECT COUNT(*) FROM f_threads"; //FROM HERE
$resultc = mysql_query("$sqlc");
$totalrows = mysql_num_rows($resultc); //TO HERE is what I think messes things up
	
echo $totalrows."<table width='100%' class='sometables'><tr><td class='bsometds' align='center' colspan='5'>"; //Start the output table, ended later in another part of the page. I echoed $totalrows just to see what it is
	
$numofpages = $totalrows / $limit;
for($i = 1; $i <= $numofpages; $i++) //This for loop prints the page numbers
{
if($i == $page)
{
echo "[".$i."]";
}
else
{
echo "[<a href='forumview.php?id=".$_GET['id']."&page=".$i."'>".$i."</a>]";
}
}
if($totalrows % $limit != 0) //This if prints the last number in some cases
{
if($i == $page)
{
echo "[".$i."]";
}
else
{
echo "[<a href='forumview.php?id=".$_GET['id']."&page=".$i."'>".$i."</a>]";
}
}
echo $numofpages."</td></tr>"; //I echoed $numofpages just to see what it is
?>[/code]
What is wrong with this? I have followed the tutorial perfectly well.
Can you help?

"; //Start the output table, ended later in another part of the page. I echoed $totalrows just to see what it is $numofpages = $totalrows / $limit; for($i = 1; $i <= $numofpages; $i++) //This for loop prints the page numbers { if($i == $page) { echo "[".$i."]"; } else { echo "[".$i."]"; } } if($totalrows % $limit != 0) //This if prints the last number in some cases { if($i == $page) { echo "[".$i."]"; } else { echo "[".$i."]"; } } echo $numofpages."
22.06.04 09:41 Post #1	Last edited: 22.06.04 09:42 (Dingbats - 1 times)	[Softbrain Games] [Hide Sig (2)] [Profile] [Quote]
Zogger! Looking For Status Send PM Posts: 3954 Threads: 62 Money: £93.82 (D) (+ Friend)	first I have to say: lol. have you been looking at our code? you're using all the same table names and column names. If someone didn't show you then it's quite funny . what exactly is the problem? it doesn't show the pages? it doens't give you a different limit when you click on a different number? first look, I would say you do need some kind of WHERE on this query: SELECT COUNT() FROM f_threads otherwise it'll just count all the threads in any forum. If you say what doesn't work I'll probly be able to help more. heh. first I have to say: lol. have you been looking at our code? you're using all the same table names and column names. If someone didn't show you then it's quite funny :). what exactly is the problem? it doesn't show the pages? it doens't give you a different limit when you click on a different number? first look, I would say you do need some kind of WHERE on this query: SELECT COUNT() FROM f_threads otherwise it'll just count all the threads in any forum. If you say what doesn't work I'll probly be able to help more. heh. ________________ You know I'm a dancing machine
22.06.04 10:30 Post #2	[Hide Sig (8)] [Profile] [Quote]
Dingbats ; Send PM Posts: 1970 Threads: 50 Mood: Optative Money: £268.57 (D) (+ Friend)	Oh. Of course I need a WHERE. I added that, but nothing changed. What happens? This: There are seven threads in the forum, but $totalrows shows up as 1, with or without the WHERE. At the first page, there are only two threads, the two latest. There are no links to the other pages, only a [1] which is not a link. At page 2, the next the 3 threads are shown, just as they should. And at page 3 the remaining 2 threads are shown. $numofpages is 0.33333333333333. Everything is very weird. I'm totally confused. Oh. Of course I need a WHERE. I added that, but nothing changed. What happens? This: There are seven threads in the forum, but $totalrows shows up as 1, with or without the WHERE. At the first page, there are only two threads, the two latest. There are no links to the other pages, only a [1] which is not a link. At page 2, the next the 3 threads are shown, just as they should. And at page 3 the remaining 2 threads are shown. $numofpages is 0.33333333333333. Everything is very weird. I'm totally confused. :\|
22.06.04 10:53 Post #3	[Softbrain Games] [Hide Sig (2)] [Profile] [Quote]
Zogger! Looking For Status Send PM Posts: 3954 Threads: 62 Money: £93.82 (D) (+ Friend)	yes, total rows will be one because you're using mysql_num_rows, it only returns one row, which contains the number of rows that match that query. you need to change the mysql_num_rows to mysql_result($resultc, 0); then see what happens. yes, total rows will be one because you're using mysql_num_rows, it only returns one row, which contains the number of rows that match that query. you need to change the mysql_num_rows to mysql_result($resultc, 0); then see what happens. ________________ You know I'm a dancing machine
22.06.04 13:01 Post #4	[Hide Sig (8)] [Profile] [Quote]
Dingbats ; Send PM Posts: 1970 Threads: 50 Mood: Optative Money: £268.57 (D) (+ Friend)	Thanks, that works quite well, but it's still a little odd. The first page shows only two threads, the second shows three, and the third and last shows two. The first and second should show three each, and the last two. Weird. Thanks, that works quite well, but it's still a little odd. The first page shows only two threads, the second shows three, and the third and last shows two. The first and second should show three each, and the last two. Weird.
22.06.04 13:05 Post #5	[Softbrain Games] [Hide Sig (2)] [Profile] [Quote]
ReadMe Absent Send PM Posts: 2820 Threads: 85 Money: £43.42 (D) (+ Friend)	I'm not even sure that you main query is correct, if we take $_GET['id'] to be 5, start to be 0 and limit to be 10 then it would read as follows: SELECT f_threads., f_posts.date, f_posts.thread FROM f_threads INNER JOIN f_posts ON f_posts.thread = f_threads.id AND f_threads.forum = 5 ORDER BY date DESC LIMIT 0, 10; you seem to have what should be a where clause in the join clause, the query should read SELECT f_threads., f_posts.date, f_posts.thread FROM f_threads INNER JOIN f_posts ON f_posts.thread = f_threads.id WHERE f_threads.forum = 5 ORDER BY date DESC LIMIT 0, 10; in fact, this will select all threads and all posts, so it's still not right. lemme think... try this: SELECT f_threads., f_posts.date, f_posts.thread FROM f_threads INNER JOIN f_posts ON f_posts.thread = f_threads.id AND f_threads.forum = 5 GROUP BY f_threads.id ORDER BY date DESC LIMIT 0, 10; I'm not even sure that you main query is correct, if we take $_GET['id'] to be 5, start to be 0 and limit to be 10 then it would read as follows: SELECT f_threads., f_posts.date, f_posts.thread FROM f_threads INNER JOIN f_posts ON f_posts.thread = f_threads.id AND f_threads.forum = 5 ORDER BY date DESC LIMIT 0, 10; you seem to have what should be a where clause in the join clause, the query should read SELECT f_threads., f_posts.date, f_posts.thread FROM f_threads INNER JOIN f_posts ON f_posts.thread = f_threads.id WHERE f_threads.forum = 5 ORDER BY date DESC LIMIT 0, 10; in fact, this will select all threads and all posts, so it's still not right. lemme think... try this: SELECT f_threads., f_posts.date, f_posts.thread FROM f_threads INNER JOIN f_posts ON f_posts.thread = f_threads.id AND f_threads.forum = 5 GROUP BY f_threads.id ORDER BY date DESC LIMIT 0, 10; ________________ Cant be arsed to remake my sig.
22.06.04 15:25 Post #6	[Hide Sig (7)] [Profile] [Quote]
Archamond Statusless? Maybe Send PM Posts: 1825 Threads: 123 Mood: meh Money: £422.09 (D) User Tax: 5% (+ Friend)	Ofcourse, this is not porn This is way above my skills but I will give one tip that I get in my short experience. Your php settings maybe couses problems I cant test any page on my PC cos settings are wrong and it doesnt work, so you try to test it on server where site will beroll: PS>Thanks for turtorials link This is way above my skills but I will give one tip that I get in my short experience. Your php settings maybe couses problems:):) I cant test any page on my PC cos settings are wrong and it doesnt work, so you try to test it on server where site will be:Droll::D PS>Thanks for turtorials link:):):)
22.06.04 15:34 Post #7	Last edited: 22.06.04 15:34 (Archamond - 1 times)	[ArchamondCOM ] [Hide Sig (20)] [Profile] [Quote]
C1 Looking For Status Send PM Posts: 0 Threads: 0 Money: £0.18 (D) (+ Friend)	Dingbats, talk to me on msn and I'll give you my pageination code. Dingbats, talk to me on msn and I'll give you my pageination code.
22.06.04 15:55 Post #8	[Hide Sig (2)] [Profile] [Quote]
Dingbats ; Send PM Posts: 1970 Threads: 50 Mood: Optative Money: £268.57 (D) (+ Friend)	Quoted :: ReadMe I'm not even sure that you main query is correct, if we take $_GET['id'] to be 5, start to be 0 and limit to be 10 then it would read as follows: SELECT f_threads., f_posts.date, f_posts.thread FROM f_threads INNER JOIN f_posts ON f_posts.thread = f_threads.id AND f_threads.forum = 5 ORDER BY date DESC LIMIT 0, 10; you seem to have what should be a where clause in the join clause, the query should read SELECT f_threads., f_posts.date, f_posts.thread FROM f_threads INNER JOIN f_posts ON f_posts.thread = f_threads.id WHERE f_threads.forum = 5 ORDER BY date DESC LIMIT 0, 10; in fact, this will select all threads and all posts, so it's still not right. lemme think... try this: SELECT f_threads., f_posts.date, f_posts.thread FROM f_threads INNER JOIN f_posts ON f_posts.thread = f_threads.id AND f_threads.forum = 5 GROUP BY f_threads.id ORDER BY date DESC LIMIT 0, 10; Yay, it works! Thanks, ReadMe! Though I don't understand what the difference is... Edit:* Nope, doesn't work perfecly. It doesn't order them by the last post. [quote=ReadMe] I'm not even sure that you main query is correct, if we take $_GET['id'] to be 5, start to be 0 and limit to be 10 then it would read as follows: SELECT f_threads., f_posts.date, f_posts.thread FROM f_threads INNER JOIN f_posts ON f_posts.thread = f_threads.id AND f_threads.forum = 5 ORDER BY date DESC LIMIT 0, 10; you seem to have what should be a where clause in the join clause, the query should read SELECT f_threads., f_posts.date, f_posts.thread FROM f_threads INNER JOIN f_posts ON f_posts.thread = f_threads.id WHERE f_threads.forum = 5 ORDER BY date DESC LIMIT 0, 10; in fact, this will select all threads and all posts, so it's still not right. lemme think... try this: SELECT f_threads.*, f_posts.date, f_posts.thread FROM f_threads INNER JOIN f_posts ON f_posts.thread = f_threads.id AND f_threads.forum = 5 GROUP BY f_threads.id ORDER BY date DESC LIMIT 0, 10; [/quote] Yay, it works! Thanks, ReadMe!:) Though I don't understand what the difference is...:\| <b>Edit:</b> Nope, doesn't work perfecly. It doesn't order them by the last post.
22.06.04 16:45 Post #9	Last edited: 22.06.04 16:47 (Dingbats - 1 times)	[Softbrain Games] [Hide Sig (2)] [Profile] [Quote]
ReadMe Absent Send PM Posts: 2820 Threads: 85 Money: £43.42 (D) (+ Friend)	change date to f_posts.date change date to f_posts.date ________________ Cant be arsed to remake my sig.
22.06.04 18:22 Post #10	[Hide Sig (7)] [Profile] [Quote]
Dingbats ; Send PM Posts: 1970 Threads: 50 Mood: Optative Money: £268.57 (D) (+ Friend)	I did that, but there was no difference... I did that, but there was no difference...:(
23.06.04 09:02 Post #11	[Softbrain Games] [Hide Sig (2)] [Profile] [Quote]
ReadMe Absent Send PM Posts: 2820 Threads: 85 Money: £43.42 (D) (+ Friend)	all forums+latest post is a pretty tricky query to pull off really, i's the problem that the posts it puts with each forum not the latest ones? just had a look at how BTP pulls it off, cheating really. There's actually a last post ID stored in the forum table and then each forum has a query to find the latest post. all forums+latest post is a pretty tricky query to pull off really, i's the problem that the posts it puts with each forum not the latest ones? just had a look at how BTP pulls it off, cheating really. There's actually a last post ID stored in the forum table and then each forum has a query to find the latest post. ________________ Cant be arsed to remake my sig.
23.06.04 21:17 Post #12	[Hide Sig (7)] [Profile] [Quote]
Zogger! Looking For Status Send PM Posts: 3954 Threads: 62 Money: £93.82 (D) (+ Friend)	yeh, that was back in the days of me not really knowing much about JOINs... I should update that sometime, get the queries down. yeh, that was back in the days of me not really knowing much about JOINs... I should update that sometime, get the queries down. ________________ You know I'm a dancing machine
24.06.04 10:03 Post #13	[Hide Sig (8)] [Profile] [Quote]
Dingbats ; Send PM Posts: 1970 Threads: 50 Mood: Optative Money: £268.57 (D) (+ Friend)	If I understood your question correctly ReadMe, the answer is no. The problem is not which posts it links to which threads. It just orders the threads wrong. If I understood your question correctly ReadMe, the answer is no. The problem is not which posts it links to which threads. It just orders the threads wrong.
24.06.04 11:43 Post #14	[Softbrain Games] [Hide Sig (2)] [Profile] [Quote]
ReadMe Absent Send PM Posts: 2820 Threads: 85 Money: £43.42 (D) (+ Friend)	been thinking about that and i cant see a way around it inside mysql since it has to be grouped by thread. You'll need to build a large array of threads and then sort them, and then you can parse the output with php. Actually, I'd have your SQL output parser create an array with the timestamp of the last post as the key, and the output for that thread's row in the field. And then just do an ksort() and ouput the array with foreach. been thinking about that and i cant see a way around it inside mysql since it has to be grouped by thread. You'll need to build a large array of threads and then sort them, and then you can parse the output with php. Actually, I'd have your SQL output parser create an array with the timestamp of the last post as the key, and the output for that thread's row in the field. And then just do an ksort() and ouput the array with foreach. ________________ Cant be arsed to remake my sig.
24.06.04 23:31 Post #15	[Hide Sig (7)] [Profile] [Quote]
Page: [1] [2]