combining two querys into one

[durangod]

mysql version 5.1.54

 

i wrote a page and the page works fine other than the pagination,  the problem i believe is that i have two while loops and two queries.

 

the purpose of this page is as a total replies page, meaning it shows all the replies to my topics so people do not have to go looking for them.  all the replies are on one page, like a bump page.

 

the first query grabs all my replies from the replies table

the secone query takes those results, and finds the associated topic id and finds the topic title from the topic table and then display's them

 

in therory i should be able to to just load the first array with all the replies and use that as a feeder for my second query and place the limit on the second query but it is not working correctly.

 

so what i need to do (i guess) is combine the two queries (join i guess) so that i can use my query limit one time and it should work.

 

here is the code.

 

i have had several feeble attempts at this join and failed misserably, so i did not want to post that trash join code here

but here is my php code.

 

any suggestions would be appreciated thanks so much.

 

Is Join or left join the way to go here?

 

from the replies table i need the tid and the date

from the topics table i just need the topic title

then what is displayed on the page is the topic title and date (this is the reply date not the topic date)

 

<?php

//get total replies of forum
$queryr = "SELECT * FROM `forum_replies` ORDER by `date` ASC ";
$resultr = mysql_query($queryr,$link) or die(mysql_error());
$totrep = mysql_num_rows($resultr);
                   
if ($totrep == 0) {
    echo "No replies found, nothing to print sorry";
    exit;
}
  
   
// While a row of data exists, put that row in $row as an associative array
// Note: If you're expecting just one row, no need to use a loop
while ($row = mysql_fetch_assoc($resultr)) 
{
       
     //get topic titles from topics using current replies topic id
$queryt = "SELECT * FROM `forum_topics` WHERE `id`='".$row['tid']."' LIMIT ".$end.",".$start."";

$resultt = mysql_query($queryt,$link) or die(mysql_error());


          while ($topicrow = mysql_fetch_assoc($resultt)) 
            {
                       
             $topicrow['title'] = htmlentities($topicrow['title']);
                // show date of reply not of topic
             $row['date'] = htmlentities($row['date']);

           echo "<tr><td width=\"50%\" align=\"center\" class=\"hovr\"><b>";
           echo "<a href=\"./index.php?act=topic&cat=".$topicrow['cid']."&id=".$topicrow['id']."\">".$topicrow['title']."</a> ";
           echo "</b></td>";
           echo "<td width=\"50%\" align=\"center\">Posted On: <em>";
           echo $row['date'];
           echo "</em></td></tr>";

           }//close inner while
}//close outer while

[durangod]

After reading and reading today here is my feeble attempt, and it still does not return the results i want, no errors, just not what i want.

 

<?php

$query = "SELECT forum_topics.title,forum_topics.cid,forum_topics.id FROM forum_topics INNER JOIN forum_replies ON forum_topics.id=forum_replies.tid
ORDER BY forum_replies.date DESC  LIMIT ".$limit.",".$start."";
$resultr = mysql_query($query,$link) or die(mysql_error());
$totrep = mysql_num_rows($resultr);

?>

[durangod]

nevermind i got it working thanks

[fenway]

Then please post the solution.

[durangod]

sorry here is the solution, really simple but kind of confusing as well logic speaking, but works perfectly.

 

 

SELECT forum_topics.title,forum_topics.cid,forum_topics.id,forum_replies.date 
FROM forum_topics INNER JOIN forum_replies 
ON forum_topics.id=forum_replies.tid 
ORDER BY forum_replies.date DESC 
LIMIT $offset, $rowsperpage