Latest Attachment of 1st Post in Forums

OK, this is one hell of a confusing thing for me to get my head round, so I was wondering someone can lend a hand here

Check out http://www.pcreview.co.uk/index.php and you will see "Latest Articles" at the top - all of that I have to add manually everytime I create a review in one of the review forums.

If you check the image source, the source file is actually the attachment of the first post in the review thread. I want to get the latest four review images from my database and insert them on my main page - does anyone know how I would do this? (each post in the same review has the same attachment, so just getting the latest 4 images from a specific forum would not work).

Any ideas?

[Issvar]

SELECT postid FROM post LEFT JOIN thread ON thread.threadid=post.threadid WHERE thread.forumid IN (xx,xy,xz) AND thread.dateline=post.dateline ORDER BY post.dateline DESC LIMIT 4

That's the mysql statement you probably want. It gets the postids from the first posts of the latest threads in the forums with forumid xx,xy and xz (change as required). Avatars are accessed by their postid so you should be able to use them now easily. You can add getting the post content to that mysql statement so you can automize that as well. To do so just replace the SELECT postid with SELECT postid, pagetext

[Ian Cunningham]

Thanks

So far I am 50% of the way there, and I'm using this code:

PHP Code:

<?php
require('./admin/config.php');

//connect
$connection = mysql_connect("$servername","$dbusername","$dbpassword") or die ("Cannot connect to server.");

//select database
$db = mysql_select_db("$dbname", $connection) or die ("Could not select database.");

// create sql statement
$sql = "SELECT postid FROM post LEFT JOIN thread ON thread.threadid=post.threadid WHERE thread.forumid IN (22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38) AND thread.dateline=post.dateline ORDER BY post.dateline DESC LIMIT 4";

//execute sql query
$sql_result = mysql_query($sql, $connection) or die ("Could not execute query.");

while ($row = mysql_fetch_array($sql_result)) {
   $postid = $row["postid"];
}
echo "

<img src=\"http://www.pcreview.co.uk/attachment.php?postid=$postid\" border=\"0\">

";

mysql_free_result($sql_result);
mysql_close($connection);
?>

What I really need is to get the $threadid of that post, the $postid (which works!), and the $title. This code currently displays the 4th last image to be attached in those forums, can you help me modify it so all the last 4 are, and so I can call $threadid and $title too?

[Issvar]

PHP Code:

<?php
require('./admin/config.php');

//connect
$connection = mysql_connect("$servername","$dbusername","$dbpassword") or die ("Cannot connect to server.");

//select database
$db = mysql_select_db("$dbname", $connection) or die ("Could not select database.");

// create sql statement
$sql = "SELECT postid,post.threadid,post.title FROM post LEFT JOIN thread ON thread.threadid=post.threadid WHERE thread.forumid IN (22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38) AND thread.dateline=post.dateline AND post.attachmentid!=0 ORDER BY post.dateline DESC LIMIT 4";

//execute sql query
$sql_result = mysql_query($sql, $connection) or die ("Could not execute query.");

while ($row = mysql_fetch_array($sql_result)) {
   list($postid,$threadid,$title)=$row;
   echo "

<img src=\"http://www.pcreview.co.uk/attachment.php?postid=$postid\" border=\"0\">

   ";
}

mysql_free_result($sql_result);
mysql_close($connection);
?>

Changes I made: I put the echo command inside the while loop, so it will execute once for each row, instead of only once total;
I added getting titles and threadids and put those in their variables (be sure to stripslashes() on the title or it will have slashes in front of quotesigns);
I made sure the sql statement doesn't get posts that don't have attachments.