returning the last entered value in a mysql table in php

stakey

I'm writing a forum, similar to these here pages, all is going well except i want to get the date of the latest post and the poster and insert it into the display topic function. I'm not 100% sure on how to get the latest post but I am trying it using the MAX function in mysql. The code i wrote dont seem to work though. Anyone care to have a look.


$gettopics = "SELECT * FROM core_forum_topics";
$execute = mysql_query($gettopics) or die(mysql_error());
while ($get=mysql_fetch_array($execute))
{
$topicid = $get;
$topictitle = $get;
$topicdate = $get;
$topicauthor = $get;


$countpost = "SELECT count(post_id) FROM core_forum_posts WHERE post_pid = $topicid ORDER BY post_pid DESC";
$get_count = mysql_query($countpost) or die(mysql_error());
$number=mysql_result($get_count,0,'count(post_id)');

$getpost = "SELECT * FROM core_forum_posts WHERE $topicid = MAX(post_pid)";

$post = mysql_query($getpost) or die(mysql_error());
while ($getinfo=mysql_fetch_array($post))
{
$postauthor = $getinfo;
$postdate = $getinfo;


?>
do display stuff....

<?
}
}

any ideas?

Caixa

do an ORDER BY post_pid DESC and extract the first row?

Ph3n0m

uhm are your writing the code for the forum from scratch or are you using a pre-existing one - like vbulletin or phpbb?

If so the hacks for either of them for what you want to do are already available

stakey

dont think that will work ciaxa

what i am trying to do is select the latest post posted under a topic by its ID where the parent ID is equal to the topic ID.

Ph3n0m --

I am writing my own forum & CMS from the ground up, my final year project in college

Caixa

How about:

SELECT MAX(post_pid) FROM table WHERE parentID = topicID

Ph3n0m

to make it easier could you post the fields that core_forum_topics contains?

stakey

Ph3n0m

core_forum_topics contains the following fields

topic_id-- int
topic_title --varchar
topic_timestamp --datetime
topic_author --varchar

Ph3n0m

nevermind, try this

<?
$countpost = "SELECT * FROM core_forum_posts ORDER BY post_timestamp DESC LIMIT 1";
$get_count = mysql_query($countpost) or die(mysql_error());

while ($getinfo=mysql_fetch_array($get_count))
{
$postid = $getinfo['post_pid'];
$postauthor = $getinfo['post_author'];
$postdate = $getinfo['post_timestamp'];

$gettopics = "select *  from core_forum_topics where topic_id = $postid ";

$execute = mysql_query($gettopics) or die(mysql_error());
while ($get=mysql_fetch_array($execute))
{
$topictitle = $get['topic_title'];


echo "Topic: $topictitle<br>
Author: $postauthor<br>
Date: $postdate
<P>
";
}
}
?>