Next topic?

[MattDurnan]

Hi

I havent really got much of an idea of how to do like next and previous topics like on phpbb.

"View previous topic :: View next topic"

As the ids of the topics are not always +1/-1 then the only way that i could think of would be to try and get the number by doing a loop. Im not sure if there is a mysql query that you can use though.

If anyone can help id be really grateful.

Thanks,

~Matt

[mrhappiness]

next:

SELECT t.topic_id
FROM topics t
WHERE t.topic_id > $the_current_topics_id
ORDER BY t.topic_id ASC
LIMIT 1

prev:

SELECT t.topic_id
FROM topics t
WHERE t.topic_id < $the_current_topics_id
ORDER BY t.topic_id DESC
LIMIT 1

what about these?

[bluns]

Hey i am also having trouble doing the same thing 🙁

  <?php
$id=$_GET['id']
  $query= "SELECT id FROM data WHERE id < '$id' ORDER BY id DESC LIMIT 1";
  $result = mysql_query($query);
  $row=mysql_fetch_row($result);
  echo $row->id;
  ?>

 

i get nothing as a result 🙁 anyone know whats wrong with it?

[mrhappiness]

$id=$_GET['id']
echo $query= "SELECT id FROM data WHERE id < $id ORDER BY id DESC LIMIT 1"; //debug output
$result = mysql_query($query) or die(mysql_error());
$row=mysql_fetch_row($result) or die(mysql_error());
echo $row[0]; 

what does happen now

[PurpleMonkey]

You could set the limit to 2 and then use the DESC function to swap the rows around so the top one will have the the next topics id in.

[MattDurnan]

I sitll havent solved this:

I have now:

$query = mysql_query("SELECT * FROM tw_topic WHERE forum_id='$forum_id' AND last_post_date > '$last_post_date' ORDER BY last_post_date ASC LIMIT 0,1") or die (@mysql_error());
$row = mysql_fetch_array($query);
//header ("Location: forum.php?fourm_id=$forum_id&topic_id=$row[topic_id]");
echo "Redirecting...";
echo "<META HTTP-EQUIV=Refresh CONTENT=\"3; URL=forum.php?forum_id=$forum_id&topic_id=$row[topic_id]\">";

I dont know why it doesnt work. It does redirect to a topic but not the correct one.

[tekky]

lets see what you are sending?

what kinda columns are each of these? and give us an example...

if you are on topic_id=8 what is the NEXT one this currently SELECT gives you? not 9 you say... is it 20? 4? -13?

all those things can help us help you 😉

SELECT * FROM tw_topic WHERE forum_id='$forum_id' AND last_post_date > '$last_post_date' ORDER BY last_post_date ASC LIMIT 0,1

[MattDurnan]

This works fine but the thing is, when you post again then you have to start from the beginning 😐

if(@$view){
	if(!$last_post){
		$gquery = mysql_query("SELECT * FROM tw_topic WHERE forum_id='$forum_id' AND topic_id='$topic_id'") or die (@mysql_error());
		$grow = mysql_fetch_array($gquery);
		$last_post = $grow[last_post_date];
	}

if($view=="next"){
	$query = mysql_query("SELECT * FROM tw_topic WHERE forum_id='$forum_id' AND topic_id!='$topic_id' AND last_post_date < '$last_post' ORDER  BY last_post_date DESC LIMIT 1") or die (@mysql_error());
	$row = mysql_fetch_array($query);

	if(@mysql_num_rows($query) > 0){
	//header ("Location: forum.php?forum_id=$forum_id&topic_id=$row[topic_id]");
	//echo "Redirecting...";
	//echo "<META HTTP-EQUIV=Refresh CONTENT=\"3; URL=forum.php?forum_id=$forum_id&topic_id=$row[topic_id]\">";
		$topic_id=$row[topic_id];
	}
	else {
		$e = "There are no more topics";
	}
}
else {
	$query = mysql_query("SELECT * FROM tw_topic WHERE forum_id='$forum_id' AND last_post_date >  '$last_post' ORDER BY last_post_date ASC
	LIMIT 1") or die (@mysql_error());
	$row = mysql_fetch_array($query);

	if(@mysql_num_rows($query) > 0){
	//header ("Location: forum.php?forum_id=$forum_id&topic_id=$row[topic_id]");
	//echo "Redirecting...";
	//echo "<META HTTP-EQUIV=Refresh CONTENT=\"3; URL=forum.php?forum_id=$forum_id&topic_id=$row[topic_id]\">";
		$topic_id=$row[topic_id];
	}
	else {
		$e = "There are no previous topics";
	}
}
}

[synergypoint]

This really isn't that hard if i understand what you are trying to do.

You need to run the same query each time but change the value set for limit x,y.

SELECT * FROM table WHERE something ORDER BY ASC/DESC LIMIT $recstart, $numrecs

First time you run the script I assume $recstart=0 and $numrecs=1 (if you only want to return 1 record).

When the user clicks "next" increment the $recstart value by 1.

If the user clicks "previous" increment $recstart by -1. As long as the rest of your query is the same, you will always be returning a recordset in the same order (assuming your database doesn't get updated in midstream). Using the record start value of LIMIT lets you effectively move forward and back in the record set.

You do have to check for the case in which you are either at the begining or end of the record set. I do this by running an identical seperate query that counts the number of records returned for the entire set. In that query eliminate the LIMIT part.

SELECT count(*) FROM table WHERE something

Does that solve it?