[SOLVED] Order By most recent dates from 2 tables

[mark110384]

Hey guys, I'm building a website forum and I'm nearly there but I've hit a snag. I am trouble trying to display the forum Topics in order of either the last reply date or if they have no replies of yet the creation date of the topic with the most recent at the top of the pile. This would require a query to 2 tables and compare the 2 dates. Would anyone know how to achieve what I am looking to do?

 

$dbhost = "localhost";
$dbuser = "root";
$dbname = "******";

mysql_connect("$dbhost", "$dbuser")or die("Could not connect to database");

mysql_select_db("$dbname") or die("Could not select db");

$sql = "SELECT * FROM forum_topic WHERE thread_ID = '$thread_no'";

			$result = mysql_query($sql) or die  ("Data not found");

			while ($myrow = mysql_fetch_array($result))
			{

				$ID_Topic = $myrow['topic_ID'];

				$Topic_N = $myrow['topic_Name'];

				$starter_Topic = $myrow['topic_Starter'];

				$solved_State = $myrow['status'];

					if (!$solved_State)

						{ 

						$image_path = "postreplyimg.gif";

						$name_Topic = "$Topic_N";

						}
					else
						{

						$image_path = "topic_solved.gif";

						$name_Topic = "[sOLVED] $Topic_N";
						}




					$sql_count_reply = "SELECT * FROM forum_reply WHERE topic_No = '$ID_Topic'";

					$count_result = mysql_query($sql_count_reply) or die  ("Data not found");

					$reply_count = mysql_num_rows($count_result);






				?>
        <tr> 
          <td bgcolor="#F6F6F6" align="center" width="5%"><img src="<? echo "$image_path"; ?>"/></td>
          <td align="left" bgcolor="#ECEDF3" ><? echo "<a class= 'select_thread' href=showtopic.php?topic_selected=$ID_Topic><b>$name_Topic </b></a>" ?></td>
          <td bgcolor="#F6F6F6" class="forumtxt"> <? echo "$starter_Topic"; ?></td>
          <td bgcolor="#ECEDF3" class="forumtxt" align="center"><? echo "$reply_count"; ?></td>
          <td width="20%" bgcolor="#F6F6F6" class="lastposter"><? echo  "$formated_rep_date";?> 
            <br> <? echo "$reply_name"; ?></td>
          <?

}
//}



?>

[mark110384]

bump

[budimir]

You are not giving enough info. From the code you've posted we can't see anything.

 

Have you done anything in this matter. If not, post your DB structure so we could see the names of your date fields and explain which field is what.

[mark110384]

There is essentially 3 tables.

 

forum_thread contains thread_Name and thread_ID, this table populates the main thread options the equivalent of PHP Help on this forum.

 

The second table is forum_topic - this contains topic_ID, topic_Name, topic_Message, topic_Starter, start_Date, thread_ID (This references the appropriate topic to the correct thread) and status.

 

The third and final table is forum_Reply - reply_ID, reply_Message, reply_Author, reply_Date andtopic_No (references the reply to the topic)

 

I need to check the tables forum_topic and forum_Reply and determine which dates in terms of start_Date(topic) and reply_Date are most recent, and then put them in order but I'm a bit puzzled on how to achieve this.

[mark110384]

bump

[mark110384]

I have managed to get the last reply time displayed in the table. Now it is just a mean of reorganising the table with regard to the date, but I'm stumped on how to do that.

 

<? 
//Variable passed from session start
$name = $_SESSION['username'];
$thread_no = $_GET["thread_selected"];

?>
<table width="80%" border="0" cellspacing="0" cellpadding="0" align="center" class="forumborder">
  <tr> 
    <td> <table width="100%" border="0" cellspacing="0" cellpadding="5" align="center" class="forumbartext" >
        <tr bgcolor="#999999"> 
          <td> </td>
          <td>Subject</td>
          <td>Started by</td>
          <td>Replies</td>
          <td>Last Post</td>
        </tr>
      </table>
      <table width="100%" border="1" cellspacing="0" cellpadding="0" align="center">
        <tr> 
          <?




$dbhost = "localhost";
$dbuser = "root";
$dbname = "****";

mysql_connect("$dbhost", "$dbuser")or die("Could not connect to database");

mysql_select_db("$dbname") or die("Could not select db");

//Access topics with regard to the thread number passed from a url

$sql = "SELECT * FROM forum_topic WHERE thread_ID = '$thread_no' ORDER BY start_Date DESC";

			$result = mysql_query($sql) or die  ("Data not found");

			while ($myrow = mysql_fetch_array($result))
			{

				$ID_Topic = $myrow['topic_ID'];

				$Topic_N = $myrow['topic_Name'];

				$starter_Topic = $myrow['topic_Starter'];

				$solved_State = $myrow['status'];

					if (!$solved_State)

						{ 

						$image_path = "postreplyimg.gif";

						$name_Topic = "$Topic_N";

						}
					else
						{

						$image_path = "topic_solved.gif";

						$name_Topic = "[sOLVED] $Topic_N";
						}


				//Count the number of replies

					$sql_count_reply = "SELECT * FROM forum_reply WHERE topic_No = '$ID_Topic'";

					$count_result = mysql_query($sql_count_reply) or die  ("Data not found");

					$reply_count = mysql_num_rows($count_result);

					if ($reply_count == 0)
                    {
						$reply_name = "";
						$formated_rep_date = "";

					}
					else
					{
//If it is not equal to zero find the last reply details (Name and Time)

					$last_reply = "SELECT * FROM forum_reply WHERE topic_No = '$ID_Topic' ORDER BY reply_Date DESC limit 0,1";

					$last_reply_result = mysql_query($last_reply) or die  ("Data not found");

					while ($myrow_last_rep = mysql_fetch_array($last_reply_result))

					{

						$last_reply_name = $myrow_last_rep['reply_Author'];

						$last_reply_time  = $myrow_last_rep['reply_Date'];



								$reply_name = "$last_reply_name";
								$reply_date = "$last_reply_time";
								$formated_rep_date = date('F d, Y, G:i A', strtotime($reply_date));

								}


										  

						}






				?>
        <tr> 
          <td bgcolor="#F6F6F6" align="center" width="5%"><img src="<? echo "$image_path"; ?>"/></td>
          <td align="left" bgcolor="#ECEDF3" ><? echo "<a class= 'select_thread' href=showtopic.php?topic_selected=$ID_Topic><b>$name_Topic </b></a>" ?></td>
          <td bgcolor="#F6F6F6" class="forumtxt"> <? echo "$starter_Topic"; ?></td>
          <td bgcolor="#ECEDF3" class="forumtxt" align="center"><? echo "$reply_count"; ?></td>
          <td width="20%" bgcolor="#F6F6F6" class="lastposter"><? echo  "$formated_rep_date";?> 
            <br> <? echo "$reply_name"; ?></td>
          <?

}



?>
        </tr>
      </table></td>
  </tr>
</table>

[mark110384]

bump

[mark110384]

bump

[mark110384]

I created a new date field in my table that would be created when the topic is created and then subsquently updated when a reply is added. I then just ordered it by the new update field.